Here's the official story.

Users take up one or more specific roles in a 'context', e.g. they are student (role) in a course (context). Contexts are divided into levels:

From lib/accesslib.php:

define(‘CONTEXT_SYSTEM’, 10);
define(‘CONTEXT_PERSONAL’, 20);
define(‘CONTEXT_USER’, 30);
define(‘CONTEXT_COURSECAT’, 40);
define(‘CONTEXT_COURSE’, 50);
define(‘CONTEXT_GROUP’, 60);
define(‘CONTEXT_MODULE’, 70);
define(‘CONTEXT_BLOCK’, 80);

This means that, for instance, contextlevel 50 is about courses.

There are five important tables involved in enrollment. Here's the schema for the five of them:

(Schema created with http://ondras.zarovi.cz/sql/demo/)

If you are assigning a user as a student to a course, you have to make a new role_assignments record. To do that, you need look up:

  • role_assignments.roleid: retrieve by role.shortname ('student')
  • role_assignments.contextid: retrieve by context.contextlevel (always 50 for a course, see above) and instanceid (course.id)
  • role_assignments.userid: user.id of the, uh, user.

Please note that there's an additional column in role_assignments called modifierid, destined for the user id of the user who added the new user (usually a course administrator or an admin user).

In addition, for each user you have to create a record in user_enrolments. The user_enrolments.enrolid is a foreign key pointing to enrol.id. The column user_enrolments.status normally has one of two values: 0 means active enrolment; 1 means suspended - anything else means inactive. The enrol.enrol column stores the enrolment plugin name (usually 'manual').

WARNING: as far as I can tell, Moodle 2.0 and 2.1 don't use enrol.roleid in any meaningful way. For instance, if you enrol a user as a teacher, the roleid will still point to a student for that course. In other words: user_enrolment.enrolid points to an enrol record which points to a student role, even though the user is actually a teacher in that course.

The column enrol.roleid is probably only used to store the default roleid for a given enrollment method.

Role Assignments Are Completely Separate from Enrollments

A user can have an active or inactive enrollment status in a course. This enrollment status is in no way linked to the role(s) a user may have in a course.

In other words, you can be a student who is enrolled 'manually', and also through a cohort sync enrollment. If the cohort enrollment is deleted, you'll still have the student role in that course.

Get All Active Users in A Course, by Capability

Here's how to retrieve all active users (i.e. they haven't completed the course yet), by a specific (i.e. single) capability:

$active_users = $DB->get_records_sql("
    SELECT DISTINCT(u.id), u.firstname, u.lastname, u.email,  con.instanceid as course_id
    FROM mdl_context AS con, mdl_user AS u, mdl_role_assignments AS ra, mdl_course AS c, mdl_role_capabilities AS rc
    WHERE con.contextlevel = 50 
    AND ra.userid = u.id 
    AND ra.contextid = con.id
    AND NOT EXISTS (SELECT * FROM mdl_course_completions AS cc WHERE cc.userid = ra.userid AND cc.course = con.instanceid AND cc.timecompleted > 0)
    AND EXISTS (SELECT * FROM mdl_enrol AS e, mdl_user_enrolments AS ue WHERE e.id = ue.enrolid AND e.courseid = con.instanceid AND ue.userid = ra.userid AND ue.status = 0)
    AND NOT u.deleted = 1
    AND c.id = con.instanceid
    AND c.id = :course_id
    AND rc.permission = 1
    AND ra.roleid = rc.roleid
    AND rc.capability LIKE :capability_receivereminders
    AND u.id NOT IN ({$CFG->siteadmins})",
        'course_id' => $course_id,
        'capability_receivereminders' => 'local/remindermailer:receivereminders'

By the way, you'd expect you'd have to add in this clause:

AND rc.contextid = con.id

But for some unknown reason, Moodle sets the role_capabilities.contextid to 1 in almost all cases (except where permission != 1). I assume that a permission can be withdrawn in a more specialized local context.

Get All Inactive Users

Conversely, to get all users who are inactive, change the EXISTS clause on the user enrollment status to NOT EXISTS:

AND NOT EXISTS (SELECT * FROM mdl_enrol AS e, mdl_user_enrolments AS ue WHERE e.id = ue.enrolid AND e.courseid = con.instanceid AND ue.userid = ra.userid AND ue.status = 0)

Properties of Cohort Sync

If you enroll a user through cohort sync, please keep in mind that unenrolling that user will result in the removal of all grades, user/course specific settings, group membership, etc..

The grades WILL be stored if you add in another type of enrollment. For instance, if the user is enrolled through a cohort sync and through a manual enrollment, then the user data will be stored if you remove the user from the cohort.

The grades will still be invisible if the alternative (i.e. non-cohort) enrollment is set to 'Suspended' (inactive) status.

Personal Tools