SQL Retrieving all grades from one user

SQL Retrieving all grades from one user

di Julien Thomas -
Numero di risposte: 6

Hi,

I'm working with Moodle database and I want to get all grades from an unenrolled user.

The purpose is to print all final grades in the user profile.

I tryed to get the data from mdl_grade_grades_history and mdl_grade_items_history databases,

but the final result is strange and incoherent, cause I have no grade history for a user id=832 who was enrolled in the course id=102

 

Here's an example of the sql :

 

SELECT distinct gg.rawgrade, gg.rawgrademax, gg.finalgrade, gi.itemname
FROM moodle_cifad.mdl_grade_grades_history gg
INNER JOIN moodle_cifad.mdl_grade_items_history gi ON gg.itemid = gi.id
where gg.userid = 832 and gi.courseid = 102

 

Has anyone an idea, am i on the right path ?

In riposta a Julien Thomas

Re: SQL Retrieving all grades from one user

di Kimber Warden -

Assuming you're using 2.0+, try this:

SELECT u.firstname AS 'First Name', u.lastname AS 'Last Name', c.fullname as 'Course',
(SELECT gh.finalgrade
FROM mdl_grade_grades_history gh
JOIN mdl_grade_items_history gih ON gih.id=gh.itemid
WHERE gih.itemtype='course'
AND u.id=gh.userid
AND gih.courseid=c.id
GROUP BY c.id) AS 'Final Grade'

FROM mdl_user u
JOIN mdl_user_enrolments ue ON ue.userid=u.id
JOIN mdl_enrol e ON e.id=ue.enrolid
JOIN mdl_course c ON c.id=e.courseid
WHERE u.id='832' #might be easier to search for u.username instead
AND c.id='102' #might be easier to search for c.fullname instead
/*if you leave out the last line, your search would include grades for all of the student's courses.*/


In riposta a Kimber Warden

Re: SQL Retrieving all grades from one user

di Julien Thomas -

I am using Moodle 2.4.

Thank you for your help sorridente.

 

I just tryed it out and I get all enrolled user in a course and noting in the FinalGrade column.

You still use enrol and user_enrollments database.

Does it mean it is not possible to get historical grades of a student if he is unenrolled in a course ?

 

For exemple, one student, who's not enrolled anymore in a course.

I would like to get his  final result called "Note finale du cours" in the "Sommatif" category.

 

In Moodle 1.9 It was easy cause there was not any history tables :

SELECT ROUND(mdl_grade_grades.finalgrade) AS final_grade, mdl_grade_grades.finalgrade, mdl_course.fullname, itemname FROM mdl_grade_gradesJOIN mdl_user ON mdl_grade_grades.userid = mdl_user.id JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.id JOIN mdl_course ON mdl_grade_items.courseid = mdl_course.idWHERE userid = '$user->id' and grademax = 100 and (itemtype = 'mod' OR itemtype = 'category') and itemname = 'Note finale du cours'"

 

 

 

 

 

 

In riposta a Julien Thomas

Re: SQL Retrieving all grades from one user

di Kimber Warden -

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? sorridente

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'

 

In riposta a Kimber Warden

Re: SQL Retrieving all grades from one user

di Julien Thomas -

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 riposta a Julien Thomas

Re: SQL Retrieving all grades from one user

di 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 riposta a Chad Bergeron

Re: SQL Retrieving all grades from one user

di 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