Gradebook

 
 
Picture of Kimber Warden
Re: SQL Retrieving all grades from one user
 

I've never tried to do what you're doing. We don't have a lot of unenrolled users, so this is hard for me to test. I'm sure it's not impossible to retrieve grades for unenrolled users. The question is how do we get them? smile

I used the user_enrolments and enrol tables because there aren't any tables called user_enrolments_history or enrol_history.

Does it help to add the following as the last line in the query? A '1' indicates "unenrolled"

WHERE ue.status='1'

 

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 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.

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',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