Limit report to Categories

Limit report to Categories

by DeLoy Wilkins -
Number of replies: 5

How do I limit this query to just certain categories?

I am trying to get all grades for all items for all users in "specific" categories

SELECT u.firstname AS 'First' ,
u.lastname AS 'Last',
CONCAT(u.firstname, ' ', u.lastname) AS 'Display Name',
c.fullname AS 'Course',
cc.name AS 'Category',

CASE
  WHEN gi.itemtype = 'course'
   THEN CONCAT(c.fullname, ' Course Total')
  ELSE gi.itemname
END AS 'Item Name',

ROUND(gg.finalgrade,2) AS Grade,
DATE_FORMAT(FROM_UNIXTIME(gg.timemodified),'%Y-%m-%d') AS Time

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories as cc ON cc.id = c.category

WHERE  gi.courseid = c.id
ORDER BY u.lastname

This report shows all users , I just want to show users in certain categories,


any help would be greatly appreciated.


DeLoy Wilkins

Average of ratings: -
In reply to DeLoy Wilkins

Re: Limit report to Categories

by DeLoy Wilkins -

I found an answer and it works using category id. Whoohoo!

SELECT u.firstname AS 'First' ,
u.lastname AS 'Last',
CONCAT(u.firstname, ' ', u.lastname) AS 'Display Name',
c.fullname AS 'Course',
cc.name AS 'Category',

CASE
WHEN gi.itemtype = 'course'
THEN CONCAT(c.fullname, ' Course Total')
ELSE gi.itemname
END AS 'Item Name',

ROUND(gg.finalgrade,2) AS Grade,
DATE_FORMAT(FROM_UNIXTIME(gg.timemodified),'%Y-%m-%d') AS Time

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories as cc ON cc.id = c.category

WHERE  cc.id = '143'
ORDER BY u.lastname


Average of ratings: -
In reply to DeLoy Wilkins

Re: Limit report to Categories

by Randy Thornton -
Picture of Documentation writers
You can do this by category name, cc.name (if they are unique) or you can do this by category id as you did - or if you have category idnumbers by those, both of which are always unique.

Personally I prefer to always manually set category idnumbers for categories just for use in reports. it saves you the trouble of looking up the category id itself and still allows you to change its name without worrying about having to update the reports. It also makes your reports more readable since your future self will probably not remember in 6 months exactly what cc.id = 143 is, but you would know immediately what cc.idnumber = 'worldhistory' means.
Average of ratings:Useful (2)
In reply to Randy Thornton

Re: Limit report to Categories

by DeLoy Wilkins -
What an excellent idea, I wouldn't have thought that, thanks Randy for the tip , will use names for Category ID. Brilliant!
Lucky to have folks like you in Moodle to help us out.
DeLoy Wilkins
Average of ratings: -
In reply to Randy Thornton

Re: Limit report to Categories

by DeLoy Wilkins -
Randy, I guess the grades I showed in the report aren't enough for my Grader role person, they want to see the Percentage of each Quiz and SCORM and the total on the User Report for each student. What tables would I be looking for, been at it awhile but dont seem to be making any headway.

here is the code I am using. I just need to add that percentage from the user report

SELECT u.firstname AS 'First' ,
u.lastname AS 'Last',
CONCAT(u.firstname, ' ', u.lastname) AS 'Display Name',
c.fullname AS 'Course',
cc.name AS 'Category',

CASE
WHEN gi.itemtype = 'course'
THEN CONCAT(c.fullname, ' Course Total')
ELSE gi.itemname
END AS 'Item Name',

ROUND(gg.finalgrade,2) AS Grade,
DATE_FORMAT(FROM_UNIXTIME(gg.timemodified),'%Y-%m-%d') AS Time

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories as cc ON cc.id = c.category

WHERE  cc.id = '143'
ORDER BY u.lastname

Thanks a bunch
DeLoy Wilkins
Average of ratings:Useful (1)