General developer forum

Moodle query: Return only students without group in course

 
Picture of Sergio R. B. Junior
Moodle query: Return only students without group in course
 

Hello,


I'm trying to create a query that returns me the courses and groups of students in these courses, but I want to return the students who are not in any group too, is it possible?

My first try query:


SELECT u.id,u.firstname,u.lastname,u.email,g.name,g.courseid,c.fullname  FROM mdl_groups_members m 

LEFT JOIN mdl_groups g ON g.id=m.groupid 

LEFT JOIN mdl_user u ON u.id=m.userid

LEFT join mdl_course c ON c.id = g.courseid

WHERE u.username =  ''


Thanks.

 
Average of ratings: -
Davo
Re: Moodle query: Return only students without group in course
Core developersParticularly helpful MoodlersPlugin developers

If you want all users who are enrolled in the course, then you're going to need something like this (written off the top of my head and so may have typos):

SELECT u.id, u.firstname, c.id, g.name
FROM mdl_course c
JOIN mdl_enrol e ON e.course = c.id
JOIN mdl_user_enrolments ue ON ue.enrolid = e.id
JOIN mdl_user u ON u.id = ue.userid
LEFT JOIN mdl_groups_members gm ON gm.userid = u.id
LEFT JOIN mdl_groups g ON g.id = gm.groupid

This won't be limited to students - to do that, you'd have to also join the mdl_course table to the mdl_context table (ctx.instanceid = c.id AND ctx.contextlevel = 50), then join to the mdl_role_assignments table (ra.contextid = ctx.id AND ra.userid = u.id), then join to the mdl_role table (r.id = ra.roleid).

You may also want to take into account whether or not the enrolment is suspended, whether or not the enrolment method is enabled and whether the enrol start / end time has been reached.

https://stackoverflow.com/questions/22161606/sql-query-for-courses-enrolment-on-moodle has an example query that might be helpful.

 
Average of ratings: Useful (1)
Picture of Sergio R. B. Junior
Re: Moodle query: Return only students without group in course
 

Thanks for your help! 


However when I make a query of a specific student that I just removed from a group it still returns me the group in which it was.

I believe I'm forgetting some field that returns if this student is active in this group.


 
Average of ratings: -
Picture of Sergio R. B. Junior
Re: Moodle query: Return only students without group in course
 

If you test the query you did, it doesn't return the data exactly. If the student is in a group in another course he replies the id of some group in which it is in the registry where it should be empty.

 
Average of ratings: -
Picture of Sergio R. B. Junior
Re: Moodle query: Return only students without group in course
 

Hello,

I finally managed to fix the query big grin


SELECT u.id,u.firstname,c.fullname,g.timecreated,g.timemodified,c.id AS curso,g.id grupo,g.name

FROM mdl_course c

JOIN mdl_enrol e ON e.courseid = c.id

JOIN mdl_user_enrolments ue ON ue.enrolid = e.id

JOIN mdl_user u ON u.id = ue.userid

LEFT JOIN mdl_groups_members gm ON gm.userid = u.id

LEFT JOIN mdl_groups g ON g.id = gm.groupid AND g.courseid = c.id


I forgot to link mdl_course with mdl_groups mixed

 
Average of ratings: Useful (1)
Davo
Re: Moodle query: Return only students without group in course
Core developersParticularly helpful MoodlersPlugin developers

Glad you managed to correct it. Yes, I managed to miss the g.courseid bit out - I should have remembered that (but, as I said, it was written off the top of my head without testing).

 
Average of ratings: -
Picture of Eoin Campbell
Re: Moodle query: Return only students without group in course
Core developersParticularly helpful MoodlersPlugin developers

I don't normally like to show off, but in this case I'm making an exception! I recently discovered the MySQL GROUP_CONCAT() function, while trying to create a very similar report. I use the query below in a Configurable Report plugin query, and it concatenates all the students group names into a single row, which is very neat. This query also limits the participants to Students, and still includes those students who are not in any groups.

Replace {course} with mdl_course etc. to run this query directly in a MySQL command-line client.


-- All student participants, group memberships in a single line, including students not in any groups
SELECT
    u.lastname,
    u.firstname,
    SUBSTRING(FROM_UNIXTIME(g.timecreated), 1, 10) AS "Created",
    SUBSTRING(FROM_UNIXTIME(g.timemodified), 1, 10) "Modified",
    GROUP_CONCAT(DISTINCT CONCAT(" ", g.name) ORDER BY g.name) AS "Groups"
FROM
    {course} AS c
    INNER JOIN {context} AS ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50)
    INNER JOIN {role_assignments} AS ra ON (ra.contextid = ctx.id)
    INNER JOIN {role} AS r ON (r.shortname = 'student' AND r.id = ra.roleid)
    INNER JOIN {user} AS u ON (u.id = ra.userid and u.deleted = 0)
    LEFT JOIN {groups_members} gm ON gm.userid = u.id
    LEFT JOIN {groups} g ON g.id = gm.groupid AND g.courseid = c.id
WHERE
    c.id = %%COURSEID%%
GROUP BY
    u.id
ORDER BY
    u.lastname, u.firstname 


 
Average of ratings: -
Picture of Sergio R. B. Junior
Re: Moodle query: Return only students without group in course
 

Thanks Eoin,


Your query will be very useful for other queries I have in mind  Yes

 
Average of ratings: -