Monday, 4 March 2013

Understanding the Moodle database 2

Following on from my previous: here I am counting the number of students enrolled in each course.

Nice and simple this one :)


select
COUNT (userid) as 'Students'
, mdl_context.instanceid as 'CourseID'
, mdl_course.fullname as 'CourseName'
from [moodle].[dbo].[mdl_role_assignments]
join [moodle].[dbo].[mdl_context] on mdl_role_assignments.contextid = mdl_context.id
join [Moodle].[dbo].mdl_course on mdl_context.instanceid = mdl_course.id
join [Moodle].[dbo].mdl_user on mdl_role_assignments.userid = mdl_user.id
where mdl_context.contextlevel = '50' and roleid = '5'
group by mdl_context.instanceid, mdl_course.fullname
order by CourseName asc

No new tables here, just a slightly different way of extracting the data.

We're counting the number of students in each course, which requires the other columns to be grouped (count is an aggregate function, you have to describe how you want the other columns to be arranged). Note - you have to use the actual column name in the Group by, you can't get away with the renamed value. I learned that the hard way :)

roleid for students is 5 - although forum recommendation is that you should use the short name rather than the id value as this is a customisable setting.

And now, I'm going to see if I can combine this query with the previous one and get one big table of results :)

EDIT: BIG PROBLEMS IN "SIMPLE" QUERY TOWN

So, a little further inspection of the returned results demonstrated that my query only returns those users enrolled directly onto the courses. The users who are enrolled via a meta link are not counted. I haven't tracked down the correct table for the meta linking yet and have actually opened a Moodle forum query about it because I am *stumped*.

I have a looming suspicion that cohort enrolment may also be wrongly counted, but I'm having trouble finding a Cohorts table with up to date information. There should be two cohorts for each ALevel we deliver and myriad others for the short courses/ complimentary courses available. Unfortunately, there are only 5 cohorts  in the helpfully named mdl_cohorts table, so I don't think that's correct.

*sigh* going crosseyed. Ideally I'd break at this point and think about something else for ten minutes, but all of my typical resources have a tenuous link to what I'm doing here and I can't stop myself consciously reflecting on it.