Monday, 4 March 2013

Understanding the Moodle database

I am trying to produce a report that tells us for every course on our Moodle site:

Name of course
Name of unit
Names of teachers on unit
Number of resources/ activities in each unit
Range of resources/ activities in each unit
Number of students enrolled on unit
Number of active students on unit
Number of hits on unit between September 3rd 2012 and June 22nd 2013
Number of hits on each resource/ activity between September 3rd 2012 and June 22nd 2013

Now, the Moodle database structure is not clear at a casual glance so writing up the queries is an interesting challenge and (when I come back to this next year) I will inevitable forget the reasons behind the structure of my queries so, query by query, here's a quick wherefore;


select
userid
, mdl_context.instanceid as 'CourseID'
, mdl_course.fullname as 'CourseName'
, mdl_user.email as 'email'
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 = '3' or roleid = '4' order by CourseName asc

Teacher Names on Course.

mdl_role lists the different roles and assigns each an id. RoleId 3 & 4 are Teacher and non-editing teacher respectively.
mdl_role_assignments contains information about each role assigned to a specific user in each context (system, category, course, forum, etc). It contains the column roleid referred to in this query, context and userid.
mdl_contextlevel contains the different context levels. There is no description here of what the context levels are but it is explained on this page (not got time to figure out how to link to the exact response, but it's there!)
mdl_context gives the id of each item with enrolment (I'm pretty sure of this, but could be wrong), the contextlevel related to it, the instanceid (course ID) related to it and the path and depth (path = /system/category(^nth)/course)

Now for a lunch break and to see how much sense this makes on my return.