SQL Query for Courses Enrolment on Moodle

SQL query for Courses Enrolment on Moodle

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).

Moodle SQL query to retrieve all users enrolled to a course in the past 90 days

the solution is here:

SELECT DISTINCT u.id AS userid, c.id AS courseid, DATE_FORMAT(FROM_UNIXTIME(ue.timecreated),'%m/%d/%Y') AS timecreated
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 > NOW( )
)
AND ue.status =0
AND courseid =11
AND FROM_UNIXTIME(ue.timecreated) BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()

First Access date or Enrolled date for a specific course

You cannot get first course access in moodle 3+. This action is not logged anymore, you can only get last access.

As for enrolment time (TimeCreated), try this:

SELECT u.username, u.lastname, u.firstname, c.fullname, DATE_FORMAT(FROM_UNIXTIME(ue.timecreated), '%Y-%m-%d %H:%i') AS 'Timecreated' FROM prefix_user_enrolments ue LEFT JOIN prefix_enrol e ON (ue.enrolid = e.id) LEFT JOIN prefix_course c ON (e.courseid = c.id) LEFT JOIN prefix_user u ON (ue.userid = u.id)

First access actually makes no sense, since enrolment time will always be the earliest date between two. Even if user uses self-enrolment. Guest course access is probably the only exception, but it doesn't require user id, so it will make your report inconsistent anyway. So 99% of the time you would want to check upon user enrolments exactly, and the earliest date is always enrolment time.

If your moodle version is less than 3, you may get first access via prefix_log

Moodle SQL number of students

SELECT c.id, c.fullname, COUNT(*)
FROM mdl_course c
JOIN (
    SELECT DISTINCT e.courseid, ue.userid /* Distinct because one user can be enrolled multiple times */
    FROM mdl_enrol e
    JOIN mdl_user_enrolments ue ON ue.enrolid = e.id AND ue.timestart BETWEEN :starttime AND :endtime
) students ON students.courseid = c.id
GROUP BY c.id, c.fullname

The time enrolled is stored in mdl_user_enrolments.timestart

There can be multiple enrolment methods on a course and it is possible that a student is enrolled more than once on a course using different methods. So you'll need to us a distinct join.

You might also want do add some additional filters. eg:

Check the user hasn't been suspended

JOIN mdl_user u ON u.id = ue.userid AND u.suspended = 0

The user enrolment is active

AND ue.status = 0 /* ENROL_USER_ACTIVE */

The Enrolment method is active

AND e.status = 0 /* ENROL_INSTANCE_ENABLED */

---

Related Topics