SQL query for Courses Enrolment on Moodle

Question

I was looking for the proper SQL queries for retrieving all students enrolled in a certain course, or all courses a certain student has enrolled in, on Moodle.

I have found a few solutions from the Internet, and most of them suggest joining these tables:
context, role_assignments, course, user, role

But then when I looked at the database, I found that there is a table named user_enrolments, and it seems to me that I could get the results by joining the following tables:
user_enrolments, user, course, enrol

For example,

SELECT u.id, c.id
FROM mdl_user u
INNER JOIN mdl_user_enrolments ue ON ue.userid = u.id
INNER JOIN mdl_enrol e ON e.id = ue.enrolid
INNER JOIN mdl_course c ON e.courseid = c.id

and

SELECT u.id, c.id
FROM mdl_user u
INNER JOIN mdl_role_assignments ra ON ra.userid = u.id
INNER JOIN mdl_context ct ON ct.id = ra.contextid
INNER JOIN mdl_course c ON c.id = ct.instanceid
INNER JOIN mdl_role r ON r.id = ra.roleid
WHERE r.id = 5

(where 5 is the id for the role student)

These 2 queries give me the SAME set of results. (tested on a small set of data only)

So I would like to ask what are the differences between the two approaches?
Thank you for any assistance in advance.

Answer 1

The first query gives you a list of users who are enroled on the course, whatever role they have assigned to them (it is possible to be enroled on a course and have no role assigned at all).

The second query shows all the users who have role 5 assigned to them at the course level. It is possible (though unusual) to have a role assigned at the course level, without actually being enroled in the course itself.

However, both of the queries are flawed.

The first query could return duplicate results if the user was enroled in a course via more than one enrolment method (unusual, but possible). It also fails to take into account the following:

• The enrolment plugin may be disabled at site level
• The enrolment plugin may be disabled at the course level (check for 'e.status = 0' to only find active enrolment plugins)
• The enrolment may be time-limited - the user's enrolment may have expired (check for 'ue.timeend = 0 OR ue.timeend > NOW()' to find only unexpired enrolments)

The second query assumes that the student role is id 5 (and also that there are no other roles, based on the student role, that are in use). I would normally either use an extra query to check the id of the 'student' role in the table 'mdl_role' and then use that value, or change the last couple of lines to the following:

JOIN mdl_role r ON r.id = ra.roleid AND r.shortname = 'student'.

The second query also fails to check the 'contextlevel' - it is possible to have a multiple contexts with the same instance id (as it is possible to have course id 5, course category id 5, user id 5, etc.) - so you need to check that the context found is a 'course' context (contextlevel = 50).

Neither query checks for suspended users or deleted users (although, in the case of deleted users, they should have been automatically unenroled from all courses at the point where they were deleted).

A fully complete solution (possibly overly complex for most situations) would combine both queries together to check the user was enroled and assigned the role of student and not suspended:

SELECT DISTINCT u.id AS userid, c.id AS courseid
FROM mdl_user u
JOIN mdl_user_enrolments ue ON ue.userid = u.id
JOIN mdl_enrol e ON e.id = ue.enrolid
JOIN mdl_role_assignments ra ON ra.userid = u.id
JOIN mdl_context ct ON ct.id = ra.contextid AND ct.contextlevel = 50
JOIN mdl_course c ON c.id = ct.instanceid AND e.courseid = c.id
JOIN mdl_role r ON r.id = ra.roleid AND r.shortname = 'student'
WHERE e.status = 0 AND u.suspended = 0 AND u.deleted = 0
  AND (ue.timeend = 0 OR ue.timeend > UNIX_TIMESTAMP(NOW())) AND ue.status = 0

(Note I haven't double-checked that query extensively - it runs, but you would need to carefully cross-reference against actual enrolments to check I hadn't missed anything).

Answer 2

The following code generates a list of all your courses together with how many students are enrolled in each. Useful to find out if you have any courses with no one enrolled.

My Answer :

SELECT cr.SHORTNAME, 
       cr.FULLNAME, 
       COUNT(ra.ID) AS enrolled 
FROM   `MDL_COURSE` cr 
       JOIN `MDL_CONTEXT` ct 
         ON ( ct.INSTANCEID = cr.ID ) 
       LEFT JOIN `MDL_ROLE_ASSIGNMENTS` ra 
              ON ( ra.CONTEXTID = ct.ID ) 
WHERE  ct.CONTEXTLEVEL = 50 
       AND ra.ROLEID = 5 
GROUP  BY cr.SHORTNAME, 
          cr.FULLNAME 
ORDER  BY `ENROLLED` ASC