help in file table in moodle

help in file table in moodle

by m question -
Number of replies: 4


I want to understand file table in moodle to write sql to find number of each file type uploaded in moodle

I didn't find this in the Resource table

in file there is context and many numbers

I read context 50 is course, so the instanceid would be course id

but this is not the case in this table

how to know which file uploaded in which course


thanks

Average of ratings: -
In reply to m question

Re: help in file table in moodle

by Miguel González Laredo -
Picture of Plugin developers

I understand what you're looking for is the column "MIMETYPE" on that table: _files

Some values on It such as: application/pdf, image/png, application/zip, application/msword, audio/mp3, ...

This query would be your starting point, I think:

select count(id), mimetype  from p_files where mimetype is not null
group by mimetype

Good luck!

Average of ratings: Useful (1)
In reply to Miguel González Laredo

Re: help in file table in moodle

by m question -
Thank you so much
I need to know type for each course
but I could not connect files to courses
I hope you can help
Average of ratings: -
In reply to m question

Re: help in file table in moodle

by m question -
until now I got this
but the results still not correct

select r.course,count(*)

from prefix_files f
JOIN prefix_context cx ON f.contextid=cx.id
JOIN prefix_resource r ON cx.instanceid=r.id
where mimetype="application/vnd.ms-powerpoint.presentation.macroEnabled.12"
OR mimetype="application/vnd.openxmlformats-officedocument.presentationml.presentation"
OR mimetype="application/vnd.openxmlformats-officedocument.presentationml.slideshow"

GROUP BY r.course
Average of ratings: -
In reply to m question

Re: help in file table in moodle

by m question -
how to know which file is deleted and also which files uploaded by teacher from mdl_files table
or can I have this data from other tables ? what are these tables?
Average of ratings: -