query to find how student enrolled many different ways and update them via sql to use 'manual' enrolment only

query to find how student enrolled many different ways and update them via sql to use 'manual' enrolment only

Jerry Lau -
回帖数：6

Hello folks.

We are on 3.9.4.x and I noticed in a lot of courses, we have students enrolled in more than one method!

In other words, the student was enrolld in a course via more than one enrolment method (unusual, but possible). 

see image attached.

How can I write an sql to clean this up and change all enrolment types who have either or all the courses' "database", "selfenrol" enrolment id and update it  to their course's 'manual' enrolment id?

I believe i need to loop through them but don't know which tables I need to hit and update them.

thanks!

capture multiple enrolment methods




平均分： -
回复Jerry Lau

Re: query to find how student enrolled many different ways and update them via sql to use 'manual' enrolment only

Rick Jerz -
Particularly helpful Moodlers的头像 Testers的头像
Perhaps a good place to start is by looking over some of the contributed SQL reports, at https://docs.moodle.org/311/en/ad-hoc_contributed_reports.

Do you really have all these "students" with the same "student" name? If so, you might want to explore why?
平均分： -
回复Rick Jerz

Re: query to find how student enrolled many different ways and update them via sql to use 'manual' enrolment only

Jerry Lau -
yes they are legitimate.. same username just don't know why there are multiple ways to enroll. How can we clean this up? bug in Moodle? The adhoc reports don't seem to have the one I was looking for.

I need to know which tables are involved and their purpose.. user_enrolments, enrol, role_assignments (what is this for and is this needed)? and the columns or fields' purpose.

thanks
平均分： -
回复Jerry Lau

Re: query to find how student enrolled many different ways and update them via sql to use 'manual' enrolment only

Rick Jerz -
Particularly helpful Moodlers的头像 Testers的头像
There are probably many ways to approach this problem. The thing that I would do first is to go into a course and see what kinds of enrollment methods are being used. In my one example, below, I have only three enrollment methods. My guess is that you have at least 3 or 4. From my graphic, you can see how many students are enrolled using each enrollment method. Yours, of course, will look different. But a good place to start would be to ask "Why?" Then, I would pick one of these students and see which enrollment method(s) they have. Since you have multiple students with the exact same name, your probing (is a mess and) will take some time.

Did you explore the contributed SQL reports?
平均分： -
回复Rick Jerz

Re: query to find how student enrolled many different ways and update them via sql to use 'manual' enrolment only

Rick Jerz -
Particularly helpful Moodlers的头像 Testers的头像

Here's the graphic.

附件 enrollments.png
平均分： -
回复Rick Jerz

Re: query to find how student enrolled many different ways and update them via sql to use 'manual' enrolment only

Jerry Lau -
thank you Rick. yes I have explored the contributed reports but none outputs their enrolment types. Also saw the graphic you've attached.

I wonder why there was more than one enrolment type as probably they just want to make it easier for people to enrol be it database method, self, metacourse links, etc.

may be it's not such a bad idea to leave it as is
平均分： -
回复Jerry Lau

Re: query to find how student enrolled many different ways and update them via sql to use 'manual' enrolment only

Rick Jerz -
Particularly helpful Moodlers的头像 Testers的头像
What I once discovered is that as I was writing queries to see who was in a course, students with double-enrollments showed twice. What I had discovered is that some students would self-enrol, then email me saying they couldn't get in. My quick fix was to manually enroll them. However, this was creating double enrollments. Now, I simply make sure that I have students enrolled only once.

(Seems like I once wrote a query for this, but can't find it.)

Just created it...  but a little rough.

I think that this query will show students who have more than one enrollment in a course.

SELECT c.fullname AS Course_Name,
       u.id, u.lastname, u.firstname,
       count(u.id ) AS COUNT
FROM mdl_course c
JOIN mdl_enrol en ON en.courseid = c.id
JOIN mdl_user_enrolments ue ON ue.enrolid = en.id
JOIN mdl_user u ON u.id = ue.userid
JOIN mdl_role_assignments asg ON u.id = asg.userid
JOIN mdl_context con ON asg.contextid = con.id
WHERE ue.status = 0
  AND en.courseid = con.instanceid
GROUP BY c.id, u.id
having count > 1
ORDER BY c.id, count(*) DESC
平均分：Useful (2)