General developer forum
Databases: Query to find teacher of a particular course in db
I don't think there is a direct relation between a course and I teacher. I think the relation is more like:
|contextlevel = ’50’||roleid = ‘3’|
So you could try some SQL like:
SELECT c.id, c.shortname, u.id, u.username, u.firstname || ' ' || u.lastname AS name FROM mdl_course c LEFT OUTER JOIN mdl_context cx ON c.id = cx.instanceid LEFT OUTER JOIN mdl_role_assignments ra ON cx.id = ra.contextid AND ra.roleid = '3' LEFT OUTER JOIN mdl_user u ON ra.userid = u.id WHERE cx.contextlevel = '50';
You may want to change u.firstname || ' ' || u.lastname AS name for CONCAT(u.firstname, ' ', u.lastname) AS name if you are using MySQL.
I hope that helped
Sorry, just realise the above diagram doesn’t come right in IE7
Hello thanks for this query.
I tried to understand what is exactly context level =50
what does it mean exactly? and what is the different between context level =50 to 40 ,30 and so on?
Can someone answer me what does "context level =40,30,20, 10"
means? except from 50 that I understand it means the course context. correct me if I'm wrong.
so what are the other numbers referring to or mean?
// context definitions
Peace - Anthony
but here roleid is use as 3. is its always so.... what if it get changed???
DinKar, try something like this:
SELECT c.id, c.fullname,u.lastname,r.name
FROM mdl_course c
JOIN mdl_context ct ON c.id = ct.instanceid
JOIN mdl_role_assignments ra ON ra.contextid = ct.id
JOIN mdl_user u ON u.id = ra.userid
JOIN mdl_role r ON r.id = ra.roleid
WHERE name = 'Teacher' AND id = 5
You can change this query and add new columns from the joined tables. That is the key. You can use any role name in the WHERE clause. You could use roleid, rather than a string like 'Teacher'. The roleid is typically 3 for a teacher. Change the id (courseid) to a real value, remove the AND clause for more results.