updated 2019-11-06
To see where-how the modules for each “Topic” are stored and kept in order.
To see the record-id (instance) for the particular lesson, questionnaire, feedback request, or etc.
The instance is the mdl_{MODULE}.id
for a long time,
if the module id is 11, it is the lesson module;
if the module id is 19, it is the feedback module;
etc.
1st query … to see a list of all the courses
with a count of the students [contextlevel=50] enrolled in them:
SELECT cat.parent AS p, c.category AS cat,
SUBSTRING(cat.name, 1, 25) AS category,
cat.sortorder AS catsort, cat.visible AS V,
c.id AS cid, c.visible AS v, c.sortorder AS csort,
c.fullname AS coursename, count(ra.userid) AS STUcount
FROM mdl_course AS c
JOIN mdl_course_categories AS cat
ON cat.id = c.category
JOIN mdl_context AS con
ON con.instanceid = c.id
AND con.contextlevel = 50
LEFT JOIN mdl_role_assignments AS ra
ON ra.contextid = con.id
GROUP BY c.id
ORDER BY cat.parent, c.sortorder
To see the list of topics in a course including
all the modules called by each topic:
2 examples
example 1:
SELECT cs.id, cs.course, cs.section, cs.sequence,
cm.id AS cm_id, cm.module, cm.instance
FROM mdl_course_sections AS cs
JOIN mdl_course_modules AS cm
ON FIND_IN_SET(cm.id, cs.sequence)
WHERE cs.course = 40
AND cs.sequence <> ''
AND cs.visible = 1
AND cm.visible = 1
ORDER BY cs.course, cs.section, FIND_IN_SET(cm.id, cs.sequence) ;
look at your list of modules in the mdl_modules table
here is an example of a few core module id’s from a few years ago:
many of them differ depending on what version of moodle you were on
when you started and what upgrades you have done since.
id name
==-====
11 lesson
12 quiz
19 feedback
23 page
29 questionnaire
this example shows a course (#40) with up to 4 lesson (11) modules per topic
+ a few questionnaires (29) and one feedback (19) request.
+------+--------+---------+---------------------+-------+--------+----------+
| id | course | section | sequence | cm_id | module | instance |
+------+--------+---------+---------------------+-------+--------+----------+
| 8667 | 40 | 0 | 8473 | 8473 | 11 | 7387 |
| 8668 | 40 | 1 | 8474 | 8474 | 11 | 7388 |
| 8669 | 40 | 2 | 8475,8476 | 8475 | 11 | 7389 |
| 8669 | 40 | 2 | 8475,8476 | 8476 | 29 | 3 |
| 8671 | 40 | 3 | 8477,8478,8479 | 8477 | 11 | 7390 |
| 8671 | 40 | 3 | 8477,8478,8479 | 8478 | 29 | 4 |
| 8671 | 40 | 3 | 8477,8478,8479 | 8479 | 29 | 5 |
| 8672 | 40 | 4 | 8480 | 8480 | 11 | 7391 |
| 8673 | 40 | 5 | 8481,8487 | 8481 | 11 | 7392 |
| 8673 | 40 | 5 | 8481,8487 | 8487 | 29 | 6 |
| 8674 | 40 | 6 | 8482,8493,8494,8495 | 8482 | 11 | 7393 |
| 8674 | 40 | 6 | 8482,8493,8494,8495 | 8493 | 11 | 7400 |
| 8674 | 40 | 6 | 8482,8493,8494,8495 | 8494 | 11 | 7401 |
| 8674 | 40 | 6 | 8482,8493,8494,8495 | 8495 | 11 | 7402 |
| 8675 | 40 | 7 | 8488 | 8488 | 11 | 7395 |
| 8676 | 40 | 8 | 8483 | 8483 | 11 | 7394 |
| 8677 | 40 | 9 | 8490,8491,8492 | 8490 | 11 | 7397 |
| 8677 | 40 | 9 | 8490,8491,8492 | 8491 | 11 | 7398 |
| 8677 | 40 | 9 | 8490,8491,8492 | 8492 | 11 | 7399 |
| 8838 | 40 | 10 | 8523,8484 | 8523 | 11 | 7429 |
| 8838 | 40 | 10 | 8523,8484 | 8484 | 19 | 43 |
+------+--------+---------+---------------------+-------+--------+----------+
example 2:
Another select query showing topics where many lesson time limits were set
note: in moodle 3, the name of the time limit field was changed from “maxtime” to
“completiontimespent” and from minutes to seconds, so we divide by 60 to show it in
minutes.
it also shows the first 20 chars of the instance title, be it a lesson or a questionnaire or …
You can set the query to include whatever modules you use.
SELECT cs.course, cs.section, cs.sequence,
cm.id AS cm_id, cm.module, cm.instance,
l.id AS lessonid, ROUND((l.completiontimespent /60), 0) AS lsntime,
CASE WHEN cm.module = 11 THEN substring( l.name, 1, 20)
WHEN cm.module = 19 THEN substring( fb.name, 1, 20)
WHEN cm.module = 27 THEN substring(cer.name, 1, 20)
WHEN cm.module = 29 THEN substring(qst.name, 1, 20)
END AS activityName
FROM mdl_course_sections AS cs
JOIN mdl_course_modules AS cm
ON FIND_IN_SET(cm.id, cs.sequence)
LEFT JOIN mdl_lesson AS l
ON ( cm.instance = l.id AND l.course = cm.course AND cm.module = 11 )
LEFT JOIN mdl_feedback AS fb
ON ( cm.instance = fb.id AND cm.module = 19 )
LEFT JOIN mdl_certificate AS cer
ON ( cm.instance = cer.id AND cm.module = 27 )
LEFT JOIN mdl_questionnaire AS qst
ON ( cm.instance = qst.id AND cm.module = 29 )
WHERE cs.course IN (20, 233)
AND cs.sequence <> ''
AND cs.visible = 1
AND cm.visible = 1
AND cm.module IN (11, 19, 27, 29)
ORDER BY cs.course, cs.section, FIND_IN_SET(cm.id, cs.sequence) ;
+--------+---------+--------------+-------+--------+----------+----------+---------+----------------------+
| course | section | sequence | cm_id | module | instance | lessonid | lsntime | activityName |
+--------+---------+--------------+-------+--------+----------+----------+---------+----------------------+
| 20 | 0 | 704 | 704 | 11 | 628 | 628 | 20 | BEGIN: Site Policy a |
| 20 | 1 | 313 | 313 | 11 | 258 | 258 | 0 | Lesson 1: Working To |
| 20 | 2 | 314,998 | 998 | 11 | 884 | 884 | 0 | Lesson 2: The Legal |
| 20 | 3 | 315 | 315 | 11 | 260 | 260 | 0 | Lesson 3: How Our Th |
| 20 | 4 | 316 | 316 | 11 | 261 | 261 | 20 | Lesson 4: Learning S |
| 20 | 5 | 317 | 317 | 11 | 262 | 262 | 20 | Lesson 5: Practicing |
| 20 | 6 | 318 | 318 | 11 | 263 | 263 | 20 | Lesson 6: Practicing |
| 20 | 7 | 319 | 319 | 11 | 264 | 264 | 20 | Lesson 7: Problems & |
| 20 | 8 | 320 | 320 | 11 | 265 | 265 | 0 | Lesson 8: Pathways t |
| 20 | 9 | 321 | 321 | 11 | 266 | 266 | 0 | Lesson 9: The Proces |
| 20 | 10 | 322 | 322 | 11 | 267 | 267 | 20 | Lesson 10: Building |
| 20 | 11 | 323 | 323 | 11 | 268 | 268 | 20 | Lesson 11: Preventio |
| 20 | 12 | 324 | 324 | 11 | 269 | 269 | 20 | Lesson 12: Preventio |
| 20 | 13 | 325,326,327 | 325 | 11 | 270 | 270 | 20 | Lesson 13.1: Healthy |
| 20 | 13 | 325,326,327 | 326 | 11 | 271 | 271 | 20 | Lesson 13.2: Plannin |
| 20 | 13 | 325,326,327 | 327 | 11 | 272 | 272 | 20 | Lesson 13.3: Planni |
| 20 | 14 | 328,329 | 328 | 11 | 273 | 273 | 20 | Lesson 14.1: Does Yo |
| 20 | 14 | 328,329 | 329 | 11 | 274 | 274 | 20 | Lesson 14.2: Job Sat |
| 20 | 15 | 330,331 | 330 | 11 | 275 | 275 | 20 | Lesson 15.1: Learnin |
| 20 | 15 | 330,331 | 331 | 11 | 276 | 276 | 20 | Lesson 15.2: School |
| 20 | 16 | 332,333 | 332 | 11 | 277 | 277 | 20 | Lesson 16.1: Learnin |
| 20 | 16 | 332,333 | 333 | 11 | 278 | 278 | 20 | Lesson 16.2 : Exerc |
| 20 | 17 | 334,335 | 334 | 11 | 279 | 279 | 20 | Lesson 17.1: Prevent |
| 20 | 17 | 334,335 | 335 | 11 | 280 | 280 | 20 | Lesson 17.2: Prevent |
| 20 | 18 | 336,337,338 | 336 | 11 | 281 | 281 | 20 | Lesson 18.1: Skills |
| 20 | 18 | 336,337,338 | 337 | 11 | 282 | 282 | 20 | Lesson 18.2: Plannin |
| 20 | 18 | 336,337,338 | 338 | 11 | 283 | 283 | 20 | Lesson 18.3: Plannin |
| 20 | 19 | 339,340 | 339 | 11 | 284 | 284 | 20 | Lesson 19.1: Getting |
| 20 | 19 | 339,340 | 340 | 11 | 285 | 285 | 20 | Lesson 19.2: Exercis |
| 20 | 20 | 341,342,343 | 341 | 11 | 286 | 286 | 20 | Lesson 20.1: Giving |
| 20 | 20 | 341,342,343 | 342 | 11 | 287 | 287 | 20 | Lesson 20.2: Your St |
| 20 | 20 | 341,342,343 | 343 | 11 | 288 | 288 | 20 | Lesson 20.3: Your Ne |
| 20 | 21 | 344,345 | 344 | 11 | 289 | 289 | 20 | Lesson 21.1: Sharing |
| 20 | 21 | 344,345 | 345 | 11 | 290 | 290 | 20 | Lesson 21.2: Your Fu |
| 20 | 22 | 346 | 346 | 19 | 21 | NULL | NULL | Online Course Evalua |
| 233 | 0 | 8473 | 8473 | 11 | 7387 | 7387 | 10 | ! BEGIN: Site Policy |
| 233 | 1 | 8474 | 8474 | 11 | 7388 | 7388 | 45 | Lesson 1: Class Goal |
| 233 | 2 | 8475 | 8475 | 11 | 7389 | 7389 | 10 | Lesson 2: Self-Asses |
| 233 | 3 | 8476 | 8476 | 29 | 3 | NULL | NULL | Lesson 3: Exercises |
| 233 | 4 | 8477 | 8477 | 11 | 7390 | 7390 | 45 | Lesson 4: Readiness |
| 233 | 5 | 8478 | 8478 | 29 | 4 | NULL | NULL | Lesson 5: Your Reaso |
| 233 | 6 | 8479 | 8479 | 29 | 5 | NULL | NULL | Lesson 6: Confidence |
| 233 | 7 | 8480 | 8480 | 11 | 7391 | 7391 | 20 | Lesson 7: How to Cha |
| 233 | 8 | 8481 | 8481 | 11 | 7392 | 7392 | 20 | Lesson 8: Coping wit |
| 233 | 9 | 8487 | 8487 | 29 | 6 | NULL | NULL | Lesson 9: Examples o |
| 233 | 10 | 8482 | 8482 | 11 | 7393 | 7393 | 45 | Lesson 10: Interpers |
| 233 | 11 | 8493 | 8493 | 11 | 7400 | 7400 | 30 | Lesson 11: Social Sk |
| 233 | 12 | 8494 | 8494 | 11 | 7401 | 7401 | 30 | Lesson 12: Social Sk |
| 233 | 13 | 8495 | 8495 | 11 | 7402 | 7402 | 30 | Lesson 13: Social Sk |
| 233 | 14 | 8488 | 8488 | 11 | 7395 | 7395 | 20 | Lesson 14: Problem S |
| 233 | 15 | 8483 | 8483 | 11 | 7394 | 7394 | 30 | Lesson 15: Relapse P |
| 233 | 16 | 8490 | 8490 | 11 | 7397 | 7397 | 45 | Lesson 16: Healthy P |
| 233 | 17 | 8491 | 8491 | 11 | 7398 | 7398 | 30 | Lesson 17: Your Work |
| 233 | 18 | 8492 | 8492 | 11 | 7399 | 7399 | 30 | Lesson 18: Relaxatio |
| 233 | 19 | 8523 | 8523 | 11 | 7429 | 7429 | 0 | Complete this Class |
| 233 | 20 | 8484 | 8484 | 27 | 43 | NULL | NULL | Certificate of Compl |
+--------+---------+--------------+-------+--------+----------+----------+---------+----------------------+