Database model 2.9 or field teacher

Database model 2.9 or field teacher

by Omar Arnaldo -
Number of replies: 2

Good guys days.

I'm trying to make some queries to the database moodle to know the teacher who is assigned to a course, but nowhere see the connection to do it. In advance thank you very much and hope you can give me a smile hand version of moodle I'm driving is 2.9.

This consultation is part of another where I am seeing how many products have shipped in X matter but I need to know who is the teacher.

PS: If I can help with the structure of the database, I appreciate it.

Thank you and have a good time;)

Sorry for the English ...

Average of ratings: -
In reply to Omar Arnaldo

Re: Database model 2.9 or field teacher

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

You need to look to see what roles the users have assigned at the course level, in order to determine which of them are 'teachers' on the course.

So, first look up the roleid for the teacher role.

SELECT id FROM mdl_role WHERE shortname='editingteacher';

(Assuming 'editingteacher' is the role you are looking for).

Next you need the course context id.

SELECT id FROM mdl_context WHERE instance=[insert course id here] AND contextlevel = 50;

(contextlevel 50 is the course context)

Finally you can get a list of all users assigned the teacher role in the course context (but, note, this will not include users assigned the role at the level of an activity within the course OR users assigned the teacher role at the category or site level).

SELECT u.id, u.firstname, u.lastname
FROM mdl_user u
JOIN mdl_role_assignments ra ON ra.userid = u.id
WHERE ra.roleid = [insert teacher role id here] AND ra.contextid = [insert context id here];


Average of ratings: -
In reply to Davo Smith

Re: Database model 2.9 or field teacher

by Omar Arnaldo -

Davo Thanks for answering, I had this query below the stand, but what I need is to know where the courses are teachers. And the "ContextID" not sad which means that field and see that with "ContextID" could help me to know the course that are teachers, but this does not have to be related to "course"? Thanks I really You gave a clue (ContextID).

Again sorry for the English ...


select u.username, u.firstname, u.lastname, u.username

from mdl_role_assignments r 

inner join mdl_user u on r.userid=u.id and r.roleid='3';


or yours


SELECT u.id, u.firstname, u.lastname

FROM mdl_user u

JOIN mdl_role_assignments ra ON ra.userid = u.id

WHERE ra.roleid = '3'

Average of ratings: -