SQL Get Files for Assignments

SQL Get Files for Assignments

by Jez H -
Number of replies: 2

Hi All,

I am trying to run a report which lists all assignments and the file submissions associated with them including file size.

I am struggling to do this and wondered if I am running through the right tables?

SELECT a.name, s.assignment, s.submission, f.itemid, f.filesize,
FROM mdl_assign a
LEFT JOIN mdl_assignsubmission_file s ON a.id = s.assignment
LEFT JOIN mdl_files f ON s.assignment = f.itemid

One issue is I get multiple submissions for the same contenthash which I worked around, but then seemed to retrieve far too few assignments which made me wonder if these are the tables I should be using?

Any pointers would be greatly appreciated!

Average of ratings: -
In reply to Jez H

Re: SQL Get Files for Assignments

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Well, you are going to find a lot of extra files that way - the itemid is only one of the fields you need to match, you also need to match the 'component' field (in this case it will be 'mod_assign'), the 'filearea' field (I can't tell you off the top of my head, but I'd suggest you check the list of existing files in the database) and possibly the contextid as well (although you can probably skip that, as the itemid, in this case, will be enough, without the contextid as well.

You will also want to skip any files with the filename '.', as those represent the directory/folder within which the files are stored (every file area has a base directory/folder, it may also have further subdirectories/subfolders within it).


Average of ratings: Useful (1)
In reply to Davo Smith

Re: SQL Get Files for Assignments

by Jez H -

Thanks, I had not pasted the where clause which was filtering results on component.

Another issue was resubmissions with the same contenthash, when i grouped those I ended up with far fewer files than I expected and wondered if I was missing something.

I think from what you say the tables are correct, I just need to improve the query?

Average of ratings: -