Hi everybody,
I'm a newcomer with Moodle. So if I posted in wrong box, please let me know.
I'm creating a report which analyzes number of questions in each course by question categories. I tried to find the relationship between the mdl_course table with mdl_context, mdl_question_categories and mdl_question table but I can't understand which relationships exist between these tables? How can a mdl_question_categories is a sub branch of a course?
Thanks for any help.
:D.
The mdl_context table is an important one, so it is worth learning what it means.
What it means depends on the 'contextlevel'. The values are defined here: https://github.com/moodle/moodle/blob/master/lib/accesslib.php#L121
Depending on that, instanceid means different things. If contextlevel = 50, instanceid is a course id. If contextlevel = 70, instanceid is a course_modules id.
I use this kind of snippet quite often.
SELECT
c.shortname AS Course,
quiz.name AS Quiz,
qc.name AS Question_category,
FROM mdl_question_categories qc ON qc.id = q.category
JOIN mdl_context ctx ON ctx.id = qc.contextid
LEFT JOIN mdl_course_modules cm ON cm.id = ctx.instanceid AND ctx.contextlevel = 70
JOIN mdl_course c ON (ctx.contextlevel = 50 AND c.id = ctx.instanceid)
OR (ctx.contextlevel = 70 AND c.id = cm.course)
JOIN mdl_quiz quiz ON quiz.id = cm.instance
ORDER BY c.shortname, quiz.name, qc.name
Thank you very much Tim Hunt :D. I can make my reports easier.
