Notes on making an ad-hoc query for Moodle

Expanding on the problem and resolution at
https://github.com/hn-88/ad-hoc-moodle-database-queries/issues/1

This seems to be a classic illustration of what can go wrong when we copy code written by someone else without understanding it. Apparently Github's Copilot also has such issues.

I had blindly copied the code from https://stackoverflow.com/questions/15938185/selecting-all-the-files-along-with-their-paths-of-a-course-in-moodle without verifying if the resource join is correct. 

select count(*) as "All ppt(x) and mp3/mp4 files",

count(distinct f.contenthash) as "Only unique ppt(x) and mp3/mp4 files"

from {files} f

INNER JOIN {context} c ON f.contextid = c.id

INNER JOIN {resource} r ON c.instanceid = r.id

INNER JOIN {course} co ON r.course = co.id

where (f.filename like '%ppt%' or f.filename like '%mp%')

and co.fullname like :coursename

Apparently, in our case, where the files are inside folders, this is absolutely wrong. The contextid depends on what is mentioned in contextlevel. If contextlevel is 70 (= modules), contextid points to the entry in course_modules table. Not resource table!

So, the corrected query would be

select count(*) as "All ppt(x) and mp3/mp4 files",

count(distinct f.contenthash) as "Only unique ppt(x) and mp3/mp4 files"

from {files} f

left join {context} cont on f.contextid = cont.id

left join {course_modules} vcm on cont.instanceid = vcm.id

where

vcm.course=co.id

and vcm.module=8

and cont.contextlevel = 70

and f.component ='mod_folder'

and (f.filename like '%.ppt%' or f.filename like '%.mp%')

and co.fullname like :coursename

Unfortunately, the documentation for this was not very easily available. For example, where do we see that contextlevel=70 corresponds to modules? Only in this deprecated page, https://docs.moodle.org/dev/Roles_and_modules - archived link.

So, my notes for arriving at the above conclusion:

Symptom - when relying on contextid to determine course, a lower number of files is reported in the queries

https://moodle.org/mod/forum/discuss.php?d=214037
did not seem to be correct, since it was returning all sorts of files.

So, decided to try and understand the meaning of contextid first!

via https://moodle.org/mod/forum/discuss.php?d=200625
"In files tables, component col is the where (course, backup, mod_folder)" - so, to find list of files, we can search files table with
contextid =962 and component ='mod_folder'

To get the relevant contextid, I had used the path in LMS, which was https://server.tld/pluginfile.php/962/mod_folder/content/0/Teaching%20Resources/DD_Living%20on%20the%20Streets.pptx?forcedownload=1
Todo: get it from the database

In context table, id=962 has instanceid=667, which is what is seen in the html,
for the div
  | id="module-667"
  | data-for="cmitem"
  | data-id="667"

 Finally! Explanation of context table - https://moodle.org/mod/forum/discuss.php?d=202588 

 From https://docs.moodle.org/400/en/course_display
"contextlevel 70 is modules, then instanceid points to the mdl_course_modules table" so in the relevant context table entry, the instanceid points to course_modules table....

Checking the entry in course_modules table, id=667, course=34 (correct), module=8 (=folder, from modules table). With this info, I could put together the "correct" query above.