Search code examples
sqlmoodlescorm

SQL query for Moodle SCORM Question Report in Databasetable scorm_scoes_track


Example: 1 user has completed 2 test attempts. The test questions are taken randomly and therefore different questions are stored in the columns element and value.

table scorm_scoes_track

How can I count the number of results in column "value" for all occurring questions in same column for the value "SlideX_QuestionY"?

Desired result would be: result

   SELECT
    st.* 
    FROM prefix_scorm_scoes_track    AS st
       JOIN prefix_user              AS u   ON st.userid  = u.id
       JOIN prefix_scorm             AS sc  ON sc.id      = st.scormid
       JOIN prefix_course            AS c   ON c.id       = sc.course
    WHERE   sc.course = 684 and st.scormid = 1195  and st.userid = 9780

Solution

  • Something like this

    This includes a join on itself because of the way the scorm values are stored. The join includes a substring which will be slow.

    There can also be multiple attempts, so this checks for the last attempt

    SELECT c.id AS courseid, c.fullname AS coursename,
        s.id AS scormid, s.name AS scormname,
        slide.element,
        slide.value AS slidename,
        SUM(CASE WHEN results.value = 'wrong' THEN 1 ELSE 0 END) AS wrong,
        SUM(CASE WHEN results.value = 'correct' THEN 1 ELSE 0 END) AS correct,
        /* results can have other values so this is just in case */
        SUM(CASE WHEN results.value NOT IN ('correct', 'wrong') THEN 1 ELSE 0 END) AS other
    FROM mdl_scorm_scoes_track slide
    JOIN mdl_scorm s ON s.id = slide.scormid
    JOIN mdl_course c ON c.id = s.course
    JOIN mdl_scorm_scoes_track results ON results.userid = slide.userid
        AND results.scormid = slide.scormid
        AND results.scoid = slide.scoid
        AND results.attempt = slide.attempt
        /* A join on a substring will be slow */
        AND results.element = LEFT(slide.element, LENGTH(slide.element)-3) || '.result'
    WHERE slide.element LIKE 'cmi.interactions%.id'
    AND slide.value LIKE 'Slide%_Question%'
    AND EXISTS (
        /* There can be multiple attempts so use the latest attempt only */
        SELECT 1
        FROM mdl_scorm_scoes_track lastattempt
        WHERE lastattempt.userid = slide.userid
        AND lastattempt.scormid = slide.scormid
        HAVING MAX(lastattempt.attempt) = slide.attempt
    )
    GROUP BY c.id, c.fullname,
       s.id, s.name,
       slide.element,
       slide.value
    ORDER BY c.id, c.fullname,
       s.id, s.name,
       slide.element,
       slide.value