Finally got around to solving this problem - the secret was the dump_course_structure function (from edx-platform <https://github.com/edx/edx-platform/blob/master/lms/djangoapps/courseware/management/commands/dump_course_structure.py> ).
Wrote some parsers and docs for it: https://github.com/SamuelMarks/openedx-modulestore-utils Now finding number of minutes taken per problem per exam is as easy as: SELECT T0.*, T1.exam, T1.display_name, TIMESTAMPDIFF(MINUTE, T0.created, T0.modified) minutes_taken FROM edxapp.courseware_studentmodule T0 JOIN parsed_ms T1 ON T0.module_id = T1.block_id WHERE T1.exam IS NOT NULL AND T0.module_type = 'problem'; :D On Sunday, October 29, 2017 at 2:22:54 AM UTC+11, Samuel Marks wrote: > > Been scratching my head on this problem. > > Looks like some of the information I needed was in MongoDB, the rest in > MySQL. So I went about combining the two: > mongoexport --fields blocks --collection 'modulestore.structures' -d > edxapp --assertExists --query '{"blocks.fields.format": {$in: ["Midterm > Exam", "Final Exam"]}}' | node -e > 'require("readline").createInterface({input: > process.stdin,output: process.stdout,terminal: false}).on("line", l => > console.info(JSON.parse(l).blocks.filter(e => ["Midterm Exam", "Final > Exam"].indexOf(e.fields.format) > -1).map(e => > `${e.block_id},${e.fields.format},${e.fields.display_name}`).join("\n")))' > | { echo 'block_id,exam,display_name'; sort -u; } > /tmp/ > blockid_exam_displayname.csv > > Then in MySQL I simply: > CREATE TEMPORARY TABLE blockid_exam_displayname ( > block_id VARCHAR(33) PRIMARY KEY, > exam VARCHAR(33), > display_name VARCHAR(33), > parent_display_name VARCHAR(33), > children TEXT > ); > > LOAD DATA LOCAL INFILE '/tmp/blockid_exam_displayname.csv' > INTO TABLE blockid_exam_displayname > FIELDS TERMINATED BY ',' > ENCLOSED BY '"' > IGNORE 1 LINES; > > Finally to tie the two tables together: > WITH > Q0 AS ( > SELECT T0.id, T0.module_type, T0.course_id, T0.module_id, > SUBSTRING_INDEX(T0.module_id, '@', -1) block_id, TIMESTAMPDIFF(MINUTE, T0. > created, T0.modified) minutes_taken > FROM edxapp.courseware_studentmodule T0 > WHERE T0.course_id = 'course-v1:UniversityOfSydney+OPHTH101+2017_T2' /* > AND T0.module_type = 'problem' */ > ), > Q1 AS ( > SELECT T1.block_id, T1.exam, T1.children > FROM blockid_exam_displayname T1 > ) > SELECT Q0.*, Q1.block_id q1_block_id, Q1.exam, Q1.children > FROM Q0 > LEFT JOIN Q1 ON ( > LOCATE(Q0.block_id, Q1.children) > ); > > (note that I used a newer MySQL so I could use CTE > <https://dev.mysql.com/doc/refman/8.0/en/with.html>s here) > > What am I doing wrong? - How do I find minutes per question per exam per > student? > -- You received this message because you are subscribed to the Google Groups "General Open edX discussion" group. To view this discussion on the web visit https://groups.google.com/d/msgid/edx-code/0e3ef83c-bc4e-483c-ad51-209d0d91390e%40googlegroups.com.
