Moodle自定义SQL报表问题：查询课程学生、组及期末测验成绩

问题根源 #

1. 重复值：
   • 未限定prefix_role_assignments的上下文为当前课程，导致用户在其他场景的student角色记录被关联，产生笛卡尔积。
   • 原代码中prefix_quiz_grades的关联字段错误（用了gi.id而非gi.quiz），导致成绩匹配逻辑混乱。
   • 用户若属于多个组，未处理多组场景的聚合，直接左连接会生成重复行。
2. 组名称异常：
   • 未限定组属于当前课程，可能关联到其他课程的组信息；无组用户的组名未做默认值处理。

修正后的SQL代码 #

SELECT
    u.firstname AS "First Name",
    u.lastname AS "Last Name",
    u.username AS "Username",
    u.email AS "User Email",
    -- 处理多组场景：合并组名，无组则显示默认值
    COALESCE(GROUP_CONCAT(DISTINCT g.name SEPARATOR ', '), 'No Group') 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 AND g.courseid = c.id
-- 提前过滤目标测验，减少关联数据量
INNER JOIN
    prefix_quiz AS q ON c.id = 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
-- 限定角色为当前课程的student
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.contextlevel = 50  -- 课程级上下文标识
        AND ctx.instanceid = c.id  -- 关联当前课程
INNER JOIN
    prefix_role AS r ON ra.roleid = r.id AND r.shortname = 'student'
WHERE
    c.id = %%COURSEID%%
-- 按用户维度聚合，消除重复行
GROUP BY
    u.id, u.firstname, u.lastname, u.username, u.email, gi.grade
ORDER BY
    u.lastname, u.firstname

关键修改说明 #

• 修正成绩关联逻辑：将q.id = gi.id改为q.id = gi.quiz，这是原代码的核心错误——prefix_quiz_grades的quiz字段才对应prefix_quiz的ID，而非主键id。
• 限定角色上下文：通过ctx.contextlevel = 50和ctx.instanceid = c.id，确保仅关联当前课程的student角色记录，避免跨场景的角色数据导致重复。
• 组数据优化：限定组属于当前课程，同时用GROUP_CONCAT合并多组用户的组名，用COALESCE给无组用户设置默认显示值。
• 聚合去重：基于用户ID分组，彻底消除因多角色、多组关联产生的重复行。

内容的提问来源于stack exchange，提问作者L.J. Goico