Moodle Course Custom SQL Report: Select students, group they belong to and final assessment grade for a specific course

👋

I'm pretty knew to programming in general, and I wanted to try to build a Moodle Custom SQL query, but I've had no success so far.

I wanted to SELECT a few fields from the user table, as well as the group name the user belongs to and a specific quizz final grade, all of this in the context of a specific course.

This is what I've achieved so far:

SELECT
    u.firstname AS "First Name",
    u.lastname AS "Last Name",
    u.username AS "Username",
    u.email AS "User Email",
    g.name AS "Group Name",
    CASE
        WHEN gi.grade IS NULL THEN 'Ungraded'
        ELSE gi.grade
    END AS "Quiz Grade"
FROM
    prefix_user AS u
INNER JOIN
    prefix_user_enrolments AS ue ON u.id = ue.userid
INNER JOIN
    prefix_enrol AS e ON ue.enrolid = e.id
INNER JOIN
    prefix_course AS c ON e.courseid = c.id
LEFT JOIN
    prefix_groups_members AS gm ON u.id = gm.userid
LEFT JOIN
    prefix_groups AS g ON gm.groupid = g.id
LEFT JOIN
    prefix_quiz AS q ON c.id = q.course
LEFT JOIN
    prefix_quiz_grades AS gi ON q.id = gi.id AND u.id = gi.userid
INNER JOIN
    prefix_role_assignments AS ra ON ra.userid = u.id
INNER JOIN
    prefix_context AS ctx ON ra.contextid = ctx.id
INNER JOIN
    prefix_role AS r ON ra.roleid = r.id
WHERE
    c.id = %%COURSEID%%
    AND q.name = 'Evaluación final'
    AND r.shortname = 'student'
ORDER BY
    u.lastname, u.firstname

The thing is that I'm getting a lot of repeated values (quiz is set to only one attempt) and some of them with different group name values (courses are not reused).

I'd really appreciate any correction/help you can provide.

Thanks a lot in advance!🙌

You are on the right track, but it might be returning repeated values because of multiple role assignments, group memberships... You should use the DISTINCT keyword to select unique rows, and ensure that you're joining tables correctly.

you can try this

SELECT DISTINCT
    u.firstname AS "First Name",
    u.lastname AS "Last Name",
    u.username AS "Username",
    u.email AS "User Email",
    g.name AS "Group Name",
    CASE
        WHEN gi.grade IS NULL THEN 'Ungraded'
        ELSE CAST(gi.grade AS CHAR)
    END AS "Quiz Grade"
FROM
    prefix_user AS u
INNER JOIN
    prefix_user_enrolments AS ue ON u.id = ue.userid
INNER JOIN
    prefix_enrol AS e ON ue.enrolid = e.id AND e.courseid = %%COURSEID%%
LEFT JOIN
    prefix_groups_members AS gm ON u.id = gm.userid
LEFT JOIN
    prefix_groups AS g ON gm.groupid = g.id
LEFT JOIN
    prefix_quiz AS q ON e.courseid = q.course AND q.name = 'Evaluación final'
LEFT JOIN
    prefix_quiz_grades AS gi ON q.id = gi.quiz AND u.id = gi.userid
INNER JOIN
    prefix_role_assignments AS ra ON ra.userid = u.id
INNER JOIN
    prefix_context AS ctx ON ra.contextid = ctx.id AND ctx.instanceid = %%COURSEID%%
INNER JOIN
    prefix_role AS r ON ra.roleid = r.id AND r.shortname = 'student'
ORDER BY
    u.lastname, u.firstname

here you also caste the grade to a character type in the CASE statement to have same data types in the result.