I am a little new to Moodle (2.9) and very new to SQL. My institution wants to grade courses with certain benchmarks (like the old BSG add-on). I have begun designing queries which list courses which meet certain criteria. We will be rewarding courses which achieve a high percentage of students interacting, rather than just the amount of modules added to the course.
Here is a query which lists all courses with the following Modules (Module ID in brackets, although that appears to differ from install to install):
Database (4)
Feedback (5)
Forum (7)
Glossary (8)
IMS (9)
Lesson (12)
Quiz (14)
SCORM (16)
Wiki (19)
Workshop (20)
Respondus Quiz (23)
Blog (26)
Questionnaire (32)
Advanced Forum (41)
Assignment (53)
But only if there is any student completion:
SELECT mdl_course.id AS 'Moodle ID', mdl_course.shortname AS 'Course Code', mdl_course.shortname AS 'Course Title'
FROM mdl_course
JOIN mdl_course_modules
ON mdl_course.id = mdl_course_modules.course
JOIN mdl_course_modules_completion
ON mdl_course_modules.id = mdl_course_modules_completion.coursemoduleid
WHERE mdl_course_modules.module=4 OR mdl_course_modules.module=5 OR mdl_course_modules.module=7 OR mdl_course_modules.module=8 OR mdl_course_modules.module=9 OR mdl_course_modules.module=12 OR mdl_course_modules.module=14 OR mdl_course_modules.module=16 OR mdl_course_modules.module=19 OR mdl_course_modules.module=20 OR mdl_course_modules.module=23 OR mdl_course_modules.module=26 OR mdl_course_modules.module=32 OR mdl_course_modules.module=41 OR mdl_course_modules.module=53
AND mdl_course_modules_completion.completionstate = 1
GROUP BY shortname
If you want to try this out, you probably need to do a find-and-replace on all those mdl_ prefixes.
I will probably join the course_categories table, so that they're listed alongside their faculty, but my main aim is to join additional tables to only list courses if 75% or more enrolled students have completed 2 or more of these modules on a course. Any suggestions on how to do that would be appreciated.
