Wednesday, 6 March 2013

(Failing at) Understanding the Moodle database 4

This is deeply frustrating. I'm building up a sketch of how I think this is constructed and I can't find the missing piece. So:

On Moodle, we have courses. There is a table (mdl_course) which contains the basic construction details  for each course and assigns an id value.

On Moodle we have users. There is a table (mdl_users) which contains the basic details about each user and assigns an id value.

There are various contexts on Moodle in which people can be enrolled. These may be courses, categories, etc. Context Levels don't appear to be defined in the database but can be tracked down in the docs, which may or may not be obsolete (?)

However, for the purposes of this: Users are enrolled on Courses. The contextlevel for courses is 50.

Each context is described in a table (mdl_context) which gives each context an ID, describes the contextlevel, the depth it occurs at (system, category, sub-category^nth, course, resource) and the instanceid. In this table, instance id refers to the specific id of the item. ie, instanceid 474 occurs twice in my table - once for a user and once for a course.

There are various ways people can be enrolled on a course. The methods in which people are or may be enrolled are listed in a table (mdl_enrol) which describes an enrolment method, related to a courseid and states if it is currently allowed. It also describes a sort order for these methods and an id for each position in the table.

There are various roles users can have within courses and across the site. The different roles are described in  a table (mdl_role) and given an id.

There is a table (mdl_role_assignments) which assigns a roleid to each userid with a specific contextid.

So, in role_assignments, for a random userid there is an entry:

id   | roleid | contextid | userid | component
2169 | 5      | 3611      | 1034   |

Then, in context we can look for id 3611 and see


id   | contextlevel | instanceid | path | depth
3611 | 50           | 152        |      | 4

We know from the context level that this is a course, with instance id 152


So, lets look in mdl_course for id 152


id  | category | Course name and other details -->
152 | 49       | 

So we know the user, the course they are enrolled on and the role they have in that course. However, when I did my count, not all the students were listed. If we look in mdl_enrol, we can see this course only allows manual enrollments. 


So lets look at course 588. This allows manual, category and two meta enrollments. We'll try to track down the students in meta enrollment id 2926:


id   | enrol | status | courseid 
2926 | meta  | 0      | 588

so in mdl_context lets look for contextlevel = 50 and instanceid = 588



id    | contextlevel | instanceid | path | depth
10662 | 50           | 588        |      | 4

and in mdl_role_assignments lets check out contextid 10662



id   | roleid | contextid | userid | component
3835 | 3      | 10662     | 21     | enrol_meta
3836 | 4      | 10662     | 405    | enrol_meta
3837 | 5      | 10662     | 46     | enrol_meta 
3838 | 5      | 10662     | 48     | enrol_meta
...

and I get 14 entries. 1 teacher, 1 non-editing teacher and 12 students.

EDIT: see, that's not actually helpful. I just checked the course and of the two meta linked courses one has 44 and the other 77 students.


There must, somewhere in one of these tables be an enrolid column, surely? Where I can look up mdl_enrol.id = 2926 (and 2925, which was the other option)



There is, in mdl_user_enrolments a column called enrolid, so I'll just look into that... There are 131 rows for 2926 and 133 for 2925. While that sounds more promising a little cross checking shows that there is too high a percentage of people who are not enrolled on this course listed here. Additionally, apart from the two additional userids in 2925, both lists are identical.

id    | status | enrolid | userid
46550 | 0      | 2926    | 17
46551 | 0      | 2926    | 18

Lunch break, I think.

EDIT (a couple of days later!): 


select
count (distinct mdl_user_enrolments.userid) as 'Students'
, mdl_context.instanceid as 'Course ID'
, mdl_course.fullname as 'Course Name'
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_enrol on mdl_course.id = mdl_enrol.courseid
join [moodle].[dbo].mdl_user on mdl_role_assignments.userid = mdl_user.id
join [moodle].[dbo].mdl_user_enrolments on mdl_enrol.id = mdl_user_enrolments.enrolid

where mdl_context.contextlevel = '50'
group by mdl_context.instanceid, mdl_course.fullname
order by [Course Name] asc

This query counts people enrolled on a course. It is not just students - but this is because a lot of my students do not have a specific role assigned to them - if you want to count students only, include "and mdl_role_assignments.roleid = '5'". The difficulty I was having was due to stupidity on my part (I was crosschecking on the wrong set of data :( )