Roles and permissions

Hi Mark,

This might help. The following SQL is what I use to get lists of courses in reverse order of number of editors. Not what you want at all, but it illustrates how the enrolments are stored.

The important tables are 'role_assignments' and 'context'. These link the user and course tables in a chain, as: user->role_assignments->context->course. You also need to know that a context level of 50 means "at the course level", and that role IDs of 3,4 and 5 correspond to teachers, non-editing teachers and students respectively.

SELECT mdl_course.fullname as coursename, COUNT(mdl_user.id) as noofeditors, mdl_course.metacourse
FROM mdl_role_assignments, mdl_user, mdl_context, mdl_course
WHERE mdl_role_assignments.roleid = 3
AND mdl_context.contextlevel = 50
AND mdl_role_assignments.contextid = mdl_context.id
AND mdl_role_assignments.userid = mdl_user.id
AND mdl_context.instanceid = mdl_course.id GROUP BY mdl_course.id
ORDER BY noofeditors DESC

Hopefully you can see how to proceed from this!

Steve

I think this sql will give you the teacher(s), followed by the students, for each moodle course in your database. 

SELECT c.fullname as coursename, r.name as rolename, u.lastname as usersname FROM `mdl2_course` c
JOIN mdl2_context con
ON con.instanceid = c.id
JOIN mdl2_role_assignments ra
ON con.id = ra.contextid
JOIN mdl2_role r
ON ra.roleid = r.id
JOIN mdl2_user u
ON u.id = ra.userid
WHERE contextlevel = 50
AND (r.id = 3 OR r.id = 5)
ORDER BY c.fullname, rolename DESC

Role id 3 is teacher, role id 5 is student.  The context levels are in lib/accesslib.php  from your root directory (if you have access to the files).  contextlevel 50 is the value given to the constant CONTEXT_COURSE in this file.