Need help!
I have no SQL programming experience, and am trying to set up a customizable report. I need to return only those records where the grade > 7.9. I also need to add two custom user fields: County and State. I'm attaching a file with my code.
Hi Deb,
For the grade, add the line "AND gg.finalgrade>7.9" to the end of the JOIN of the grade.
To add the custom user fields, you need to find out what fieldid moodle assigned the custom field to. This is held in the table mdl_user_info_field.
Once you have that, you can use the following code:
JOIN prefix_user_info_data AS aa
ON u.ID = aa.userid AND aa.fieldid=9
In this example, aa.data will contain the information for custom field 9, which in our case is the Position Description.
To pull a second field, use
JOIN prefix_user_info_data AS ab
ON u.ID = ab.userid AND ab.fieldid=5
And again, ab.data will contain the information.
So your code (once we know the correct fieldid's is:
SELECT u.firstname AS 'First' , u.lastname AS 'Last', aa.data AS 'Country', ab.data AS 'State',
CASE
WHEN gi.itemtype = 'course'
THEN CONCAT(c.fullname, ' - Total')
ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Grade,
FROM_UNIXTIME(gg.timemodified) 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_user_info_data AS aa
ON u.ID = aa.userid AND aa.fieldid=9
JOIN prefix_user_info_data AS ab
ON u.ID = ab.userid AND ab.fieldid=9
JOIN prefix_grade_grades AS gg ON gg.userid = u.id AND gg.finalgrade>7.9
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 lastname
Cheers
Peter
You rock!!! Thank you so much!!!
Hi Peter,
I too have very little experience with SQL and was hoping you (or someone else on this thread) might be able to assist?
I have a similar need to Deb's. I am trying to create a customisable report that simply replicates the Course Completion report (found in the Course Completion block), but with the addition of some custom user profile fields. So the columns would be:
Any help would be greatly appreciated!
Thanks,
Andrew
Hey Andrew,
Try this:
SELECT u.firstname AS 'First' , u.lastname AS 'Last', aa.data AS 'State', ab.data AS 'County', ac.data AS 'Gender', ad.data AS 'Ethnicity', ae.data AS 'Race', af.data AS 'Residence', ag.data AS 'Grade in School',
CASE
WHEN gi.itemtype = 'course'
THEN CONCAT(c.fullname, ' - Total')
ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Grade,
FROM_UNIXTIME(gg.timemodified) 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_user_info_data AS aa
ON u.ID = aa.userid AND aa.fieldid=1
JOIN prefix_user_info_data AS ab
ON u.ID = ab.userid AND ab.fieldid=2
JOIN prefix_user_info_data AS ac
ON u.ID = ac.userid AND ac.fieldid=3
JOIN prefix_user_info_data AS ad
ON u.ID = ad.userid AND ad.fieldid=4
JOIN prefix_user_info_data AS ae
ON u.ID = ae.userid AND ae.fieldid=5
JOIN prefix_user_info_data AS af
ON u.ID = af.userid AND af.fieldid=6
JOIN prefix_user_info_data AS ag
ON u.ID = ag.userid AND ag.fieldid=7
JOIN prefix_grade_grades AS gg ON gg.userid = u.id AND gg.finalgrade>10 and gg.finalgrade<100
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 Last
Thanks Deb,
Seems to be pulling the right kind of info
I even managed to change the column titles and add the idnumber column.
A couple of issues though...
1) The report is returning course completions for all courses across the site. How can I restrict this so the report only returns course completions for one course? (the course which the configurable report block is added)
2) The report is returning 896 records which is not the full number records I was expecting,
3) I'm getting a lot of duplicated records in the report, by up to 4 times.
Any ideas?
Cheers,
Andrew
Here's my current code:
SELECT u.idnumber AS 'ID' , u.firstname AS 'First' , u.lastname AS 'Last', aa.data AS 'Employment Type', ab.data AS 'Department', ac.data AS 'Dept', ad.data AS 'Sub Section', ae.data AS 'Section', af.data AS 'Directorate', ag.data AS 'Area',
CASE
WHEN gi.itemtype = 'course'
THEN CONCAT(c.fullname, ' - Total')
ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Grade,
FROM_UNIXTIME(gg.timemodified) 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_user_info_data AS aa
ON u.ID = aa.userid AND aa.fieldid=1
JOIN prefix_user_info_data AS ab
ON u.ID = ab.userid AND ab.fieldid=2
JOIN prefix_user_info_data AS ac
ON u.ID = ac.userid AND ac.fieldid=3
JOIN prefix_user_info_data AS ad
ON u.ID = ad.userid AND ad.fieldid=4
JOIN prefix_user_info_data AS ae
ON u.ID = ae.userid AND ae.fieldid=5
JOIN prefix_user_info_data AS af
ON u.ID = af.userid AND af.fieldid=6
JOIN prefix_user_info_data AS ag
ON u.ID = ag.userid AND ag.fieldid=7
JOIN prefix_grade_grades AS gg ON gg.userid = u.id AND gg.finalgrade>10 and gg.finalgrade<100
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 Last
Hey Peter!
So your code is working fabulously, but now I have way too many records. How can I filter for a specific course, specified start and end time, filter for a state, and also include more than 5000 records? It sounds like the last request may be the hardest, so maybe I'll just focus on the first two!
SELECT u.firstname AS 'First' , u.lastname AS 'Last', ab.data AS 'State', aa.data AS 'County',
CASE
WHEN gi.itemtype = 'course'
THEN CONCAT(c.fullname, ' - Total')
ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Grade,
FROM_UNIXTIME(gg.timemodified) 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_user_info_data AS aa
ON u.ID = aa.userid AND aa.fieldid=2
JOIN prefix_user_info_data AS ab
ON u.ID = ab.userid AND ab.fieldid=1
JOIN prefix_grade_grades AS gg ON gg.userid = u.id AND gg.finalgrade>7.9 AND gg.finalgrade<99
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 lastname
