Ad hoc Reports - Courseid vs Course Name

Ad hoc Reports - Courseid vs Course Name

by J. M. Kihoro -
Number of replies: 1
I picked SQL Code below from the Adhoc Reports and  I have used to get specific information about a particular student but it wont give me the correct course name. Instead, it gives the grades associated with the displayed courseID who name I need to capture. I am not an expert in SQL. Note that I have used the Firstname field for Student Registration Numbers. Kindly help. Thank you.


Code is Here

SELECT

user2.firstname AS Firstname,

user2.lastname AS Lastname,

course.fullname AS Course,

  prefix_grade_items.courseid, 

  prefix_grade_items.itemmodule

, prefix_grade_items.itemname 

, ROUND(prefix_grade_items.grademax,0) as grademax

, ROUND(prefix_grade_grades.finalgrade, 0) AS finalgrade

, from_unixtime(prefix_grade_grades.timecreated) AS timecreated


FROM prefix_course AS course

left outer JOIN prefix_enrol AS en ON en.courseid = course.id

left outer JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id

left outer JOIN prefix_user AS user2 ON ue.userid = user2.id

left outer join prefix_grade_grades  on user2.id = prefix_grade_grades.userid

left outer join   prefix_grade_items   on prefix_grade_grades.itemid = prefix_grade_items.id 


where user2.firstname ='BCC01/0150/2018' AND itemmodule='quiz' AND itemname ='ORDINARY EXAM'


ORDER BY Course

Average of ratings: -
In reply to J. M. Kihoro

Re: Ad hoc Reports - Courseid vs Course Name

by Mark Sharp -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers
Hi, those are odd looking firstnames you've got going on there 'BCC01/0150/2018'.

The courseid is the id field in the course table. You don't need to display it, but it's useful to distinguish it from another course that might have the same name. Which seems to be the case with your example.

The query, as you have it there, will only pull grade items for quizzes that have the title "ORDINARY EXAM", so will not return any grades from any other activity or quizzes with different names.

Also the query will show grades for particular items rather than a summary of all grades - that would be an entirely different query - and not a straight-forward one. Is Moodle's own grade report pages not sufficient for your purposes?

If you want to show the grades for a particular course, then use the course id in the where. e.g. 

where user2.firstname ='BCC01/0150/2018' AND itemmodule='quiz' AND itemname ='ORDINARY EXAM' AND course.id=3022


                                    

                
                
                
                                        
                
                
                                            

                                                    
Average of ratings: Useful (1)