feat: allow enrollment api admin to see all enrollments #1714
Conversation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
openedx-webhooks
added
the
open-source-contribution
|
Thanks for the pull request, @0x29a! Please note that it may take us up to several weeks or months to complete a review and merge your PR. Feel free to add as much of the following information to the ticket as you can:
All technical communication about the code itself will be done via the GitHub pull request interface. As a reminder, our process documentation is here. Please let us know once your PR is ready for our review and all tests are green. |
0x29a
force-pushed
the
0x29a/enterprise-course-enrollments-api-improvements
branch
5 times, most recently
from
585e511
to
fcc3e08
Compare
0x29a
changed the title
Cup0fCoffee
reviewed
Feb 8, 2023
Comment on lines
85
to
148
| @ddt.ddt | ||
| @mark.django_db | ||
| class TestEnterpriseCourseEnrollmentFilterBackend(APITest): | ||
| """ | ||
| Test suite for the ``EnterpriseCourseEnrollmentFilterBackend`` filter. | ||
| """ | ||
|
|
||
| def setUp(self): | ||
| super().setUp() | ||
| self.other_user= factories.UserFactory() | ||
| self.enterprise_customer = factories.EnterpriseCustomerFactory(uuid=FAKE_UUIDS[0]) | ||
|
|
||
| self.enterprise_customer_user_1 = factories.EnterpriseCustomerUserFactory( | ||
| enterprise_customer=self.enterprise_customer, | ||
| user_id=self.user.id | ||
| ) | ||
| self.enterprise_customer_user_2 = factories.EnterpriseCustomerUserFactory( | ||
| enterprise_customer=self.enterprise_customer, | ||
| user_id=self.other_user.id | ||
| ) | ||
| self.course_enrollment_1 = factories.EnterpriseCourseEnrollmentFactory( | ||
| enterprise_customer_user=self.enterprise_customer_user_1 | ||
| ) | ||
| self.course_enrollment_2 = factories.EnterpriseCourseEnrollmentFactory( | ||
| enterprise_customer_user=self.enterprise_customer_user_2 | ||
| ) | ||
|
|
||
| self.url = settings.TEST_SERVER + reverse('enterprise-course-enrollment-list') | ||
|
|
||
| @ddt.data( | ||
| ("regular", 1), | ||
| ("staff", 2), | ||
| ("enrollment_api_admin", 2), | ||
| ) | ||
| @ddt.unpack | ||
| def test_filter_for_list(self, user_role, expected_course_enrollment_count): | ||
| """ | ||
| Filter objects based off whether the user is a staff, enterprise enrollment api admin, or neither. | ||
| """ | ||
| if user_role == "staff": | ||
| self.user.is_staff = True | ||
| self.user.save() | ||
| elif user_role == "enrollment_api_admin": | ||
| factories.EnterpriseFeatureUserRoleAssignmentFactory( | ||
| user=self.user, | ||
| role=EnterpriseFeatureRole.objects.get(name=ENTERPRISE_ENROLLMENT_API_ADMIN_ROLE) | ||
| ) | ||
|
|
||
| response = self.client.get(self.url) | ||
| assert response.status_code == status.HTTP_200_OK | ||
| data = self.load_json(response.content) | ||
| assert len(data['results']) == expected_course_enrollment_count |
There was a problem hiding this comment.
@0x29a, imho, this test should be setup in such a way, that staff user gets more results than enrollment_api_admin, so that it's obvious that enrollment_api_admin can only get a subset of staff's results, since staff can get all enrollments, and enrollment_api_admin can only get the enrollments of the EnterpriseCustomer he is linked too. The way the test setup right now, if we were to modify the filter to return the same result for staff and for enrollment_api_admin, the test would still pass, when it should fail.
Also, since relationship between EnterpriseCustomerUser and User is many-to-one, it might be worth adding tests related to the behavior. I.e. having two enterprise accounts, enroll users under different enterprise customers, linking an admin to both EnterpriseCustomers and then checking that he filter returns results both customers (assuming this is the intended behavior).
Comment on lines
124
to
131
| if user_role == "staff": | ||
| self.user.is_staff = True | ||
| self.user.save() | ||
| elif user_role == "enrollment_api_admin": | ||
| factories.EnterpriseFeatureUserRoleAssignmentFactory( | ||
| user=self.user, | ||
| role=EnterpriseFeatureRole.objects.get(name=ENTERPRISE_ENROLLMENT_API_ADMIN_ROLE) | ||
| ) |
There was a problem hiding this comment.
nit: the setup could be extracted from the test, e.g. into a decorator or a function. It would simplify the test it itself, but perhaps it would over-complicate the code.
mphilbrick211
added
the
needs test run
|
👍 Other than some changes necessary for the new test case, everything else is fine.
|
|
@mphilbrick211 tests running. |
Cup0fCoffee
force-pushed
the
0x29a/enterprise-course-enrollments-api-improvements
branch
from
f28747d
to
433e06b
Compare
|
@0x29a looks like there are some quality and linting issues. |
e0d
removed
the
needs test run
Cup0fCoffee
force-pushed
the
0x29a/enterprise-course-enrollments-api-improvements
branch
from
433e06b
to
2bbff99
Compare
|
@Cup0fCoffee no problem. Ran again and there's a coverage difference: https://github.com/openedx/edx-enterprise/pull/1714/checks?check_run_id=11423944585 |
@e0d The file that is mentioned was not modified in this PR, and no tests were removed. I think the coverage diff is coming from this commit (different PR, which already has been merged). I've rebased onto the most recent version of the |
Cup0fCoffee
force-pushed
the
0x29a/enterprise-course-enrollments-api-improvements
branch
from
2bbff99
to
aa912ed
Compare
|
@e0d Kind reminder ^ |
tecoholic
added a commit
to open-craft/edx-enterprise
that referenced
this pull request
Mar 24, 2023
This enables the enterprise admin to see all the enrollments of the enterprise and adds the following extra fields to the response: * enrollment_date * enrollment_track * user_email * course_start * course_end Ref: openedx#1714 Co-authored-by: 0x29a <[email protected]>, Cup0fCoffee <[email protected]>
|
@Cup0fCoffee sorry this feel off the radar with conference planning. It looks like there are conflicts that need to be resolved. Once that's done, we can adjust the coverage requirements given your PR is not introducing the issue. |
|
FYI @feanil @felipemontoya input into future plans around a standard enrollment API. |
Cup0fCoffee
force-pushed
the
0x29a/enterprise-course-enrollments-api-improvements
branch
2 times, most recently
from
3dce3c4
to
fe92bec
Compare
|
@e0d While resolving the conflicts I've noticed that our implementation breaks code/tests which were added in between when this PR was created and now. I need some time to adjust our implementation. I'll ping here again once that is done. |
Cup0fCoffee
force-pushed
the
0x29a/enterprise-course-enrollments-api-improvements
branch
2 times, most recently
from
29bd233
to
91be371
Compare
mphilbrick211
added
the
waiting on author
|
Moving the conversation from #1714 (comment) here to improve readability of my response. Hi @feanil and @ormsbee, I've done tests that you suggested and some others. Let me re-state the problem a bit more clearly first: We have this endpoint: http://localhost:18000/enterprise/api/v1/enterprise-course-enrollment/. It's returning
Here are all four queries: COUNT for staff SELECT COUNT(*)
FROM (
SELECT (
SELECT U0.`mode`
FROM `student_courseenrollment` U0
WHERE (U0.`course_id` = `enterprise_enterprisecourseenrollment`.`course_id` AND U0.`user_id` = `enterprise_enterprisecustomeruser`.`user_id`)
ORDER BY U0.`user_id` ASC, U0.`course_id` ASC
LIMIT 1
) AS `enrollment_track`,
(
SELECT U0.`created`
FROM `student_courseenrollment` U0
WHERE (U0.`course_id` = `enterprise_enterprisecourseenrollment`.`course_id` AND U0.`user_id` = `enterprise_enterprisecustomeruser`.`user_id`)
ORDER BY U0.`user_id` ASC, U0.`course_id` ASC
LIMIT 1
) AS `enrollment_date`,
(
SELECT U0.`email`
FROM `auth_user` U0
WHERE U0.`id` = `enterprise_enterprisecustomeruser`.`user_id`
LIMIT 1
) AS `user_email`,
(
SELECT U0.`start`
FROM `course_overviews_courseoverview` U0
WHERE U0.`id` = `enterprise_enterprisecourseenrollment`.`course_id`
LIMIT 1
) AS `course_start`,
(
SELECT U0.`end`
FROM `course_overviews_courseoverview` U0
WHERE U0.`id` = `enterprise_enterprisecourseenrollment`.`course_id`
LIMIT 1
) AS `course_end`
FROM `enterprise_enterprisecourseenrollment`
INNER JOIN `enterprise_enterprisecustomeruser`
ON (`enterprise_enterprisecourseenrollment`.`enterprise_customer_user_id` = `enterprise_enterprisecustomeruser`.`id`)
WHERE `enterprise_enterprisecustomeruser`.`linked`
) subquerySELECT for staff SELECT `enterprise_enterprisecourseenrollment`.`id`,
`enterprise_enterprisecourseenrollment`.`created`,
`enterprise_enterprisecourseenrollment`.`modified`,
`enterprise_enterprisecourseenrollment`.`enterprise_customer_user_id`,
`enterprise_enterprisecourseenrollment`.`course_id`,
`enterprise_enterprisecourseenrollment`.`saved_for_later`,
`enterprise_enterprisecourseenrollment`.`source_id`,
`enterprise_enterprisecourseenrollment`.`unenrolled`,
`enterprise_enterprisecourseenrollment`.`unenrolled_at`,
(
SELECT U0.`mode`
FROM `student_courseenrollment` U0
WHERE (U0.`course_id` = `enterprise_enterprisecourseenrollment`.`course_id` AND U0.`user_id` = `enterprise_enterprisecustomeruser`.`user_id`)
ORDER BY U0.`user_id` ASC, U0.`course_id` ASC
LIMIT 1
) AS `enrollment_track`,
(
SELECT U0.`created`
FROM `student_courseenrollment` U0
WHERE (U0.`course_id` = `enterprise_enterprisecourseenrollment`.`course_id` AND U0.`user_id` = `enterprise_enterprisecustomeruser`.`user_id`)
ORDER BY U0.`user_id` ASC, U0.`course_id` ASC
LIMIT 1
) AS `enrollment_date`,
(
SELECT U0.`email`
FROM `auth_user` U0
WHERE U0.`id` = `enterprise_enterprisecustomeruser`.`user_id`
LIMIT 1
) AS `user_email`,
(
SELECT U0.`start`
FROM `course_overviews_courseoverview` U0
WHERE U0.`id` = `enterprise_enterprisecourseenrollment`.`course_id`
LIMIT 1
) AS `course_start`,
(
SELECT U0.`end`
FROM `course_overviews_courseoverview` U0
WHERE U0.`id` = `enterprise_enterprisecourseenrollment`.`course_id`
LIMIT 1
) AS `course_end`,
`enterprise_enterprisecustomeruser`.`id`,
`enterprise_enterprisecustomeruser`.`created`,
`enterprise_enterprisecustomeruser`.`modified`,
`enterprise_enterprisecustomeruser`.`enterprise_customer_id`,
`enterprise_enterprisecustomeruser`.`user_id`,
`enterprise_enterprisecustomeruser`.`active`,
`enterprise_enterprisecustomeruser`.`linked`,
`enterprise_enterprisecustomeruser`.`is_relinkable`,
`enterprise_enterprisecustomeruser`.`invite_key_id`,
`enterprise_enterprisecustomeruser`.`should_inactivate_other_customers`
FROM `enterprise_enterprisecourseenrollment`
INNER JOIN `enterprise_enterprisecustomeruser`
ON (`enterprise_enterprisecourseenrollment`.`enterprise_customer_user_id` = `enterprise_enterprisecustomeruser`.`id`)
WHERE `enterprise_enterprisecustomeruser`.`linked`
ORDER BY `enterprise_enterprisecourseenrollment`.`created` ASC
LIMIT 100COUNT for enrollment_api_admin (with WHERE clause) SELECT COUNT(*)
FROM (
SELECT (
SELECT U0.`mode`
FROM `student_courseenrollment` U0
WHERE (U0.`course_id` = `enterprise_enterprisecourseenrollment`.`course_id` AND U0.`user_id` = `enterprise_enterprisecustomeruser`.`user_id`)
ORDER BY U0.`user_id` ASC, U0.`course_id` ASC
LIMIT 1
) AS `enrollment_track`,
(
SELECT U0.`created`
FROM `student_courseenrollment` U0
WHERE (U0.`course_id` = `enterprise_enterprisecourseenrollment`.`course_id` AND U0.`user_id` = `enterprise_enterprisecustomeruser`.`user_id`)
ORDER BY U0.`user_id` ASC, U0.`course_id` ASC
LIMIT 1
) AS `enrollment_date`,
(
SELECT U0.`email`
FROM `auth_user` U0
WHERE U0.`id` = `enterprise_enterprisecustomeruser`.`user_id`
LIMIT 1
) AS `user_email`,
(
SELECT U0.`start`
FROM `course_overviews_courseoverview` U0
WHERE U0.`id` = `enterprise_enterprisecourseenrollment`.`course_id`
LIMIT 1
) AS `course_start`,
(
SELECT U0.`end`
FROM `course_overviews_courseoverview` U0
WHERE U0.`id` = `enterprise_enterprisecourseenrollment`.`course_id`
LIMIT 1
) AS `course_end`
FROM `enterprise_enterprisecourseenrollment`
INNER JOIN `enterprise_enterprisecustomeruser`
ON (`enterprise_enterprisecourseenrollment`.`enterprise_customer_user_id` = `enterprise_enterprisecustomeruser`.`id`)
WHERE (`enterprise_enterprisecustomeruser`.`linked` AND `enterprise_enterprisecustomeruser`.`enterprise_customer_id` IN (SELECT U0.`uuid` FROM `enterprise_enterprisecustomer` U0 INNER JOIN `enterprise_enterprisecustomeruser` U1 ON (U0.`uuid` = U1.`enterprise_customer_id`) WHERE U1.`user_id` = 17))
) subquerySELECT for enrollment_api_admin (with WHERE clause) SELECT `enterprise_enterprisecourseenrollment`.`id`,
`enterprise_enterprisecourseenrollment`.`created`,
`enterprise_enterprisecourseenrollment`.`modified`,
`enterprise_enterprisecourseenrollment`.`enterprise_customer_user_id`,
`enterprise_enterprisecourseenrollment`.`course_id`,
`enterprise_enterprisecourseenrollment`.`saved_for_later`,
`enterprise_enterprisecourseenrollment`.`source_id`,
`enterprise_enterprisecourseenrollment`.`unenrolled`,
`enterprise_enterprisecourseenrollment`.`unenrolled_at`,
(
SELECT U0.`mode`
FROM `student_courseenrollment` U0
WHERE (U0.`course_id` = `enterprise_enterprisecourseenrollment`.`course_id` AND U0.`user_id` = `enterprise_enterprisecustomeruser`.`user_id`)
ORDER BY U0.`user_id` ASC, U0.`course_id` ASC
LIMIT 1
) AS `enrollment_track`,
(
SELECT U0.`created`
FROM `student_courseenrollment` U0
WHERE (U0.`course_id` = `enterprise_enterprisecourseenrollment`.`course_id` AND U0.`user_id` = `enterprise_enterprisecustomeruser`.`user_id`)
ORDER BY U0.`user_id` ASC, U0.`course_id` ASC
LIMIT 1
) AS `enrollment_date`,
(
SELECT U0.`email`
FROM `auth_user` U0
WHERE U0.`id` = `enterprise_enterprisecustomeruser`.`user_id`
LIMIT 1
) AS `user_email`,
(
SELECT U0.`start`
FROM `course_overviews_courseoverview` U0
WHERE U0.`id` = `enterprise_enterprisecourseenrollment`.`course_id`
LIMIT 1
) AS `course_start`,
(
SELECT U0.`end`
FROM `course_overviews_courseoverview` U0
WHERE U0.`id` = `enterprise_enterprisecourseenrollment`.`course_id`
LIMIT 1
) AS `course_end`,
`enterprise_enterprisecustomeruser`.`id`,
`enterprise_enterprisecustomeruser`.`created`,
`enterprise_enterprisecustomeruser`.`modified`,
`enterprise_enterprisecustomeruser`.`enterprise_customer_id`,
`enterprise_enterprisecustomeruser`.`user_id`,
`enterprise_enterprisecustomeruser`.`active`,
`enterprise_enterprisecustomeruser`.`linked`,
`enterprise_enterprisecustomeruser`.`is_relinkable`,
`enterprise_enterprisecustomeruser`.`invite_key_id`,
`enterprise_enterprisecustomeruser`.`should_inactivate_other_customers`
FROM `enterprise_enterprisecourseenrollment`
INNER JOIN `enterprise_enterprisecustomeruser`
ON (`enterprise_enterprisecourseenrollment`.`enterprise_customer_user_id` = `enterprise_enterprisecustomeruser`.`id`)
WHERE (`enterprise_enterprisecustomeruser`.`linked` AND `enterprise_enterprisecustomeruser`.`enterprise_customer_id` IN (SELECT U0.`uuid` FROM `enterprise_enterprisecustomer` U0 INNER JOIN `enterprise_enterprisecustomeruser` U1 ON (U0.`uuid` = U1.`enterprise_customer_id`) WHERE U1.`user_id` = 17))
ORDER BY `enterprise_enterprisecourseenrollment`.`created` ASC
LIMIT 100Explains for each without any additional indexes (this time with diagrams): COUNT for staff
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "5385.06"
},
"table": {
"table_name": "subquery",
"access_type": "ALL",
"rows_examined_per_scan": 47845,
"rows_produced_per_join": 47845,
"filtered": "100.00",
"cost_info": {
"read_cost": "600.56",
"eval_cost": "4784.50",
"prefix_cost": "5385.06",
"data_read_per_join": "50M"
},
"used_columns": [
"enrollment_track",
"enrollment_date",
"user_email",
"course_start",
"course_end"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "6777.55"
},
"nested_loop": [
{
"table": {
"table_name": "enterprise_enterprisecustomeruser",
"access_type": "ALL",
"possible_keys": [
"PRIMARY"
],
"rows_examined_per_scan": 2959,
"rows_produced_per_join": 2663,
"filtered": "90.00",
"cost_info": {
"read_cost": "34.09",
"eval_cost": "266.31",
"prefix_cost": "300.40",
"data_read_per_join": "582K"
},
"used_columns": [
"id",
"user_id",
"linked"
],
"attached_condition": "(0 <> `edxapp`.`enterprise_enterprisecustomeruser`.`linked`)"
}
},
{
"table": {
"table_name": "enterprise_enterprisecourseenrollment",
"access_type": "ref",
"possible_keys": [
"enterprise_enterprisecou_enterprise_customer_user_71fe301a_uniq"
],
"key": "enterprise_enterprisecou_enterprise_customer_user_71fe301a_uniq",
"used_key_parts": [
"enterprise_customer_user_id"
],
"key_length": "4",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.id"
],
"rows_examined_per_scan": 17,
"rows_produced_per_join": 47845,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "1692.64",
"eval_cost": "4784.51",
"prefix_cost": "6777.55",
"data_read_per_join": "36M"
},
"used_columns": [
"course_id",
"enterprise_customer_user_id"
]
}
}
],
"select_list_subqueries": [
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 7,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "767",
"ref": [
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "3K"
},
"used_columns": [
"id",
"end"
]
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 6,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "767",
"ref": [
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "3K"
},
"used_columns": [
"id",
"start"
]
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 5,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "2K"
},
"used_columns": [
"id",
"email"
],
"attached_condition": "(`edxapp`.`U0`.`id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)"
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 4,
"cost_info": {
"query_cost": "0.35"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"student_courseenrollment_user_id_4263a8e2",
"student_cou_user_id_b19dcd_idx",
"student_courseenrollment_course_id_a6f93be8"
],
"key": "student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"used_key_parts": [
"user_id",
"course_id"
],
"key_length": "771",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id",
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"index_condition": "(`edxapp`.`U0`.`user_id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"course_id",
"created",
"user_id"
]
}
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 3,
"cost_info": {
"query_cost": "0.35"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"student_courseenrollment_user_id_4263a8e2",
"student_cou_user_id_b19dcd_idx",
"student_courseenrollment_course_id_a6f93be8"
],
"key": "student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"used_key_parts": [
"user_id",
"course_id"
],
"key_length": "771",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id",
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"index_condition": "(`edxapp`.`U0`.`user_id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"course_id",
"mode",
"user_id"
]
}
}
}
}
]
}
}
}
}
}SELECT for staff
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "23590.40"
},
"ordering_operation": {
"using_filesort": true,
"nested_loop": [
{
"table": {
"table_name": "enterprise_enterprisecourseenrollment",
"access_type": "ALL",
"possible_keys": [
"enterprise_enterprisecou_enterprise_customer_user_71fe301a_uniq"
],
"rows_examined_per_scan": 52227,
"rows_produced_per_join": 52227,
"filtered": "100.00",
"cost_info": {
"read_cost": "88.25",
"eval_cost": "5222.70",
"prefix_cost": "5310.95",
"data_read_per_join": "40M"
},
"used_columns": [
"id",
"created",
"modified",
"course_id",
"enterprise_customer_user_id",
"source_id",
"saved_for_later",
"unenrolled",
"unenrolled_at"
]
}
},
{
"table": {
"table_name": "enterprise_enterprisecustomeruser",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"edxapp.enterprise_enterprisecourseenrollment.enterprise_customer_user_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 47004,
"filtered": "90.00",
"cost_info": {
"read_cost": "13056.75",
"eval_cost": "4700.43",
"prefix_cost": "23590.40",
"data_read_per_join": "10M"
},
"used_columns": [
"id",
"created",
"modified",
"user_id",
"active",
"linked",
"enterprise_customer_id",
"invite_key_id",
"should_inactivate_other_customers",
"is_relinkable"
],
"attached_condition": "(0 <> `edxapp`.`enterprise_enterprisecustomeruser`.`linked`)"
}
}
],
"select_list_subqueries": [
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 6,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "767",
"ref": [
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "3K"
},
"used_columns": [
"id",
"end"
]
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 5,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "767",
"ref": [
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "3K"
},
"used_columns": [
"id",
"start"
]
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 4,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "2K"
},
"used_columns": [
"id",
"email"
],
"attached_condition": "(`edxapp`.`U0`.`id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)"
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 3,
"cost_info": {
"query_cost": "0.35"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"student_courseenrollment_user_id_4263a8e2",
"student_cou_user_id_b19dcd_idx",
"student_courseenrollment_course_id_a6f93be8"
],
"key": "student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"used_key_parts": [
"user_id",
"course_id"
],
"key_length": "771",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id",
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"index_condition": "(`edxapp`.`U0`.`user_id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"course_id",
"created",
"user_id"
]
}
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "0.35"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"student_courseenrollment_user_id_4263a8e2",
"student_cou_user_id_b19dcd_idx",
"student_courseenrollment_course_id_a6f93be8"
],
"key": "student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"used_key_parts": [
"user_id",
"course_id"
],
"key_length": "771",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id",
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"index_condition": "(`edxapp`.`U0`.`user_id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"course_id",
"mode",
"user_id"
]
}
}
}
}
]
}
}
}COUNT for enrollment_api_admin
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "108.02"
},
"table": {
"table_name": "subquery",
"access_type": "ALL",
"rows_examined_per_scan": 938,
"rows_produced_per_join": 938,
"filtered": "100.00",
"cost_info": {
"read_cost": "14.22",
"eval_cost": "93.80",
"prefix_cost": "108.03",
"data_read_per_join": "1003K"
},
"used_columns": [
"enrollment_track",
"enrollment_date",
"user_email",
"course_start",
"course_end"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "148.71"
},
"nested_loop": [
{
"table": {
"table_name": "U1",
"access_type": "ref",
"possible_keys": [
"enterprise_enterprisecus_enterprise_customer_id_u_ffddc29f_uniq",
"enterprise_enterprisecustomeruser_user_id_aa8d772f"
],
"key": "enterprise_enterprisecustomeruser_user_id_aa8d772f",
"used_key_parts": [
"user_id"
],
"key_length": "4",
"ref": [
"const"
],
"rows_examined_per_scan": 2,
"rows_produced_per_join": 2,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.50",
"eval_cost": "0.20",
"prefix_cost": "0.70",
"data_read_per_join": "448"
},
"used_columns": [
"user_id",
"enterprise_customer_id"
]
}
},
{
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"uuid"
],
"key_length": "96",
"ref": [
"edxapp.U1.enterprise_customer_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 2,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "0.50",
"eval_cost": "0.20",
"prefix_cost": "1.40",
"data_read_per_join": "7K"
},
"used_columns": [
"uuid"
]
}
},
{
"table": {
"table_name": "enterprise_enterprisecustomeruser",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"enterprise_enterprisecus_enterprise_customer_id_u_ffddc29f_uniq"
],
"key": "enterprise_enterprisecus_enterprise_customer_id_u_ffddc29f_uniq",
"used_key_parts": [
"enterprise_customer_id"
],
"key_length": "96",
"ref": [
"edxapp.U1.enterprise_customer_id"
],
"rows_examined_per_scan": 29,
"rows_produced_per_join": 52,
"filtered": "90.00",
"cost_info": {
"read_cost": "14.50",
"eval_cost": "5.22",
"prefix_cost": "21.71",
"data_read_per_join": "11K"
},
"used_columns": [
"id",
"user_id",
"linked",
"enterprise_customer_id"
],
"attached_condition": "(0 <> `edxapp`.`enterprise_enterprisecustomeruser`.`linked`)"
}
},
{
"table": {
"table_name": "enterprise_enterprisecourseenrollment",
"access_type": "ref",
"possible_keys": [
"enterprise_enterprisecou_enterprise_customer_user_71fe301a_uniq"
],
"key": "enterprise_enterprisecou_enterprise_customer_user_71fe301a_uniq",
"used_key_parts": [
"enterprise_customer_user_id"
],
"key_length": "4",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.id"
],
"rows_examined_per_scan": 17,
"rows_produced_per_join": 938,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "33.19",
"eval_cost": "93.81",
"prefix_cost": "148.71",
"data_read_per_join": "740K"
},
"used_columns": [
"course_id",
"enterprise_customer_user_id"
]
}
}
],
"select_list_subqueries": [
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 7,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "767",
"ref": [
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "3K"
},
"used_columns": [
"id",
"end"
]
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 6,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "767",
"ref": [
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "3K"
},
"used_columns": [
"id",
"start"
]
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 5,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "2K"
},
"used_columns": [
"id",
"email"
],
"attached_condition": "(`edxapp`.`U0`.`id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)"
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 4,
"cost_info": {
"query_cost": "0.35"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"student_courseenrollment_user_id_4263a8e2",
"student_cou_user_id_b19dcd_idx",
"student_courseenrollment_course_id_a6f93be8"
],
"key": "student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"used_key_parts": [
"user_id",
"course_id"
],
"key_length": "771",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id",
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"index_condition": "(`edxapp`.`U0`.`user_id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"course_id",
"created",
"user_id"
]
}
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 3,
"cost_info": {
"query_cost": "0.35"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"student_courseenrollment_user_id_4263a8e2",
"student_cou_user_id_b19dcd_idx",
"student_courseenrollment_course_id_a6f93be8"
],
"key": "student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"used_key_parts": [
"user_id",
"course_id"
],
"key_length": "771",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id",
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"index_condition": "(`edxapp`.`U0`.`user_id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"course_id",
"mode",
"user_id"
]
}
}
}
}
]
}
}
}
}
}SELECT for enrollment_api_admin
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1288.19"
},
"ordering_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "938.14"
},
"nested_loop": [
{
"table": {
"table_name": "U1",
"access_type": "ref",
"possible_keys": [
"enterprise_enterprisecus_enterprise_customer_id_u_ffddc29f_uniq",
"enterprise_enterprisecustomeruser_user_id_aa8d772f"
],
"key": "enterprise_enterprisecustomeruser_user_id_aa8d772f",
"used_key_parts": [
"user_id"
],
"key_length": "4",
"ref": [
"const"
],
"rows_examined_per_scan": 2,
"rows_produced_per_join": 2,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.50",
"eval_cost": "0.20",
"prefix_cost": "0.70",
"data_read_per_join": "448"
},
"used_columns": [
"id",
"user_id",
"enterprise_customer_id"
]
}
},
{
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"uuid"
],
"key_length": "96",
"ref": [
"edxapp.U1.enterprise_customer_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 2,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "0.50",
"eval_cost": "0.20",
"prefix_cost": "1.40",
"data_read_per_join": "7K"
},
"used_columns": [
"uuid"
]
}
},
{
"table": {
"table_name": "enterprise_enterprisecustomeruser",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"enterprise_enterprisecus_enterprise_customer_id_u_ffddc29f_uniq"
],
"key": "enterprise_enterprisecus_enterprise_customer_id_u_ffddc29f_uniq",
"used_key_parts": [
"enterprise_customer_id"
],
"key_length": "96",
"ref": [
"edxapp.U1.enterprise_customer_id"
],
"rows_examined_per_scan": 29,
"rows_produced_per_join": 52,
"filtered": "90.00",
"cost_info": {
"read_cost": "14.50",
"eval_cost": "5.22",
"prefix_cost": "21.71",
"data_read_per_join": "11K"
},
"used_columns": [
"id",
"created",
"modified",
"user_id",
"active",
"linked",
"enterprise_customer_id",
"invite_key_id",
"should_inactivate_other_customers",
"is_relinkable"
],
"attached_condition": "(0 <> `edxapp`.`enterprise_enterprisecustomeruser`.`linked`)"
}
},
{
"table": {
"table_name": "enterprise_enterprisecourseenrollment",
"access_type": "ref",
"possible_keys": [
"enterprise_enterprisecou_enterprise_customer_user_71fe301a_uniq"
],
"key": "enterprise_enterprisecou_enterprise_customer_user_71fe301a_uniq",
"used_key_parts": [
"enterprise_customer_user_id"
],
"key_length": "4",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.id"
],
"rows_examined_per_scan": 17,
"rows_produced_per_join": 938,
"filtered": "100.00",
"cost_info": {
"read_cost": "234.53",
"eval_cost": "93.81",
"prefix_cost": "350.06",
"data_read_per_join": "740K"
},
"used_columns": [
"id",
"created",
"modified",
"course_id",
"enterprise_customer_user_id",
"source_id",
"saved_for_later",
"unenrolled",
"unenrolled_at"
]
}
}
],
"select_list_subqueries": [
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 6,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "767",
"ref": [
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "3K"
},
"used_columns": [
"id",
"end"
]
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 5,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "767",
"ref": [
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "3K"
},
"used_columns": [
"id",
"start"
]
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 4,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "2K"
},
"used_columns": [
"id",
"email"
],
"attached_condition": "(`edxapp`.`U0`.`id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)"
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 3,
"cost_info": {
"query_cost": "0.35"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"student_courseenrollment_user_id_4263a8e2",
"student_cou_user_id_b19dcd_idx",
"student_courseenrollment_course_id_a6f93be8"
],
"key": "student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"used_key_parts": [
"user_id",
"course_id"
],
"key_length": "771",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id",
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"index_condition": "(`edxapp`.`U0`.`user_id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"course_id",
"created",
"user_id"
]
}
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "0.35"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"student_courseenrollment_user_id_4263a8e2",
"student_cou_user_id_b19dcd_idx",
"student_courseenrollment_course_id_a6f93be8"
],
"key": "student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"used_key_parts": [
"user_id",
"course_id"
],
"key_length": "771",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id",
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"index_condition": "(`edxapp`.`U0`.`user_id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"course_id",
"mode",
"user_id"
]
}
}
}
}
]
}
}
}As @ormsbee suggested, I tried indexes on different columns, but improvements can be seen only for SELECT query for staff (without the WHERE clause) when there is an index on Before
After
Given all this, I can propose this plan:
If I'm not missing anything, this should make us safe from performance issues or high DB load. What do you think? |
mphilbrick211
removed
the
waiting on author
|
@0x29a: Is sorting that table by |
|
@ormsbee, ah, right, I forgot to address this comment. Indeed, sorting by Results.
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "23590.40"
},
"ordering_operation": {
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "enterprise_enterprisecourseenrollment",
"access_type": "index",
"possible_keys": [
"enterprise_enterprisecou_enterprise_customer_user_71fe301a_uniq"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"rows_examined_per_scan": 111,
"rows_produced_per_join": 52227,
"filtered": "100.00",
"cost_info": {
"read_cost": "88.25",
"eval_cost": "5222.70",
"prefix_cost": "5310.95",
"data_read_per_join": "40M"
},
"used_columns": [
"id",
"created",
"modified",
"course_id",
"enterprise_customer_user_id",
"source_id",
"saved_for_later",
"unenrolled",
"unenrolled_at"
]
}
},
{
"table": {
"table_name": "enterprise_enterprisecustomeruser",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"edxapp.enterprise_enterprisecourseenrollment.enterprise_customer_user_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 47004,
"filtered": "90.00",
"cost_info": {
"read_cost": "13056.75",
"eval_cost": "4700.43",
"prefix_cost": "23590.40",
"data_read_per_join": "10M"
},
"used_columns": [
"id",
"created",
"modified",
"user_id",
"active",
"linked",
"enterprise_customer_id",
"invite_key_id",
"should_inactivate_other_customers",
"is_relinkable"
],
"attached_condition": "(0 <> `edxapp`.`enterprise_enterprisecustomeruser`.`linked`)"
}
}
],
"select_list_subqueries": [
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 6,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "767",
"ref": [
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "3K"
},
"used_columns": [
"id",
"end"
]
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 5,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "767",
"ref": [
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "3K"
},
"used_columns": [
"id",
"start"
]
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 4,
"cost_info": {
"query_cost": "0.35"
},
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "2K"
},
"used_columns": [
"id",
"email"
],
"attached_condition": "(`edxapp`.`U0`.`id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)"
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 3,
"cost_info": {
"query_cost": "0.35"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"student_courseenrollment_user_id_4263a8e2",
"student_cou_user_id_b19dcd_idx",
"student_courseenrollment_course_id_a6f93be8"
],
"key": "student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"used_key_parts": [
"user_id",
"course_id"
],
"key_length": "771",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id",
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"index_condition": "(`edxapp`.`U0`.`user_id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"course_id",
"created",
"user_id"
]
}
}
}
},
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "0.35"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "U0",
"access_type": "eq_ref",
"possible_keys": [
"student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"student_courseenrollment_user_id_4263a8e2",
"student_cou_user_id_b19dcd_idx",
"student_courseenrollment_course_id_a6f93be8"
],
"key": "student_courseenrollment_user_id_course_id_5d34a47f_uniq",
"used_key_parts": [
"user_id",
"course_id"
],
"key_length": "771",
"ref": [
"edxapp.enterprise_enterprisecustomeruser.user_id",
"edxapp.enterprise_enterprisecourseenrollment.course_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"index_condition": "(`edxapp`.`U0`.`user_id` = `edxapp`.`enterprise_enterprisecustomeruser`.`user_id`)",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.10",
"prefix_cost": "0.35",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"course_id",
"mode",
"user_id"
]
}
}
}
}
]
}
}
} |
0x29a
force-pushed
the
0x29a/enterprise-course-enrollments-api-improvements
branch
7 times, most recently
from
39af57c
to
6422e62
Compare
|
@feanil, I went ahead and added the suggested improvements, see these commits:
I didn't fix the issue with enrollment users string representation, as it's beyond the scope of this PR and we currently don't have budget for this. Also I rebased the branch and moved the changelog item to the "Unreleased" section, just to avoid fixing conflicts again. cc @ormsbee |
ormsbee
approved these changes
Jan 3, 2024
|
|
||
| class Meta: | ||
| unique_together = (('enterprise_customer_user', 'course_id',),) | ||
| app_label = 'enterprise' | ||
| ordering = ['created'] | ||
| ordering = ['id'] |
There was a problem hiding this comment.
Please add a comment to the effect that the originally intended ordering is by created, but there was never an index added for it, and that we're ordering by id because we believe it's equivalent and doesn't require an expensive migration that might require downtime.
At some point, it's possible that created will become editable, a bug will be filed, and someone is going to look at this and think, "oh, easy fix, I'll just make this created instead of id" without realizing the potential consequences.
There was a problem hiding this comment.
Done, @ormsbee, thank you for taking time to review this at last.
0x29a
force-pushed
the
0x29a/enterprise-course-enrollments-api-improvements
branch
from
6422e62
to
aa3f73b
Compare
|
@0x29a it looks like there is a conflict on the branch, can you rebase and then I can merge this. Can you also please, bump the version for this so we can do a release of this code. |
0x29a
force-pushed
the
0x29a/enterprise-course-enrollments-api-improvements
branch
3 times, most recently
from
3c45f1a
to
cd01e5c
Compare
@feanil, done. |
|
@0x29a sorry, but based on the content of the PR and semver, we should bump the minor version not the patch version, you're adding a new feature but it's backwords compatible. Sorry I was not clear about that before. |
- Allows Enrollment API Admin to see all enrollments. - Makes the endpoint return more fields, such as: enrollment_track, enrollment_date, user_email, course_start and course_end. - Changes EnterpriseCourseEnrollment's default ordering from 'created' to 'id', which equivalent, but faster in some cases (due to the existing indes on 'id'). Co-authored-by: Maxim Beder <[email protected]>
0x29a
force-pushed
the
0x29a/enterprise-course-enrollments-api-improvements
branch
from
cd01e5c
to
237e4a1
Compare
|
@0x29a 🎉 Your pull request was merged! Please take a moment to answer a two question survey so we can improve your experience in the future. |
0x29a
deleted the
0x29a/enterprise-course-enrollments-api-improvements
branch
|
Thanks for merging this, @feanil! |
|
No problem! Thanks for your patience, it took a while but I'm glad we got it landed, improving our APIs is really useful but it can be hard to assess he performance impact so I appreciate you taking all the extra time and effort needed to land this. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
Description
edx-enterpriseprovides the API to query enrollments to courses at the URL:<lms_base_url>/enterprise/api/v1/enterprise-course-enrollment/. However, the information returned by the API is extremely limited. For a non-staff user it returns only the enrollment data of self, and for a staff user, it returns all the enrollments with very limited data.Example response with 1 enrollment:
{ "next": null, "previous": null, "count": 1, "num_pages": 1, "current_page": 1, "start": 0, "results": [ { "enterprise_customer_user": 3, "course_id": "course-v1:ENT+ENT101+2023" } ] }This is limiting for 2 reasons:
This PR updates the enterprise course enrollment endpoint to use rules.py,
enterprise.can_enroll_learnersin particular. Now, only staff or users withenrollment_api_adminrole can see all enterprise course enrollments under their enterprise.Also, now the endpoint returns more fields similarly to the learner-summary API. Example:
{ "next": null, "previous": null, "count": 3, "num_pages": 1, "current_page": 1, "start": 0, "results": [ { "enterprise_customer_user": 12, "course_id": "course-v1:ABC+ABC+ABC", "enrollment_date": "2023-02-04T18:44:54.412228Z", "enrollment_track": "audit", "user_email": "[email protected]", "course_start": "2019-01-01T00:00:00Z", "course_end": "2024-06-14T00:00:00Z" }, { "enterprise_customer_user": 14, "course_id": "course-v1:ABC+ABC+ABC", "enrollment_date": "2023-02-05T16:01:28.044111Z", "enrollment_track": "audit", "user_email": "[email protected]", "course_start": "2019-01-01T00:00:00Z", "course_end": "2024-06-14T00:00:00Z" }, { "enterprise_customer_user": 11, "course_id": "course-v1:ABC+ABC+ABC", "enrollment_date": "2023-02-04T17:52:40.554691Z", "enrollment_track": "audit", "user_email": "[email protected]", "course_start": "2019-01-01T00:00:00Z", "course_end": "2024-06-14T00:00:00Z" } ] }Testing instructions
Before testing, you'll need to install
edx-enterpriseto your devstack:cd $DEVSTACK_ROOT/srcgit clone -b 0x29a/enterprise-course-enrollments-api-improvements [email protected]:open-craft/edx-enterprise.gitcd ../devstackmake lms-shellcd /edx/src/edx-enterprise/pip uninstall edx-enterprisepip install -e .Then, you'll need to create:
[email protected]and[email protected].<LMS>/admin/enterprise/enterprisecustomer/Then, you have to link both users to the course here:
<LMS>/admin/enterprise/enterprisecustomer/<ENTERPRISE_CUSTOMER_UUID>/manage_learners.Also, one of the users has to have
enrollment_api_adminrole assigned. You can do this here:<LMS>/admin/enterprise/enterprisefeatureuserroleassignment/.Now, test that:
<LMS>/enterprise/api/v1/enterprise-course-enrollment/.<LMS>/enterprise/api/v1/enterprise-course-enrollment/.Checklists
Merge checklist:
requirements/*.txtfiles)base.inif needed in production but edx-platform doesn't install ittest-master.inif edx-platform pins it, with a matching versionmake upgrade && make requirementshave been run to regenerate requirementsmake statichas been run to update webpack bundling if any static content was updated./manage.py makemigrationshas been run./manage.py lms makemigrationsin the shell.Post merge:
(so basically once your build finishes, after maybe a minute you should see the new version in PyPi automatically (on refresh))
make upgradein edx-platform will look for the latest version in PyPi.Private-ref