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

Question

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!

Answer 1

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.

Answer 2

You will get a lot of duplicates for a few reasons

A user can be enrolled more than once in the same course - they can be enrolled manually but can also be enrolled via other enrolment methods

You might want to narrow down the results by using the EXISTS operator rather than a JOIN

For example

WHERE EXISTS (
    SELECT ue.id
    FROM prefix_enrol AS e ON e.courseid = c.id
    JOIN prefix_user_enrolments AS ue ON ue.enrolid = e.id AND ue.userid = u.id
)

This returns true if the user is enrolled on a course, regardless of the enrolment method

They can also have more than one role, they can be a teacher and student for example

And can also be in more than one group on a course

So for those 2, use a group concat function

If you are using PHP, then there is an existing Moodle function to return the correct compatible SQL

$groupconcatsql = $DB->sql_group_concat('tablename.columnname');

Or if you are using SQL externally, then check your database for the group contact function

e.g. for MySQL

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

Use something like

LEFT JOIN (
    SELECT gm.userid, g.courseid, GROUP_CONCAT(g.name)
    FROM prefix_groups_members AS gm
    JOIN prefix_groups AS g ON g.id = gm.groupid
    GROUP BY gm.userid, g.courseid
) usergroups ON usergroups.userid = u.id AND usergroups.courseid = c.id

Then use SELECT usergroups.name

Note that you also need to join the groups to the course - another reason for lots of duplicates

There are a bunch of example SQL queries on the Moodle website here

https://docs.moodle.org/403/en/ad-hoc_contributed_reports#COURSE_COMPLETION_and_GRADE_REPORTS