Hello,
I want to to know all the teachers who added a file in a course from a specific date, can we know that?
if this requires an sql request, what are the tables I should call
Thanks for your help
Mimi,
This is a great resource for understanding the database schema in Moodle: https://www.examulator.com/er/output/index.html
- The table is files.
- The context ID is how you'd identify the course. A quick way of finding out the relevant context id is to go to the relevant course > Participants > click on the little settings cog > Permissions. And you'll see the context ID in the URL.
- The User ID tells you who uploaded it.
- The timecreated or timemodified fields tell you when it was added/updated.
You can join the user table to get the firstname, lastname, email, whatever else.
So your query will be something like
SELECT files.filename, user.firstname, user.lastname
FROM files
JOIN user
ON user.id=files.userid
WHERE files.contextid=XXX and timecreated > YYY
Hello,
Thank you very much for your reply, i really learned a lot, i just have a little question about the contextid
my goal is to display all the teachers who added a file in the home page of their course throughout the site from a specific date. According to this page, the course context is defined by the contextlevel 50
but in your request you have used the contextid field and not the contextlevel
the contextid in the files table points to the id field of the context table.
how to represent the home page context of a course for all the courses on the site?
I am studying these tables.
I thank you in advance for you help
Hello,
Here is the SQL request to show files added bye teachers from a specific date (Thank you very much C Behan), but il shows all files even those added in a forum, I know It's a question of context, I will work on it.
SELECT DISTINCT mdl_files.filename, mdl_files.timecreated, mdl_user.firstname, mdl_user.lastname, mdl_user.username
#I used distinct because it shows duplicate files
FROM mdl_files
JOIN mdl_user
ON mdl_user.id=mdl_files.userid
WHERE mdl_files.timecreated > 1598054400
AND
# To not take files with name just a dot
mdl_files.filename <> '.'
AND
mdl_user.username IN (SELECT username FROM table_x);