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

Question

I need an SQL query that will give me all users enrolled to a course in the last 90 days that are not suspended.

I have done some searching and found this:

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 > NOW()) AND ue.status = 0

I can return results from a specific course by adding AND courseid = 11 to the end of the query, but i'm still struggling with the date range.

I've tried using:

AND ue.timecreated BETWEEN NOW() - INTERVAL 90 DAY AND NOW()

but that is returning no results i even tried to add 2000 day range and it returns nothing

this is my current modified query:

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

And that return nothing, as soon as I remove the second-last line that contains the date range I receive all the results inside course id 11.

Basically I need:

A list of all users that are enrolled in to a specific course in the last 90 days that is not suspended or deleted.

Results would be something like:

course id: 20
user id: 200, 533, 221, 22, 356

Answer 1

The times in Moodle are stored as integers and are a UTC unix timestamp. So the date functions in MySql won't work - unless they are converted using FROM_UNIXTIME().

If you want all users enrolled, not just students, then this is simpler.

SELECT ue.id, e.courseid, ue.userid, e.enrol AS enrolmethod, FROM_UNIXTIME(ue.timecreated)
FROM mdl_user_enrolments ue
JOIN mdl_enrol e ON e.id = ue.enrolid AND e.status = 0
JOIN mdl_user u ON u.id = ue.userid AND u.deleted = 0 AND u.suspended = 0
WHERE ue.timecreated >= 90 * 24 * 60 * 60 AND ue.status = 0

A user could be enrolled using more than one method though. So if you just want the userid and courseid then use a group by rather than a distinct.

SELECT CONCAT(e.courseid, '_', ue.userid) AS uniqueid, e.courseid, ue.userid, FROM_UNIXTIME(MAX(ue.timecreated))
FROM mdl_user_enrolments ue
JOIN mdl_enrol e ON e.id = ue.enrolid AND e.status = 0
JOIN mdl_user u ON u.id = ue.userid AND u.deleted = 0 AND u.suspended = 0
WHERE ue.status = 0
GROUP BY e.courseid, ue.userid
HAVING MAX(ue.timecreated) >= 90 * 24 * 60 * 60

Answer 2

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