0

I have a question relating to a SQL command - I need to get a list of all students that are enrolled in a Moodle course, but I also need to find out how each student was enrolled i.e. Manual, Banner Plugin, etc, this is the SQL that I currently have but I cant seem to get the enrol part ?

Were on Moodle 3.1

Thanks Conor.

SELECT
c.id AS courseid, 
c.idnumber AS crn,
c.fullname, 
u.username, 
u.firstname, 
u.lastname, 
u.email                            
FROM mdl_role_assignments ra 
JOIN mdl_user u ON u.id = ra.userid
JOIN mdl_role r ON r.id = ra.roleid
JOIN mdl_context cxt ON cxt.id = ra.contextid
JOIN mdl_course c ON c.id = cxt.instanceid
WHERE ra.userid = u.id                              
AND ra.contextid = cxt.id
AND cxt.contextlevel = 50
AND cxt.instanceid = c.id
AND roleid = 46
ORDER BY c.id
Conor
  • 1

2 Answers2

0

Your query doesn't show students who are enroled on a course, it only shows users who have the role with id '46' assigned at the course level (I assume on your system '46' is the student role you are interested in).

My answer to a similar question can be found here: https://stackoverflow.com/a/22166653/1148411

Starting with that query, just add 'e.enrol AS enrolmenttype' to the list of fields to return and that should be what you want.

Community
  • 1
  • 1
davosmith
  • 6,037
  • 2
  • 14
  • 23
  • Thanks davosmith, I got it eventually with the query below, your right I was missing the enrol type from the mdl_enrol - and yes your right '46' is our student role we were looking at. – Conor Oct 26 '16 at 08:11
  • SELECT c.idnumber, c.shortname, e.enrol, u.username, u.firstname, u.lastname FROM mdl_enrol e INNER JOIN mdl_course c ON (c.id = e.courseid) INNER JOIN mdl_user_enrolments ue ON (e.id = ue.enrolid) INNER JOIN mdl_user u ON (ue.userid = u.id) WHERE c.idnumber REGEXP '\.2015$' ORDER BY c.idnumber – Conor Oct 26 '16 at 08:13
0

This query solved the problem, we end the module ID with the academic year '2016' in this case.

SELECT c.idnumber, c.shortname, e.enrol, u.username, u.firstname, u.lastname FROM mdl_enrol e
INNER JOIN mdl_course c ON (c.id = e.courseid)
INNER JOIN mdl_user_enrolments ue ON (e.id = ue.enrolid)
INNER JOIN mdl_user u ON (ue.userid = u.id) 
WHERE c.idnumber REGEXP '\.2015$'
ORDER BY c.idnumber
Conor
  • 1