I need some help with a SQL query, Selecting teacher's names and email address from all the courses in the system.
I didn't fine a field in the mdl_user table which indicated that the user is in a specific role as teacher.
how can I do it?
Thanks
SQL query to find teacher's names and email from all the courses..
by Miki Alliel -
Number of replies: 9
In reply to Miki Alliel
Re: SQL query to find teacher's names and email from all the courses..
by Anthony Borrow -
Miki - There is no field in the mdl_user table. You have to rely on the role so the query is a bit trickier. Assuming that you do not have a variety of customized teacher roles and that your teacher role has an id of 3 in the mdl_role table a query something like:
SELECT DISTINCT u.username, u.firstname, u.lastname, u.email
FROM mdl_role_assignments AS ra, mdl_user AS u
WHERE ra.roleid =3
AND ra.userid = u.id
should give you what you are looking for. Essentially you are saying where the role id is 3 (i.e. a teacher) and the userid in the role assignment table matches the id field of the mdl_user table then display the DISTINCT username, firstname, lastname, and email (this prevents duplicates).
Peace - Anthony
SELECT DISTINCT u.username, u.firstname, u.lastname, u.email
FROM mdl_role_assignments AS ra, mdl_user AS u
WHERE ra.roleid =3
AND ra.userid = u.id
should give you what you are looking for. Essentially you are saying where the role id is 3 (i.e. a teacher) and the userid in the role assignment table matches the id field of the mdl_user table then display the DISTINCT username, firstname, lastname, and email (this prevents duplicates).
Peace - Anthony
In reply to Anthony Borrow
Re: SQL query to find teacher's names and email from all the courses..
by Andrea Zappi -
HI anthony i have another question of user and course.
Where i can find the association between user and course? In which table?
In mdl_role_assignments there isn't the course id.
Thank you!
Andrea
In reply to Andrea Zappi
Re: SQL query to find teacher's names and email from all the courses..
by Anthony Borrow -
Andrea - You are correct, in order to get to course you have to go through the context table. Role assignments are given for a user in a particular context. I believe the context levels are defined in the /lib/accesslib.php. You will be looking for contexts where the contextlevel is 50 (i.e. a course). The instanceid from contextlevel 50 corresponds to the course table's id field. So just think that you have role_assignments -> context -> course. Does that make sense? Peace - Anthony
In reply to Anthony Borrow
Re: SQL query to find teacher's names and email from all the courses..
by Andrea Zappi -
Hi Anthony
I understand now.
Thank you very much
Andrea
In reply to Anthony Borrow
Re: SQL query to find teacher's names and email from all the courses..
by Roger Emery -
Slight variation, but much the same:
SELECT *
FROM `mdl_user`
WHERE id
IN (
SELECT DISTINCT (
userid
)
FROM `mdl_role_assignments`
WHERE `roleid` =3
)
One thing I'm trying to write (but I'm not expert at this) is a query to list
Tutor and the Courses they have tutor rights to.
Example result:
Tutorname1, course1, course 2 etc
Tutorname2, course 2, course 3, course 4
and so on.
Would be very handy for contacting every tutor for end of year clean up.
Any help appreciated.
Roger
SELECT *
FROM `mdl_user`
WHERE id
IN (
SELECT DISTINCT (
userid
)
FROM `mdl_role_assignments`
WHERE `roleid` =3
)
One thing I'm trying to write (but I'm not expert at this) is a query to list
Tutor and the Courses they have tutor rights to.
Example result:
Tutorname1, course1, course 2 etc
Tutorname2, course 2, course 3, course 4
and so on.
Would be very handy for contacting every tutor for end of year clean up.
Any help appreciated.
Roger
In reply to Roger Emery
Re: SQL query to find teacher's names and email from all the courses..
by Teresa Gibbison -
Take a look at this post 'Given name of user, how to find his roles (permissions)' which might help here http://moodle.org/mod/forum/discuss.php?d=94627#p417794
Cheers
Teresa
Cheers
Teresa
In reply to Teresa Gibbison
Re: SQL query to find teacher's names and email from all the courses..
by Roger Emery -
Thanks Teresa, unfortunately I'm on 1.84, so 1.9 plug-ins are no go at the moment and it only seems to deal with users and roles, not users with roles assigned to courses.
In reply to Miki Alliel
Re: SQL query to find teacher's names and email from all the courses..
by Roger Emery -
Here's the answer (assuming tutor role has the id of 3 - if you have made custom roles you can modify this )
SELECT u.firstname, u.lastname, u.email, c.fullname
FROM mdl_user u, mdl_role_assignments r, mdl_context cx, mdl_course c
WHERE u.id = r.userid
AND r.contextid = cx.id
AND cx.instanceid = c.id
AND r.roleid =3
AND cx.contextlevel =50
SELECT u.firstname, u.lastname, u.email, c.fullname
FROM mdl_user u, mdl_role_assignments r, mdl_context cx, mdl_course c
WHERE u.id = r.userid
AND r.contextid = cx.id
AND cx.instanceid = c.id
AND r.roleid =3
AND cx.contextlevel =50
In reply to Roger Emery
Re: SQL query to find teacher's names and email from all the courses..
by Roger Emery -
Hello All,
I have another query that I'm stuck on - the inverse of the above really.
I need get a listing of all courses that *don't* have a teacher role assigned.
Getting in a right tangle trying write this as I'm effectively looking for nothing!
Through the year staff leave or change duties are there appear to be some courses with no tutor role assigned - in order to deal with them I need to know which ones they are
Anyone help with this one?
Many Thanks
Roger
I have another query that I'm stuck on - the inverse of the above really.
I need get a listing of all courses that *don't* have a teacher role assigned.
Getting in a right tangle trying write this as I'm effectively looking for nothing!
Through the year staff leave or change duties are there appear to be some courses with no tutor role assigned - in order to deal with them I need to know which ones they are
Anyone help with this one?
Many Thanks
Roger