I'm a SQL writer at a High School providing support for one of our professors who is asking, "Can we determine which
testbank the question came from and get a category score for each student?" I have discovered some of the table relationships between question and quiz, and I've found mdl_question_bank_entries contains a questioncategoryid field that might get us the info that we need... yet I imagine someone has already put this query together. I would even entertain a plugin if anyone know of one that fits this situation. As I understand it, Ideally, the professor would like to see a column added to the quiz test results report that shows the testbank (name or id ?) and possibly a score breakdown by category. I've included a screenshot of the report in question. Thank you.
The required SQL used to be out there, but I think from before Moodle 4.0, when the database structure chagned a lot.
This database query gets all the data about a quiz attempt https://docs.moodle.org/dev/Overview_of_the_Moodle_question_engine#Detailed_data_about_an_attempt. From that you could follow links question -> question version -> question bank entry -> question category.
Amending the code behing that quiz report is quite hard. I am not sure i would recommend it as someone first experience of Moodle development.
In the future, hopefully, something close to what you want should be available as standard in Moodle, thanks to MDL-74610.
This database query gets all the data about a quiz attempt https://docs.moodle.org/dev/Overview_of_the_Moodle_question_engine#Detailed_data_about_an_attempt. From that you could follow links question -> question version -> question bank entry -> question category.
Amending the code behing that quiz report is quite hard. I am not sure i would recommend it as someone first experience of Moodle development.
In the future, hopefully, something close to what you want should be available as standard in Moodle, thanks to MDL-74610.
Thanks for the resource; I just checked it out and was happy to have my attempts related table joins verified. Here's the script that I wrote to get the quiz bank source with the questions. Let me know if you spot any errors.
SELECT
u.username AS 'User Name'
,qz.name AS 'Quiz Name'
,qza.attempt AS 'Attempt #'
,qa.slot AS 'Q #', q.name AS 'Question Name'
,qza.sumgrades AS 'Grade'
,qc.id AS 'Q. Bank ID', qc.name AS 'Q. Bank Source'
FROM
mdl_quiz qz
INNER JOIN mdl_quiz_attempts qza ON qza.quiz=qz.id
INNER JOIN mdl_user u ON u.id=qza.userid
INNER JOIN mdl_quiz_slots qs ON qs.quizid=qz.id AND qs.slot=qza.attempt
INNER JOIN mdl_quiz_grades qg ON qg.quiz=qz.id AND qg.userid=u.id
INNER JOIN mdl_question_usages qu ON qu.id=qza.uniqueid
LEFT JOIN mdl_question_attempts qa ON qa.questionusageid=qu.id
INNER JOIN mdl_question q ON q.id=qa.questionid
LEFT JOIN mdl_question_versions qv ON qv.questionid=q.id
LEFT JOIN mdl_question_bank_entries qbe ON qbe.id=qv.questionbankentryid
LEFT JOIN mdl_question_categories qc ON qc.id=qbe.questioncategoryid
WHERE qz.id=[QUIZ ID GOES HERE] -- or comment this out if you want all quizzes
ORDER BY q.id, qza.attempt, qa.slot;
Next, I'll pivot the data so that questions appear horizontally, but then again, you can do that in Excel post export too.
SELECT
u.username AS 'User Name'
,qz.name AS 'Quiz Name'
,qza.attempt AS 'Attempt #'
,qa.slot AS 'Q #', q.name AS 'Question Name'
,qza.sumgrades AS 'Grade'
,qc.id AS 'Q. Bank ID', qc.name AS 'Q. Bank Source'
FROM
mdl_quiz qz
INNER JOIN mdl_quiz_attempts qza ON qza.quiz=qz.id
INNER JOIN mdl_user u ON u.id=qza.userid
INNER JOIN mdl_quiz_slots qs ON qs.quizid=qz.id AND qs.slot=qza.attempt
INNER JOIN mdl_quiz_grades qg ON qg.quiz=qz.id AND qg.userid=u.id
INNER JOIN mdl_question_usages qu ON qu.id=qza.uniqueid
LEFT JOIN mdl_question_attempts qa ON qa.questionusageid=qu.id
INNER JOIN mdl_question q ON q.id=qa.questionid
LEFT JOIN mdl_question_versions qv ON qv.questionid=q.id
LEFT JOIN mdl_question_bank_entries qbe ON qbe.id=qv.questionbankentryid
LEFT JOIN mdl_question_categories qc ON qc.id=qbe.questioncategoryid
WHERE qz.id=[QUIZ ID GOES HERE] -- or comment this out if you want all quizzes
ORDER BY q.id, qza.attempt, qa.slot;
Next, I'll pivot the data so that questions appear horizontally, but then again, you can do that in Excel post export too.
There's a kinda weird table relationship between the last two tables in that query. It turns out that mdl_question_bank_entries.id and mdl_question_categories.id are identical sets of unique IDs (implies these records are possibly created at the same time), allowing them each to join with mdl_question_versions.questionbankentryid... or each other! It's a rather unconventional approach to normalization.
Here's the data pivoted with 15 questions and export to CSV (files goes in /var/lib/mysql-files). Since MySQL, even in it's 8th generation, does not have a pivot or cross apply function, I chose to use a series of filtered self-table joins. The other option would be to use a series of sub-queries, but I think that might impact performance a bit.
WITH x AS (
SELECT
q.id AS 'qid'
,u.id AS 'uid', u.username
,qz.name
,qza.attempt
,qa.slot AS 'q', q.name AS 'qname'
,qza.sumgrades
,qc.id AS 'qbid', qc.name AS 'qbsource'
FROM
mdl_quiz qz
INNER JOIN mdl_quiz_attempts qza ON qza.quiz=qz.id
INNER JOIN mdl_user u ON u.id=qza.userid
INNER JOIN mdl_quiz_slots qs ON qs.quizid=qz.id AND qs.slot=qza.attempt
INNER JOIN mdl_quiz_grades qg ON qg.quiz=qz.id AND qg.userid=u.id
INNER JOIN mdl_question_usages qu ON qu.id=qza.uniqueid
LEFT JOIN mdl_question_attempts qa ON qa.questionusageid=qu.id
INNER JOIN mdl_question q ON q.id=qa.questionid
LEFT JOIN mdl_question_versions qv ON qv.questionid=q.id
LEFT JOIN mdl_question_bank_entries qbe ON qbe.id=qv.questionbankentryid
LEFT JOIN mdl_question_categories qc ON qc.id=qbe.questioncategoryid
WHERE qz.id=[QUIZ ID GOES HERE] -- or comment this out if you want all quizzes.
WITH x AS (
SELECT
q.id AS 'qid'
,u.id AS 'uid', u.username
,qz.name
,qza.attempt
,qa.slot AS 'q', q.name AS 'qname'
,qza.sumgrades
,qc.id AS 'qbid', qc.name AS 'qbsource'
FROM
mdl_quiz qz
INNER JOIN mdl_quiz_attempts qza ON qza.quiz=qz.id
INNER JOIN mdl_user u ON u.id=qza.userid
INNER JOIN mdl_quiz_slots qs ON qs.quizid=qz.id AND qs.slot=qza.attempt
INNER JOIN mdl_quiz_grades qg ON qg.quiz=qz.id AND qg.userid=u.id
INNER JOIN mdl_question_usages qu ON qu.id=qza.uniqueid
LEFT JOIN mdl_question_attempts qa ON qa.questionusageid=qu.id
INNER JOIN mdl_question q ON q.id=qa.questionid
LEFT JOIN mdl_question_versions qv ON qv.questionid=q.id
LEFT JOIN mdl_question_bank_entries qbe ON qbe.id=qv.questionbankentryid
LEFT JOIN mdl_question_categories qc ON qc.id=qbe.questioncategoryid
WHERE qz.id=[QUIZ ID GOES HERE] -- or comment this out if you want all quizzes.
-- AND u.email='[USER EMAIL GOES HERE]' -- You can also filter by user email if you want.
)
SELECT 'Quiz ID', 'User ID', 'User Email', 'Quiz Name', 'Attempt', 'Q1', 'Q1 Bank', 'Q2', 'Q2 Bank', 'Q3', 'Q3 Bank', 'Q4', 'Q4 Bank', 'Q5', 'Q5 Bank', 'Q6', 'Q6 Bank', 'Q7', 'Q7 Bank', 'Q8', 'Q8 Bank', 'Q9', 'Q9 Bank', 'Q10', 'Q10 Bank', 'Q11', 'Q11 Bank', 'Q12', 'Q12 Bank', 'Q13', 'Q13 Bank', 'Q14', 'Q14 Bank', 'Q15', 'Q15 Bank'
UNION ALL
SELECT q1.qid, q1.uid, q1.username, q1.name, q1.attempt, q1.sumgrades AS 'Q1', q1.qbsource AS 'Q1 Bank', q2.sumgrades AS 'Q2', q2.qbsource AS 'Q2 Bank', q3.sumgrades AS 'Q3', q3.qbsource AS 'Q3 Bank', q4.sumgrades AS 'Q4', q4.qbsource AS 'Q4 Bank', q5.sumgrades AS 'Q5', q5.qbsource AS 'Q5 Bank', q6.sumgrades AS 'Q6', q6.qbsource AS 'Q6 Bank', q7.sumgrades AS 'Q7', q7.qbsource AS 'Q7 Bank', q8.sumgrades AS 'Q8', q8.qbsource AS 'Q8 Bank', q9.sumgrades AS 'Q9', q9.qbsource AS 'Q9 Bank', q10.sumgrades AS 'Q10', q10.qbsource AS 'Q10 Bank', q11.sumgrades AS 'Q11', q11.qbsource AS 'Q11 Bank', q12.sumgrades AS 'Q12', q12.qbsource AS 'Q12 Bank', q13.sumgrades AS 'Q13', q13.qbsource AS 'Q13 Bank', q14.sumgrades AS 'Q14', q14.qbsource AS 'Q14 Bank', q15.sumgrades AS 'Q15', q15.qbsource AS 'Q15 Bank'
FROM (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=1
) q1
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=2
) q2 ON q2.uid=q1.uid and q2.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=3
) q3 ON q3.uid=q1.uid and q3.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=4
) q4 ON q4.uid=q1.uid and q4.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=5
) q5 ON q5.uid=q1.uid and q5.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=6
) q6 ON q6.uid=q1.uid and q6.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=7
) q7 ON q7.uid=q1.uid and q7.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=8
) q8 ON q8.uid=q1.uid and q8.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=9
) q9 ON q9.uid=q1.uid and q9.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=10
) q10 ON q10.uid=q1.uid and q10.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=11
) q11 ON q11.uid=q1.uid and q11.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=12
) q12 ON q12.uid=q1.uid and q12.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=13
) q13 ON q13.uid=q1.uid and q13.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=14
) q14 ON q14.uid=q1.uid and q14.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=15
) q15 ON q15.uid=q1.uid and q15.attempt=q1.attempt
INTO OUTFILE '/var/lib/mysql-files/demo_quiz.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
)
SELECT 'Quiz ID', 'User ID', 'User Email', 'Quiz Name', 'Attempt', 'Q1', 'Q1 Bank', 'Q2', 'Q2 Bank', 'Q3', 'Q3 Bank', 'Q4', 'Q4 Bank', 'Q5', 'Q5 Bank', 'Q6', 'Q6 Bank', 'Q7', 'Q7 Bank', 'Q8', 'Q8 Bank', 'Q9', 'Q9 Bank', 'Q10', 'Q10 Bank', 'Q11', 'Q11 Bank', 'Q12', 'Q12 Bank', 'Q13', 'Q13 Bank', 'Q14', 'Q14 Bank', 'Q15', 'Q15 Bank'
UNION ALL
SELECT q1.qid, q1.uid, q1.username, q1.name, q1.attempt, q1.sumgrades AS 'Q1', q1.qbsource AS 'Q1 Bank', q2.sumgrades AS 'Q2', q2.qbsource AS 'Q2 Bank', q3.sumgrades AS 'Q3', q3.qbsource AS 'Q3 Bank', q4.sumgrades AS 'Q4', q4.qbsource AS 'Q4 Bank', q5.sumgrades AS 'Q5', q5.qbsource AS 'Q5 Bank', q6.sumgrades AS 'Q6', q6.qbsource AS 'Q6 Bank', q7.sumgrades AS 'Q7', q7.qbsource AS 'Q7 Bank', q8.sumgrades AS 'Q8', q8.qbsource AS 'Q8 Bank', q9.sumgrades AS 'Q9', q9.qbsource AS 'Q9 Bank', q10.sumgrades AS 'Q10', q10.qbsource AS 'Q10 Bank', q11.sumgrades AS 'Q11', q11.qbsource AS 'Q11 Bank', q12.sumgrades AS 'Q12', q12.qbsource AS 'Q12 Bank', q13.sumgrades AS 'Q13', q13.qbsource AS 'Q13 Bank', q14.sumgrades AS 'Q14', q14.qbsource AS 'Q14 Bank', q15.sumgrades AS 'Q15', q15.qbsource AS 'Q15 Bank'
FROM (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=1
) q1
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=2
) q2 ON q2.uid=q1.uid and q2.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=3
) q3 ON q3.uid=q1.uid and q3.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=4
) q4 ON q4.uid=q1.uid and q4.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=5
) q5 ON q5.uid=q1.uid and q5.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=6
) q6 ON q6.uid=q1.uid and q6.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=7
) q7 ON q7.uid=q1.uid and q7.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=8
) q8 ON q8.uid=q1.uid and q8.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=9
) q9 ON q9.uid=q1.uid and q9.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=10
) q10 ON q10.uid=q1.uid and q10.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=11
) q11 ON q11.uid=q1.uid and q11.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=12
) q12 ON q12.uid=q1.uid and q12.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=13
) q13 ON q13.uid=q1.uid and q13.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=14
) q14 ON q14.uid=q1.uid and q14.attempt=q1.attempt
LEFT JOIN (
SELECT x.qid, x.uid, x.username, x.name, x.attempt, x.q, x.qname, x.sumgrades, x.qbid, x.qbsource FROM x WHERE x.q=15
) q15 ON q15.uid=q1.uid and q15.attempt=q1.attempt
INTO OUTFILE '/var/lib/mysql-files/demo_quiz.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Tim Hunt or another Moodle developer, it would be nice to have my table joins and queries validated, please
Yes, but you pasted in the SQL in a way that is pretty unreadable, so what you are asking is unnecesarily difficult.
It looks plausible. Perhaps a better question is: what tests can you do to valdiate the query is generating the right numbers?
It looks plausible. Perhaps a better question is: what tests can you do to valdiate the query is generating the right numbers?
Never mind. The query works for me and they way I posted it looks fine on my PC.
Copy/Paste from OP above:
SELECT
u.username AS 'User Name'
,qz.name AS 'Quiz Name'
,qza.attempt AS 'Attempt #'
,qa.slot AS 'Q #', q.name AS 'Question Name'
,qza.sumgrades AS 'Grade'
,qc.id AS 'Q. Bank ID', qc.name AS 'Q. Bank Source'
FROM
mdl_quiz qz
INNER JOIN mdl_quiz_attempts qza ON qza.quiz=qz.id
INNER JOIN mdl_user u ON u.id=qza.userid
INNER JOIN mdl_quiz_slots qs ON qs.quizid=qz.id AND qs.slot=qza.attempt
INNER JOIN mdl_quiz_grades qg ON qg.quiz=qz.id AND qg.userid=u.id
INNER JOIN mdl_question_usages qu ON qu.id=qza.uniqueid
LEFT JOIN mdl_question_attempts qa ON qa.questionusageid=qu.id
INNER JOIN mdl_question q ON q.id=qa.questionid
LEFT JOIN mdl_question_versions qv ON qv.questionid=q.id
LEFT JOIN mdl_question_bank_entries qbe ON qbe.id=qv.questionbankentryid
LEFT JOIN mdl_question_categories qc ON qc.id=qbe.questioncategoryid
WHERE qz.id=[QUIZ ID GOES HERE] -- or comment this out if you want all quizzes
ORDER BY q.id, qza.attempt, qa.slot;
u.username AS 'User Name'
,qz.name AS 'Quiz Name'
,qza.attempt AS 'Attempt #'
,qa.slot AS 'Q #', q.name AS 'Question Name'
,qza.sumgrades AS 'Grade'
,qc.id AS 'Q. Bank ID', qc.name AS 'Q. Bank Source'
FROM
mdl_quiz qz
INNER JOIN mdl_quiz_attempts qza ON qza.quiz=qz.id
INNER JOIN mdl_user u ON u.id=qza.userid
INNER JOIN mdl_quiz_slots qs ON qs.quizid=qz.id AND qs.slot=qza.attempt
INNER JOIN mdl_quiz_grades qg ON qg.quiz=qz.id AND qg.userid=u.id
INNER JOIN mdl_question_usages qu ON qu.id=qza.uniqueid
LEFT JOIN mdl_question_attempts qa ON qa.questionusageid=qu.id
INNER JOIN mdl_question q ON q.id=qa.questionid
LEFT JOIN mdl_question_versions qv ON qv.questionid=q.id
LEFT JOIN mdl_question_bank_entries qbe ON qbe.id=qv.questionbankentryid
LEFT JOIN mdl_question_categories qc ON qc.id=qbe.questioncategoryid
WHERE qz.id=[QUIZ ID GOES HERE] -- or comment this out if you want all quizzes
ORDER BY q.id, qza.attempt, qa.slot;
I attached a picture in case this formatting above still looks wonky to anyone:
