List of all students with groups, enrolment, and completion information

List of all students with groups, enrolment, and completion information

by Dennis Green -
Number of replies: 5

Hi all. I've run into a challenge due to my limited knowledge of SQL queries.

I'm trying to generate a custom report within each course that gives us a list of users including the group they belong to, how they enrolled, and when they completed. I've been getting close, except that the report only gives me the users who belong to a group, but it excludes users who do not belong to any groups. How can I tweak my query to include all users, whether they belong to a group or not?

I'd ideally like the query to return a full list of all active students, and if the don;t belong to a group, have the group field left blank or return "No Groups" like the participant page in Moodle does. We're running Moodle 3.5

Here's the query I'm running:

SELECT c.shortname AS Course, g.name AS Groupname, u.firstname, u.lastname, er.enrol AS enrolmethod, FROM_UNIXTIME(ue.timecreated) AS Enroldate, FROM_UNIXTIME(cc.timecompleted) AS Finishdate

FROM prefix_course AS c
JOIN prefix_groups AS g ON g.courseid = c.id
JOIN prefix_groups_members AS m ON g.id = m.groupid
JOIN prefix_user AS u ON  m.userid = u.id
JOIN prefix_enrol AS er ON er.courseid = c.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = er.id AND ue.userid = u.id
JOIN prefix_course_completions AS cc ON cc.course = c.id AND cc.userid = u.id
WHERE c.id = %%COURSEID%%

Average of ratings: -
In reply to Dennis Green

Re: List of all students with groups, enrolment, and completion information

by Matthew Willis -

Hi Dennis,

I'm in a similar boat. Did you resolve your issue? If so, please share - this is driving me crazy!

Average of ratings: -
In reply to Matthew Willis

Re: List of all students with groups, enrolment, and completion information

by Miguel González Laredo -
Picture of Plugin developers

Hi Dennis and Matthew. I recommend you to start simplifying our challenge. Try to list only each student and group. And for those students not inside any group, you could use LEFT|RIGTH OUTER JOIN . Let's start understanding OUTER queries: https://www.techonthenet.com/mysql/joins.php

I think that is the first key step for your requirements...

Good luck!

Average of ratings: -
In reply to Dennis Green

Re: List of all students with groups, enrolment, and completion information

by Miguel González Laredo -
Picture of Plugin developers
-- OUTER JOIN WAY
select grp.gName, u.* from
(select c.id as cid, m.id as mid, g.name as gName
FROM mdl_course AS c
JOIN mdl_groups AS g ON g.courseid = c.id
JOIN mdl_groups_members AS m ON g.id = m.groupid
WHERE c.id = %%COURSEID%%)
grp
RIGHT OUTER JOIN
mdl_user AS u ON grp.mid = u.id
Average of ratings: -
In reply to Dennis Green

Re: List of all students with groups, enrolment, and completion information

by Randy Thornton -
Picture of Documentation writers
Miguel is pointing you in the right direction with good advice on making a simple query first just on the groups.

The issue is that JOIN to the Groups tables is an INNER JOIN, and therefore it excludes any data that in not in those tables. Therefore it leaves out all users who belong to no Group since there will not be an entry for them in the Groups Members table (for that one course, that is.) A LEFT join instead of an INNER is probably the solution here.
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: List of all students with groups, enrolment, and completion information

by Matthew Willis -
I managed to successfully write the report that returns the number of users in groups based on their role, but I can't find a way to also include those not in groups. I assume I have to use a subquery that uses the NOT IN function?
select
    cat.name category,
    c.fullname course,
    g.name "group",
    count(distinct gm.userid) groupmembertotal,
    sum(case when ra.roleid = 5 then 1 else 0 end) groupstudentcount, -- mdl_role.id 5 = student role
    sum(case when ra.roleid = 9 then 1 else 0 end) groupspnetcount -- mdl_role.id 9 = spnet custom role
from mdl_course_categories cat
    join mdl_course c on c.category = cat.id
    join mdl_context ct on ct.instanceid = c.id
        and ct.contextlevel = 50
    join mdl_role_assignments ra on ra.contextid = ct.id
    join mdl_groups g on g.courseid = c.id
    join mdl_groups_members gm on gm.groupid = g.id
        and gm.userid = ra.userid
group by ra.contextid, c.fullname, g.name

Ideally, you should be joining ra.roleid to mdl_role so you can pull the role information from mdl_role.shortname instead of ra.roleid (especially if you are rolling this out over different Moodle LMSs that may have different mdl_role.ids), but this works for single-instance Moodle LMSs.

Average of ratings: -