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/cbeae143-46d8-446d-a863-a0538dfc3b7b%40googlegroups.com.
