In the world of e-learning, Moodle is a well-established and widely used platform that empowers educators and learners alike. Its flexibility and open-source nature make it a popular choice for educational institutions. However, to maximize its potential, you may need to customize and optimize your Moodle experience using SQL queries. In this article, we present a collection of SQL queries that can help you enhance your Moodle learning platform.
User Activity Completions with Dates
This analysis presents a comprehensive overview of user activity progress within multiple courses. It is designed to work in conjunction with Configurable Reports filters, allowing you to specify users, start and end times, and search for specific Module names. Additionally, it encompasses the recently introduced core H5P module in version 3.10. Feel free to integrate any third-party activity modules you may have on your site as necessary.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
SELECT u.username As 'User', c.shortname AS 'Course', m.name AS Activitytype, CASE WHEN m.name = 'assign' THEN (SELECT name FROM mdl_assign WHERE id = cm.instance) WHEN m.name = 'assignment' THEN (SELECT name FROM mdl_assignment WHERE id = cm.instance) WHEN m.name = 'book' THEN (SELECT name FROM mdl_book WHERE id = cm.instance) WHEN m.name = 'chat' THEN (SELECT name FROM mdl_chat WHERE id = cm.instance) WHEN m.name = 'choice' THEN (SELECT name FROM mdl_choice WHERE id = cm.instance) WHEN m.name = 'data' THEN (SELECT name FROM mdl_data WHERE id = cm.instance) WHEN m.name = 'feedback' THEN (SELECT name FROM mdl_feedback WHERE id = cm.instance) WHEN m.name = 'folder' THEN (SELECT name FROM mdl_folder WHERE id = cm.instance) WHEN m.name = 'forum' THEN (SELECT name FROM mdl_forum WHERE id = cm.instance) WHEN m.name = 'glossary' THEN (SELECT name FROM mdl_glossary WHERE id = cm.instance) WHEN m.name = 'h5pactivity' THEN (SELECT name FROM mdl_h5pactivity WHERE id = cm.instance) WHEN m.name = 'imscp' THEN (SELECT name FROM mdl_imscp WHERE id = cm.instance) WHEN m.name = 'label' THEN (SELECT name FROM mdl_label WHERE id = cm.instance) WHEN m.name = 'lesson' THEN (SELECT name FROM mdl_lesson WHERE id = cm.instance) WHEN m.name = 'lti' THEN (SELECT name FROM mdl_lti WHERE id = cm.instance) WHEN m.name = 'page' THEN (SELECT name FROM mdl_page WHERE id = cm.instance) WHEN m.name = 'quiz' THEN (SELECT name FROM mdl_quiz WHERE id = cm.instance) WHEN m.name = 'resource' THEN (SELECT name FROM mdl_resource WHERE id = cm.instance) WHEN m.name = 'scorm' THEN (SELECT name FROM mdl_scorm WHERE id = cm.instance) WHEN m.name = 'survey' THEN (SELECT name FROM mdl_survey WHERE id = cm.instance) WHEN m.name = 'url' THEN (SELECT name FROM mdl_url WHERE id = cm.instance) WHEN m.name = 'wiki' THEN (SELECT name FROM mdl_wiki WHERE id = cm.instance) WHEN m.name = 'workshop' THEN (SELECT name FROM mdl_workshop WHERE id = cm.instance) ELSE "Other activity" END AS Activityname, # cm.section AS Coursesection, CASE WHEN cm.completion = 0 THEN '0 None' WHEN cm.completion = 1 THEN '1 Self' WHEN cm.completion = 2 THEN '2 Auto' END AS Activtycompletiontype, CASE WHEN cmc.completionstate = 0 THEN 'In Progress' WHEN cmc.completionstate = 1 THEN 'Completed' WHEN cmc.completionstate = 2 THEN 'Completed with Pass' WHEN cmc.completionstate = 3 THEN 'Completed with Fail' ELSE 'Unknown' END AS 'Progress', DATE_FORMAT(FROM_UNIXTIME(cmc.timemodified), '%Y-%m-%d %H:%i') AS 'When' FROM mdl_course_modules_completion cmc JOIN mdl_user u ON cmc.userid = u.id JOIN mdl_course_modules cm ON cmc.coursemoduleid = cm.id JOIN mdl_course c ON cm.course = c.id JOIN mdl_modules m ON cm.module = m.id # skip the predefined admin and guest user WHERE u.id > 2 AND u.id = XXX |
List of All Courses Enrollment by User
The provided SQL query retrieves information related to a specific user’s course enrollment. It lists the username, first name, last name, and identification number of the user, along with the ID and short name of the courses they are enrolled in. The query is designed to fetch this data for a user with the ID XXX. This information can be valuable for tracking individual user enrollments in various courses on the Moodle platform.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT uu.username, uu.firstname, uu.lastname, uu.idnumber, c.id, c.shortname FROM mdl_user_enrolments ue JOIN mdl_enrol en ON ue.enrolid = en.id JOIN mdl_user uu ON uu.id = ue.userid JOIN mdl_course c ON en.courseid = c.id WHERE uu.id = XXX |
