Module assign : retrieve assessment data for a user

Module assign : retrieve assessment data for a user

by olivier dechepy -
Number of replies: 1

I have created an assignment, with an evaluation grid.

My assign module is set so that there is no student input : nothing is filled, no file is uploaded by the enrolled students. In this case, only the teacher can fill the evaluation for the student (ECOS evaluation).

The data I need are in the gradingform_rubric_fillings table, but the problem is I don't know how to "match" it with the user it is set to, and the assessment (the grade_grades table)

Sofar, i have this query for the criteria selected :

SELECT grc.description as criterion, grl.score, grl.definition, grf.remark, CONCAT (u.lastname," ",u.firstname) AS teacher, grins.status, grins.feedback, grins.itemid, grins.id as grins_id, grl.id as grl_id, grf.id as grf_id, grc.id as grc_id
FROM mdl_gradingform_rubric_levels grl
JOIN mdl_gradingform_rubric_criteria grc ON grc.id = grl.criterionid
JOIN mdl_gradingform_rubric_fillings grf ON (grl.id = grf.levelid AND grl.criterionid = grc.id)
JOIN mdl_grading_instances grins ON grins.id = grf.instanceid
JOIN mdl_user u ON u.id = grins.raterid
ORDER BY grins.itemid, grc.sortorder ASC;

and on the other hand, the grade associated with the user :

SELECT grgr.userid, grgr.rawgrade, grit.courseid, grit.categoryid, grit.itemname, grit.itemtype, grit.itemmodule, grit.iteminstance, grit.itemnumber, grit.id as gritid, grgr.id as grgrid
FROM mdl_grade_grades grgr
JOIN mdl_grade_items grit ON grit.id = grgr.itemid
WHERE grgr.usermodified IS NOT NULL

Thank you

I join an attachment to be more explicit about what i need.

Link between assessment and uuser

Thank you

Average of ratings: -
In reply to olivier dechepy

Re: Module assign : retrieve assessment data for a user

by olivier dechepy -
I'm sure, looking at some demo values, that those values are stored in mdl_gradingform_rubric_fillings

I've gone this far
SELECT DISTINCT grc.description as nom_critere, grl.definition AS valeur_critere_selectionée, grf.remark, CONCAT (u1.lastname," ",u1.firstname) AS Evaluateur, grins.feedback, grins.itemid AS grading_instances_itemid, grins.id as grading_instances_id, grdef.name AS nom_grille, c.fullname AS cours, cm.module, cm.instance, grins.status, grl.score
FROM mdl_gradingform_rubric_levels grl
JOIN mdl_gradingform_rubric_criteria grc ON grc.id = grl.criterionid
JOIN mdl_gradingform_rubric_fillings grf ON (grl.id = grf.levelid AND grl.criterionid = grc.id)
JOIN mdl_grading_instances grins ON grins.id = grf.instanceid
JOIN mdl_grading_definitions grdef ON grdef.id = grc.definitionid AND grdef.id = grins.definitionid
JOIN mdl_grading_areas grar ON grar.id = grdef.areaid
JOIN mdl_context ctx ON ctx.id = grar.contextid
JOIN mdl_course_modules cm ON cm.id = ctx.instanceid
JOIN mdl_course c ON c.id = cm.course
JOIN mdl_user u1 ON u1.id = grins.raterid
WHERE cm.course = <mycourseid>
AND cm.id = <mycoursemoduleid>
AND grins.status = 1   //not sure why...
ORDER BY grins.itemid, grc.sortorder ASC;

but if I go further
SELECT DISTINCT grc.description as nom_critere, grl.definition AS valeur_critere_selectionée, grf.remark, CONCAT (u1.lastname," ",u1.firstname) AS Evaluateur, grins.feedback, grins.itemid AS grading_instances_itemid, grins.id as grading_instances_id, grdef.name AS nom_grille, c.fullname AS cours, cm.module, cm.instance, grins.status, grit.itemname AS nom_devoir, grl.score
FROM mdl_gradingform_rubric_levels grl
JOIN mdl_gradingform_rubric_criteria grc ON grc.id = grl.criterionid
JOIN mdl_gradingform_rubric_fillings grf ON (grl.id = grf.levelid AND grl.criterionid = grc.id)
JOIN mdl_grading_instances grins ON grins.id = grf.instanceid
JOIN mdl_grading_definitions grdef ON grdef.id = grc.definitionid AND grdef.id = grins.definitionid
JOIN mdl_grading_areas grar ON grar.id = grdef.areaid
JOIN mdl_context ctx ON ctx.id = grar.contextid
JOIN mdl_course_modules cm ON cm.id = ctx.instanceid
JOIN mdl_course c ON c.id = cm.course
JOIN mdl_grade_items grit ON grit.iteminstance = cm.instance
JOIN mdl_grade_grades grgr ON grgr.itemid = grit.id
JOIN mdl_user u1 ON u1.id = grins.raterid
WHERE cm.course = <mycourseid>
AND cm.id = <mycoursemoduleid>
AND grins.status = 1
AND grit.itemname IS NOT NULL
AND grit.itemtype = 'mod'
ORDER BY grins.itemid, grc.sortorder ASC;

The results "seem" OK, but if I add the user info
SELECT DISTINCT CONCAT (u2.lastname," ",u2.firstname) AS Etudiant, grgr.rawgrade as note, grc.description as nom_critere, grl.definition AS valeur_critere_selectionée, grf.remark, CONCAT (u1.lastname," ",u1.firstname) AS Evaluateur, grins.feedback, grins.itemid, grins.id as grins_id, grdef.name AS nom_grille, c.fullname AS cours, cm.module, cm.instance, grins.status, grit.itemname AS nom_devoir, grl.score
FROM mdl_gradingform_rubric_levels grl
JOIN mdl_gradingform_rubric_criteria grc ON grc.id = grl.criterionid
JOIN mdl_gradingform_rubric_fillings grf ON (grl.id = grf.levelid AND grl.criterionid = grc.id)
JOIN mdl_grading_instances grins ON grins.id = grf.instanceid
JOIN mdl_user u1 ON u1.id = grins.raterid
JOIN mdl_grading_definitions grdef ON grdef.id = grc.definitionid
JOIN mdl_grading_areas grar ON grar.id = grdef.areaid
JOIN mdl_context ctx ON ctx.id = grar.contextid
JOIN mdl_course_modules cm ON cm.id = ctx.instanceid
JOIN mdl_course c ON c.id = cm.course
JOIN mdl_grade_items grit ON grit.iteminstance = cm.instance
JOIN mdl_grade_grades grgr ON grgr.itemid = grit.id
JOIN mdl_user u2 ON u2.id = grgr.userid
WHERE cm.course = <mycourseid>
AND cm.id = <mycoursemoduleid>
AND grins.status = 1
AND grit.itemname IS NOT NULL
ORDER BY u2.lastname ASC, grins.itemid, grc.sortorder ASC;

Then I see there is whether a bad join OR a constraint is missing...

Any ideas ?
Average of ratings: -