An SQL query to display the course gradebook

An SQL query to display the course gradebook

by Nadav Kavalerchik -
Number of replies: 5
Picture of Core developers Picture of Plugin developers Picture of Testers Picture of Translators

I am asked to display a table of the Gradebook categories and modules with all the aggregation information (like: weights, factor, ... ) on some external SIS app that is connected with our Moodle. So I am aiming for a single SQL query that will help me display all the information in one go. (by using a course id)

I researched the code that displays the Gradebook page (https://github.com/moodle/moodle/blob/master/grade/edit/tree/lib.php) and read the Moodle developer wiki (https://docs.moodle.org/dev/Grades) and also tried to find some clues on the ad-hock sql queries wiki page (https://docs.moodle.org/29/en/ad-hoc_contributed_reports) and (of course) the Moodle DB schema (https://docs.moodle.org/dev/Database_Schema#Moodle_2.9) but could not find a definite documentation that could lead for the logics needed to form a single SQL query that displays the Gradebook .

It seems, the combination of mdl_grade_grades and mdl_grade_items are holding all the Gradebook aggregation info, but also the student's actual grade item values. Is that true?

Does creating a single SQL query that displays the Gradebook is at all possible? (or do I need some php for that?) 

In reply to Nadav Kavalerchik

Re: An SQL query to display the course gradebook

by Melvyn Gomez -
Nadav,
try this query


SELECT
c.id as Course_Id,
c.shortname AS shortname,
c.fullname AS fullname,
u.id AS User_Id,
u.firstname AS firstname, 
u.lastname AS lastname, 
u.email AS email, 
COALESCE(ROUND(gg.finalgrade,2),0) as finalgrade
FROM mdl_user u
INNER JOIN mdl_role_assignments ra ON ra.userid = u.id
INNER JOIN mdl_context ct ON ct.id = ra.contextid
INNER JOIN mdl_course c ON c.id = ct.instanceid
INNER JOIN mdl_role r ON r.id = ra.roleid
LEFT JOIN
(
SELECT
u.id AS userid,c.id as courseid,
g.finalgrade AS finalgrade
FROM mdl_user u
JOIN mdl_grade_grades g ON g.userid = u.id
JOIN mdl_grade_items gi ON g.itemid =  gi.id
JOIN mdl_course c ON c.id = gi.courseid where gi.itemtype = 'course'
) gg ON gg.userid = u.id and gg.courseid = c.id
In reply to Melvyn Gomez

Re: An SQL query to display the course gradebook

by Nadav Kavalerchik -
Picture of Core developers Picture of Plugin developers Picture of Testers Picture of Translators

Thank you Melvyn, but I was referring for just the gradebook calculations without the actual grades, as the teacher see it under the gradebook setup page.

like the following:


In reply to Nadav Kavalerchik

Re: An SQL query to display the course gradebook

by Nadav Kavalerchik -
Picture of Core developers Picture of Plugin developers Picture of Testers Picture of Translators

Found what I was looking for, after some more research and playing around with phpMyAdmin & Moodle gradebook...

SELECT gc.courseid, gc.parent, gc.depth, gc.path, gc.fullname "Category"
,gi.itemname, gi.itemtype, gi.itemmodule, gi.iteminstance,gi.grademax, gi.multfactor, gi.plusfactor
FROM mdl_grade_categories gc
JOIN mdl_grade_items gi ON gi.categoryid = gc.id 
WHERE gc.courseid = XXX

Enjoy smile

In reply to Nadav Kavalerchik

Re: An SQL query to display the course gradebook

by Nadav Kavalerchik -
Picture of Core developers Picture of Plugin developers Picture of Testers Picture of Translators

Updated version:

SELECT 
gc.courseid, gc.parent, gc.depth, gc.path, gc.fullname "Category",
CASE gc.aggregation
  WHEN 0 THEN 'Mean OF Grades'
  WHEN 2 THEN 'Median OF Grades'
  WHEN 6 THEN 'Highest Grade'
  WHEN 8 THEN 'Mode OF Grades'
  WHEN 10 THEN 'Weighted Mean OF Grades'
  WHEN 11 THEN 'Simple Weighted Mean OF Grades'
  WHEN 12 THEN 'Mean OF Grades (WITH extra credits)'
  WHEN 13 THEN 'SUM OF Grades'
END AS 'aggregation name'
,gi.itemname, gi.itemtype, gi.itemmodule, gi.iteminstance,floor(gi.aggregationcoef2*100) "weight", floor(gi.grademin), floor(gi.grademax), gi.multfactor, gi.plusfactor
FROM mdl_grade_categories gc
JOIN mdl_grade_items gi ON gi.categoryid = gc.id 
WHERE gc.courseid = XXX
In reply to Melvyn Gomez

This forum post has been removed

The content of this forum post has been removed and can no longer be accessed.