SQL Retrieving all grades from one user

Re: SQL Retrieving all grades from one user

by Julien Thomas -
Number of replies: 2

I'm sure it's not impossible too, cause it exists somewhere in the moodle core !

Actually I am looking for quite the same query used to retrieve grades while enrolling/re-enrolling users.

I was doing some research in moodle libs in the enrol and gradebook folders, but I've found noting yet.

 

The data is there in the mdl_grade_grades_history and mdl_grade_items_history, I just don't understand how it works for now.

How the grade data is moved from one table to another while unenrollment, and where is the link between enrol and user_enrolments with unenrolled users.

 

The status for unenrolled user don't help really but could be an interesting filter.

 

 

In reply to Julien Thomas

Re: SQL Retrieving all grades from one user

by Chad Bergeron -

I've been building something similar, to look at the grade history, in the rare cases where a student somehow gets unenrolled and reenrolled into a course and I need to find the grade history.  Here's what I have:

 

SELECT c.fullname AS 'Course', u.firstname AS 'Firstname', u.lastname AS 'Lastname', gh.finalgrade AS 'Recorded Grade', gi.itemname AS 'Item Name'
FROM mdl_user u
JOIN mdl_grade_grades_history gh ON u.id=gh.userid
LEFT JOIN mdl_grade_items gi ON gh.itemid=gi.id
JOIN mdl_course c ON gi.courseid=c.id
WHERE u.id='8569' and gh.finalgrade IS NOT NULL and gi.itemname IS NOT NULL
ORDER BY c.id

 

It does not use the enrollment tables, so should work even if the student has been unenrolled.  It grabs the final grade history for any items -currently- in the gradebook, so if an activity or item is also removed from the gradebook it won't appear in this report.

In reply to Chad Bergeron

Re: SQL Retrieving all grades from one user

by Julien Thomas -

Thank you Chad !

I managed to build a quite similar request with the same idea, I forgot to mention it.

It work with the way i want to use it.

Here it is :

SELECT gh.finalgrade as 'finalgrade', gih.courseid as 'courseid', c.fullname as 'coursename',cc.id, cc.name AS 'categoryname', gh.timemodified
FROM mdl_grade_grades_history gh
JOIN mdl_grade_items gih ON gih.id=gh.itemid
JOIN mdl_course c ON gih.courseid = c.id
JOIN mdl_course_categories cc ON c.category = cc.id
WHERE gh.userid = '866'
and gih.itemtype = 'category'
and gih.itemname = 'Note finale du cours'
order by gih.courseid ,gh.timemodified desc