Adding Feedback Files to an Assignment Grades report

Adding Feedback Files to an Assignment Grades report

by Paul Vincent -
Number of replies: 4

Hi, I'm hoping someone with better SQL skills than I can help me with this (previoulsy posted this to the dev community - I forgot there's this specific CR forum!)

We're wanting to download the Feedback File a tutor provides for a student for each assignment into our central SIS or at least for now just make it available via Configurable reports, but I'm struggling to create a relationship between the tutor's feedback file and the grade item for the student which the file was uploaded for. It's complicated a little by us having multiple tutors within each course with their own private student groups, so not all feedback in a course belongs to one tutor, although I'm hoping there's a way of matching a feedback file against the tutor who uploaded it/graded the student. 

We have a separate report that lists links to a student's assignment submission (which contains the report file) against the marking tutor, but we need to be bringing the report files into the same report as the grades. 

Many thanks,

Paul


Average of ratings: -
In reply to Paul Vincent

Re: Adding Feedback Files to an Assignment Grades report

by Paul Vincent -
Just to update, this is the query I have to-date:

SELECT
ag.userid,
ag.grader,
u.username AS 'Graded by',
ag.id AS "ag.id",
ag.assignment AS "ag.assignment",
f.itemid,
f.contextid AS 'f.contextid',
ctx.instanceid AS "ctx.instanceid",
concat('<a target="_new" href="%%WWWROOT%%/pluginfile.php/', 
f.contextid, 
'/',f.component,'/',f.filearea,'/',f.itemid,'/',f.filename,
'">',f.filename,'</a>') 
AS "Feedback File",
DATE_FORMAT(FROM_UNIXTIME(f.timecreated),'%Y-%m-%d %H:%i') AS "Created"
FROM prefix_files f
JOIN prefix_user u ON u.id = f.userid
JOIN prefix_context ctx ON f.contextid = ctx.id
JOIN prefix_assign a ON ctx.instanceid = a.id
JOIN prefix_assign_grades ag ON a.id = ag.assignment AND ag.grader = f.userid
WHERE f.component = 'assignfeedback_file' AND 
f.filearea = 'feedback_files' AND 
f.filesize > 0
ORDER BY u.username, f.filename
So although I have the feedback file(s) associated with the grader, I'm yet to work out how to associate the feedback file with the grade or assignment submission. The tables I've checked so far have yielded no common fields between these two halves of the problem.  Hoping somebody can help with this?

Many thanks,
Paul 

Average of ratings: -
In reply to Paul Vincent

Re: Adding Feedback Files to an Assignment Grades report

by Randy Thornton -
Picture of Documentation writers
When the .component in the _files table is of type 'assignfeedback_file' then the .itemid there is the id of the grade in the _assign_grades table. The feedback file and the grade go together.

Or f.itemid = ag.id WHERE f.component = 'assignfeedback_file'.
Average of ratings: -
In reply to Randy Thornton

Re: Adding Feedback Files to an Assignment Grades report

by Paul Vincent -
Many thanks Randy! Looking back I'd already tried that but had another Join interfering with the results, you've saved me a lot of time there!
Average of ratings: -