I'm trying without success to get a list of all users enrolled in any of the courses in my moodle, that have the role "no roles" , this has happend because of an error from my part in administrating moodle: instead of disabilitating the capability for the role teacher that allow them to enrol manually users in the course, I went in "site administration ==> users ==> permissions ==> define roles ==> allow role assignment" and deselected all the checkboxes for the role of teacher, by doing it this way, the teacher could still view and click the button "enrol users" in participants section of course but no role was available to be selected so I thought that was it, what a fool I was, instead the teacher could still complete the enrolment but with no role selected and they kept doing it until i found it, that ended in the users being enroled with the "no roles" label, this creates a lot of problems for some activities in the course.
So i was trying to get a list of all of this users with this particular role in the database, in order to delete their enrolment at once (I have more than 1000 courses..) but this users do not have a record in mdl_role_assignment so i don't know how to identify them, I tried manually
and I tried with this query:
SELECT c.id AS id, c.fullname, u.username, u.firstname, u.lastname, u.email
FROM mdl_role_assignments ra, mdl_user u, mdl_course c, mdl_context cxt
WHERE ra.userid = u.id
AND ra.contextid = cxt.id
AND cxt.contextlevel =50
AND cxt.instanceid = c.id
AND c.shortname ='your course shortname'
It seems those students are invisible, but they have to be somewhere otherwise moodle would'nt be able to keep them enrolled in the course.
Can anyone help?