[Urgent] Mistake in submission report query

[Urgent] Mistake in submission report query

by Sarath P -
Number of replies: 8

Hello everyone. I am trying to get a Formative submissions report that shows student id, firstname, lastname, submission name and the feedback for the submission from the tutor.  All the submissions are created under a specific category. I'm not getting the correct results.

Moodle version: 3.4.2+

The following is the query I have written.

SELECT u.`username` AS 'Student ID',u.`firstname` AS 'Firstname',u.`lastname`AS 'Lastname',

asn.`name` AS 'Assignment',

c.`fullname`,

mcc.`name` AS 'category',

afc.`commenttext`AS 'Teacher Feedback' 

FROM mdl_assign AS asn

JOIN mdl_course AS c ON c.`id`=asn.`course`

JOIN mdl_assign_grades AS ag ON ag.`assignment`= asn.`id`

JOIN mdl_assign_submission AS mas ON mas.`assignment`=asn.`id`

JOIN mdl_user AS u ON u.`id`=mas.`userid`

JOIN mdl_assignfeedback_comments AS afc ON afc.`assignment`=ag.`assignment`

JOIN mdl_course_categories AS mcc ON mcc.`id`= c.`category`

WHERE mcc.`name`='Category name' AND mcc.`id`='category id' 

GROUP BY u.`username`

Can anyone please tell me how to get the correct results.

Thanks in advance


Average of ratings: -
In reply to Sarath P

Re: [Urgent] Mistake in submission report query

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
AND mcc.`id`='category id'

what's that supposed to do? It also strikes me (it always melts my brain) that with ordinary JOINs they will ALL have to be satisfied to get a result. You have quite a disparate list of tables and joins. 

Just in case you can get something out of it... I wrote this recently (only for courses not categories)... https://moodle.org/plugins/report_assign
Average of ratings: -
In reply to Howard Miller

Re: [Urgent] Mistake in submission report query

by Sarath P -
Hi Howard. Thank you for your response and the plugin. But my Moodle version is 3.4.2+. Actually, there are different categories created in Moodle and under all those categories different Formative submissions are also created.For example under June-Aug category there are 5 courses in which each course contain a formative submission link. So I need only the feedback from the submission links which is under June-Aug category. For that , mcc.`id`='category id' is used. Please tell me how to get the correct results using the above query?
Average of ratings: -
In reply to Sarath P

Re: [Urgent] Mistake in submission report query

by Ray Hinton -
What defines the value of 'category id'? Or is that pseudocode, and you are replacing it with a number before you run the query?

Are you getting a specific error when you run your query now, or are you getting output, that is just different from what you want?

Also, I think that you could potentially simplify your WHERE clause. The ID number should be enough to uniquely identify a category (unless I am missing something glaring).
Average of ratings: -
In reply to Ray Hinton

Re: [Urgent] Mistake in submission report query

by Sarath P -
Hi Ray. Its different from what I want. Yes the ID number is enough to uniquely identify.
I just reordered the query.
Query:
SELECT
a.`name` AS 'Submission name', c.fullname AS 'Course name'
FROM mdl_course AS c
JOIN mdl_course_categories AS cc ON cc.`id`= c.`category`
JOIN mdl_assign AS a ON c.`id`=a.`course`
WHERE cc.`id`=19
Till here I'm getting the output correctly. Lets say if I'm getting 12 different course names, after adding the next line, the rows will be incremented to 71 and the course name will be reduced to 3.

JOIN mdl_assign_grades AS g ON a.`id`=g.assignment

The table names in my query are the correct, right? The mdl_assignfeedback_comments table has the teachers feedback. But I'm not able to get the output like, for example:
Student Id firstname lastname submission_name Feedback
101 Kumar R Formative submission Well done Kumar.....
102 Ramu M Formative Submission Could have been improved more

Where should I modify to get the output like the above?
Average of ratings: -
In reply to Sarath P

Re: [Urgent] Mistake in submission report query

