如何在Moodle 4.0中获取课程进度并完善现有SQL查询？

核心思路 #

Moodle中SCORM活动的进度数据存储在mdl_scorm_scoes_track表中，通过关联该表与mdl_scorm主表，就能获取用户对每个SCORM活动的完成进度。关键关联逻辑是：利用mdl_grade_items的iteminstance字段关联到SCORM活动ID，再通过用户ID、SCORMID关联到进度追踪表。

修改后的SQL语句 #

SELECT 
    u.id AS user_id,
    u.firstname,
    u.lastname,
    c.id AS course_id,
    c.fullname AS coursename,
    act.id AS activity_id,
    act.itemname AS activity,
    grades.finalgrade AS activity_grade,
    -- 将进度值（0-1的小数）转为百分比格式，无数据时显示0%
    CONCAT(ROUND(COALESCE(track.value, 0) * 100), '%') AS progress
FROM mdl_course AS c
/* 关联课程与用户 */
INNER JOIN mdl_context AS ctx ON c.id = ctx.instanceid AND ctx.contextlevel = 50 -- 限定课程级上下文
INNER JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
INNER JOIN mdl_user AS u ON u.id = ra.userid
/* 关联SCORM活动 */
INNER JOIN mdl_grade_items act ON act.courseid = c.id AND act.itemmodule = 'scorm'
INNER JOIN mdl_scorm s ON s.id = act.iteminstance -- 关联SCORM主表
/* 关联成绩 */
INNER JOIN mdl_grade_grades grades ON grades.itemid = act.id AND grades.userid = u.id
/* 关联SCORM进度追踪表 */
LEFT JOIN mdl_scorm_scoes_track track 
    ON track.userid = u.id 
    AND track.scormid = s.id 
    AND track.element = 'cmi.core.progress_measure' -- 对应进度存储字段
WHERE c.id IN (21, 22, 23, 24, 25, 26)
ORDER BY act.courseid, u.id

关键说明 #

• 表关联逻辑：通过mdl_grade_items.iteminstance拿到SCORM活动的ID，关联到mdl_scorm后，再匹配用户ID获取对应的进度记录。
• 进度值处理：cmi.core.progress_measure字段存储的是0到1的小数，乘以100后转为百分比；用COALESCE处理用户未产生进度数据的情况，默认显示0%。
• 左连接使用：用LEFT JOIN避免因用户无进度数据而丢失整条活动记录。

输出示例 #

内容的提问来源于stack exchange，提问作者developer_jr