These are what I use. I can't vouch for 100% accuracy, because I wrote them
This gets the number of files on the course with ID number XXXX:
select mdl_context.instanceid course, count(*) ttl from mdl_files inner join mdl_context on mdl_files.contextid = mdl_context.id group by mdl_context.instanceid having mdl_context.instanceid=XXXX
This gets the total size of the files on the course with ID number $id:
select mdl_context.instanceid course, sum(filesize) ttl from mdl_files inner join mdl_context on mdl_files.contextid = mdl_context.id group by mdl_context.instanceid having mdl_context.instanceid=XXXX
Take out the 'having...' part for a report of all courses. It might take a while to run, though!
I've just checked, and the figures reported are wrong. They only show the total number/size of files added to the course: they don't include the files that are duplicates of files already on another course. I took a copy of a course, added 6 files, and my report says the course has 6 files in it.
I've got something, but it can probably be tidied up.
I had to get two queries and union them together. This works for listing files, but makes COUNT() and SUM() more difficult.
select filename file, instanceid course, filesize
from mdl_files join mdl_context on mdl_files.contextid = mdl_context.id
where instanceid = XXXX and contextlevel=50 union all
select filename file, course, filesize
from mdl_files inner join mdl_context on mdl_files.contextid = mdl_context.id
join mdl_resource on instanceid = mdl_resource.id
where course=XXXX and contextlevel = 70
The other way you can do it is this.
Suppose the course context as path /1/23/456
FROM mdl_files f
JOIN mdl_context ctx ON f.contextid = ctx.id
WHERE CONCAT(ctx.path, '/') LIKE '/1/23/456/%'
I had used this query to find all files by idcourse
but it didn't work.
I don't find the relation between instanceid and idcourse?
I would like to write a SQL to get the list of files and folders in the course (using course id) in alphabetical order. I tried with few examples, but it was not successful, can you please help me with the same?