by Ray Hinton -
OK, I think this is where we get to the logic of your query.

In your new example, try changing the "JOIN mdl_assign_grades AS g ON a.`id`=g.assignment" to a LEFT JOIN. With your first set of JOINs, you build up a nice table that should have values in all of the fields (they are all tables that are filled out automatically when you create a course or assignment). Then, when you get to mdl_assign_grades, those are values that might not exist for some students (if they do not have a grade).

I cannot explain exactly how the values get filled out with just JOIN, but it obviously does not make sense.

https://www.w3schools.com/sql/sql_join.asp

From your original query, you may need to change these into LEFT JOIN:
JOIN mdl_assign_grades
JOIN mdl_assign_submission
JOIN mdl_assignfeedback_comments

I am not a SQL expert by any means, but I have written many queries that I find useful by tediously looking through the Moodle database table/field names, reading the W3schools.com SQL tutorials over and over again, and patiently trying many variations. smile
Average of ratings: -
In reply to Ray Hinton

Re: [Urgent] Mistake in submission report query

by Sarath P -
Hi Ray. I have updated my query.

SELECT DISTINCT u.`username` AS 'Student ID',u.firstname AS "First Name", u.lastname AS "Last Name",
c.fullname AS "Course",
a.name AS "Assignment" ,mac.`commenttext` AS 'Tutor Feedback'

FROM mdl_assign_submission AS asb
JOIN mdl_assign AS a ON a.id = asb.assignment
JOIN mdl_user AS u ON u.id = asb.userid
JOIN mdl_course AS c ON c.id = a.course
JOIN mdl_course_modules AS cm ON c.id = cm.course
JOIN mdl_assign_grades AS ag ON ag.`userid`=u.`id`
JOIN mdl_assignfeedback_comments AS mac ON mac.`grade`= ag.`id`
WHERE c.`category`=19
ORDER BY c.fullname, a.name, u.lastname

The query is kind of working, but still have issues. if we sorted the list by student name, the feedback comments are the same for all the courses, which is wrong.
For example:
Student Id firstname lastname submission_name Course name Feedback
101 Kumar R Formative submission Course A Well done Kumar...
101 Kumar R Formative submission Course B Well done Kumar...
101 Kumar R Formative submission Course C Well done Kumar...
101 Ramu M Formative submission Course A Excellent work.

Let me try using LEFT JOIN. Thank you smile .
I hope the mapping between the tables are right. The last JOIN mdl_assignfeedback_comments, is that right? What do you think?
Average of ratings: -
In reply to Sarath P

Re: [Urgent] Mistake in submission report query

by Ray Hinton -
Hm, now that I look at your new version, you might just need a more specific ON condition for one of the JOINs. How about changing:

JOIN mdl_assign_grades AS ag ON ag.`userid`=u.`id`
to
JOIN mdl_assign_grades AS ag ON (ag.`userid`=u.`id` AND a.id = ag.assignment)

Otherwise, I think the mapping looks OK. I also notice that you changed GROUP to ORDER, which popped out to me in your original post. I think that will behave more predictably. My head is spinning from all the JOINs, too, but I think those two ideas - different JOINs, and/or more specific conditions - will get you in the right direction.
Average of ratings: -
In reply to Ray Hinton

Re: [Urgent] Mistake in submission report query

by Sarath P -
Hi Ray,
Apologies for the late reply. Like you suggested, I changed
JOIN mdl_assign_grades AS ag ON ag.`userid`=u.`id`
to
JOIN mdl_assign_grades AS ag ON (ag.`userid`=u.`id` AND a.id = ag.assignment)

As a result the feedback repetition is removed so as some users too.
For example after I changed the JOIN, user Kumar is removed. I changed GROUP to ORDER because the users will be attending assessment in more than one course. So If I GROUP them, rest of the courses will be ignored, right?

Let me try with more specific conditions smile
Thank you smile
Average of ratings: -