Query for enrolled users without mdl_role_assignments

Query for enrolled users without mdl_role_assignments

by Thomas College -
Number of replies: 2

Currently we use the following query to identify students who are enrolled in a course, but should not be:

"SELECT u.username, u.firstname, u.lastname, c.idnumber, c.fullname, r.id, r.roleid, r.contextid, r.userid "
"FROM mdl_user AS u INNER JOIN mdl_role_assignments AS r ON u.id = r.userid INNER JOIN mdl_context AS co ON r.contextid = co.id INNER JOIN mdl_course AS c ON c.id = co.instanceid "
"WHERE (c.idnumber = '" & w_course_id & "') AND (r.roleid IN ('3','5')) "
  

The problem we've experienced is when a student is enrolled and active in a course, but does not have a role assigned. 

Does anyone know of a good way to join contextID and instanceID without using mdl_role_assignments?  Is there a different query we could use to identify students even if they do not have a role assigned?

We are currently using Moodle 3.3.3, Windows 2016, and SQL Server 2014

Thanks

Average of ratings: -
In reply to Thomas College

Re: Query for enrolled users without mdl_role_assignments

by Mark Sharp -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

Presumably this is happening because you don't allow teachers/lecturers to enrol students.

We tackle this problem slightly differently by deleting page content and leaving them a message if the user doesn't have a role.

In our theme page_init function we add roles to the body class:

if($roles = get_user_roles($coursecontext)) {
      foreach($roles as $role) {
        $page->add_body_class('courserole-'.$role->shortname);
      }
    } else {
      if (count($roles) === 0 && !is_guest($coursecontext) && !is_siteadmin() && $COURSE->category > 0) {
        $page->add_body_class('courserole-none');
      }
    }

Then using javascript, I check to see if "courserole-none" class exists. If it does I do something like this:

if ($('body').hasClass('courserole-none')) {
    $('.courserole-none div[role="main"]').html(M.util.get_string('noroleerror', 'theme_mytheme'));
}

Basically replace the content with a message. This gets their attention rather quickly.

Average of ratings: -
In reply to Mark Sharp

Re: Query for enrolled users without mdl_role_assignments

by Thomas College -

Hi Mark,

We do allow teachers to enroll students.  This particular situation occurred when a teacher restored a course with user data included.  The students from the old course were enrolled in the new course.  At some point their student role was removed, but their account was not suspended.  As a result, our query didn't find these students and automatically suspend them.

Our thought was to modify our query to identify students that did not have a role applied in addition to those that did, in order to prevent this from happening in the future.  The only way we could see to do this is to join contextID and instanceID without using mdl_role_assignments.

Thanks

Average of ratings: -