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:

select
userid
, mdl_context.instanceid as 'CourseID'
, mdl_course_categories.name as 'Category'
, 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
join [Moodle].[dbo].mdl_course_categories on mdl_course.category = mdl_course_categories.id
where mdl_context.contextlevel = '50' and roleid = '3' or roleid = '4' order by Category asc

C'est ca!