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.
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.
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.
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.
Hello,
I finally managed to fix the query
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
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).
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
Thanks Eoin,
Your query will be very useful for other queries I have in mind