CODEWITHSUNDEEP
sqldatabasemoodle
mickhayworth
mickhayworth

Reputation: 36

Enrolled users SQL query for Moodle course

I have having trouble compiling an SQL query to find a count of the users enrolled in a specific moodle course.

I have used and played around with the query from this thread: SQL query for Courses Enrolment on Moodle

The query from there reproduced below isn't returning an accurate result:

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

The following gets me the right number but from what I have read it appears incorrect (I am working with a dataset of 3 users):

SELECT COUNT(*)

FROM mdl_course AS course 
JOIN mdl_enrol AS en ON en.courseid = course.id
JOIN mdl_user_enrolments AS ue ON ue.enrolid = en.id
JOIN mdl_user AS user2 ON ue.userid = user2.id

Any help much appreciated.

Upvotes: 1

Views: 4041

Answers (1)

Panos Paralakis
Panos Paralakis

Reputation: 29

You can get enrolled users by calling get_enrolled_users defined at accesslib.php file

There is also a count_enrolled_users method that you can use

Upvotes: 1

Related Questions