Moodle SCORM SQL

Question

Какую таблицу и поле мне нужно использовать в SQL, чтобы найти действия SCORM, которые сообщают о результатах в Moodle и исключают объекты SCORM, для которых задано отсутствие оценки. В основном ищем поле настроек SCORM: Требовать минимальный балл в SQL

Запрос:

    SELECT
    CONCAT(u.firstname,' ',u.lastname ) AS 'fullname', 
    cc.name AS 'Category',
    c.fullname AS 'Course', 
    gi.itemname AS 'Item Name',
    ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS 'Percentage',
    asgm.status as 'Status',
    p.status AS 'Completed',
    gi.itemmodule as 'Type',
    s.completionscorerequired AS require_minimum_score
    FROM prefix_course AS c
    JOIN prefix_course_categories AS cc ON cc.id = c.category
    JOIN prefix_context AS ctx ON c.id = ctx.instanceid
    JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
    JOIN prefix_user AS u ON u.id = ra.userid
    JOIN prefix_grade_items AS gi ON gi.courseid = c.id
    JOIN prefix_scorm AS s ON c.id = s.course
    LEFT JOIN prefix_grade_grades AS gg ON gi.id = gg.itemid AND gg.userid = u.id
    LEFT JOIN prefix_course_completions AS p ON p.userid = u.id AND p.course = c.id
    LEFT JOIN (
      SELECT asgm.status as status, c.id as cid, u.id as uid,  gi.id as giid
      FROM prefix_course AS c
           JOIN prefix_course_categories AS cc ON cc.id = c.category
           JOIN prefix_context AS ctx ON c.id = ctx.instanceid
           JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
           JOIN prefix_user AS u ON u.id = ra.userid
           JOIN prefix_grade_items AS gi ON gi.courseid = c.id
           JOIN prefix_assign AS asg ON gi.iteminstance = asg.id and asg.course = c.id
           JOIN prefix_assign_submission AS asgm ON asg.id = asgm.assignment and asgm.userid = u.id 
      ) AS asgm ON c.id = asgm.cid and u.id = asgm.uid  and gi.id = asgm.giid

    WHERE (gi.itemmodule = 'quiz' OR gi.itemmodule= 'assign' OR gi.itemmodule= 'scorm') AND c.visible=1

Answer 1

SELECT s.id AS scormid, s.name AS scormname,
       s.completionscorerequired AS require_minimum_score,
       c.id AS courseid, c.fullname AS coursename
FROM mdl_scorm s
JOIN mdl_course c ON c.id = s.course