1

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.

Community
  • 1
  • 1
mickhayworth
  • 36
  • 1
  • 3
  • When you say the first query 'isn't returning an accurate result' - what is wrong with the result? Are there extra entries which you don't think should be there? Are there entries missing from the list? Is it the fact that the query only returns 'student' enrolments? Why do you believe the second query is incorrect (and what, specifically, do you think is incorrect about it)? – davosmith Nov 19 '14 at 07:52
  • The first query returns the number as 4. There are only 3 registered users on the site. So I believe it is double counting someone. I think the second query doesn't deal with the context and roles and also doesn't excluded suspended/deleted users. – mickhayworth Nov 19 '14 at 17:11
  • Is the 'guest' user or the 'admin' user being counted in the list? Have you tried going through the list of results from the query to see who it is counting? Have you made sure the 'DISTINCT' is in there, as that should make sure that each combination of user + course only appears once. The second query does not deal with roles, but if you don't care about roles (you want all enrolments, not just students) then you can ignore that part. The 'WHERE' part could just be copied from the first query, if you want to account for deleted/supsended users and expired enrolments. – davosmith Nov 19 '14 at 22:39

1 Answers1

1

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

Panos Paralakis
  • 29
  • 5
  • 1
    This answer does not appear to be helpful. The original question was looking for a SQL query and not a PHP function. – Fred Lackey Dec 16 '21 at 11:17