Monday, 4 March 2013

Understanding Moodle database 3

Great news!

Getting the course name out (previously all reports were unit names, although in Moodle they are courses, so it gets a bit confusing) is actually very simple.

In mdl_course there is a category value and in mdl_course_categories we can identify the category name from the associated id.

So in my first query I added two new lines and changed the order:

, mdl_context.instanceid as 'CourseID'
, as 'Category'
, mdl_course.fullname as 'CourseName'
, as 'email'
from [moodle].[dbo].[mdl_role_assignments]
join [moodle].[dbo].[mdl_context] on mdl_role_assignments.contextid =
join [Moodle].[dbo].mdl_course on mdl_context.instanceid =
join [Moodle].[dbo].mdl_user on mdl_role_assignments.userid =
join [Moodle].[dbo].mdl_course_categories on mdl_course.category =
where mdl_context.contextlevel = '50' and roleid = '3' or roleid = '4' order by Category asc

C'est ca!