SQL Retrieving all grades from one user

Picture of Julien Thomas
Re: SQL Retrieving all grades from one user

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.



Picture of Chad Bergeron
Re: SQL Retrieving all grades from one user

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
LEFT JOIN mdl_grade_items gi ON
JOIN mdl_course c ON
WHERE'8569' and gh.finalgrade IS NOT NULL and gi.itemname IS NOT NULL


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.

Picture of Julien Thomas
Re: SQL Retrieving all grades from one user

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',, AS 'categoryname', gh.timemodified
FROM mdl_grade_grades_history gh
JOIN mdl_grade_items gih ON
JOIN mdl_course c ON gih.courseid =
JOIN mdl_course_categories cc ON c.category =
WHERE gh.userid = '866'
and gih.itemtype = 'category'
and gih.itemname = 'Note finale du cours'
order by gih.courseid ,gh.timemodified desc