General developer forum

Databases: Query to find teacher of a particular course in db

 
 
Picture of MaXor blu
Databases: Query to find teacher of a particular course in db
 
hi all
searching in the database to find in which table 'course' -> 'teacher' realationship exist but failed.
Can any one tell how to make a sql query of this?
Is there ready made queries for moodle database?

Thanks
 
Average of ratings: -
Repeal the guilt upon accusation copyright law S92a
Re: Databases: Query to find teacher of a particular course in db
 
Hi MaXor

I don't think there is a direct relation between a course and I teacher. I think the relation is more like:

course
context
role_assignments
user
id --------->
instanceid
userid ---------->
id


id
----------->
contextid



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

Regards,
GV

 
Average of ratings:Useful (2)
Repeal the guilt upon accusation copyright law S92a
Re: Databases: Query to find teacher of a particular course in db
 

Sorry, just realise the above diagram doesn’t come right in IE7


 
Average of ratings:Useful (5)
Picture of Miki Alliel
תשובה ל: Re: Query to find teacher of a particular course in db
Group Translators

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?

thanks..

 
Average of ratings: -
Picture of Miki Alliel
תשובה ל: Re: Query to find teacher of a particular course in db
Group Translators

Hi again

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?

thanks 

 
Average of ratings: -
Anthony Borrow
Re: תשובה ל: Re: Query to find teacher of a particular course in db
Group DevelopersGroup Moodle HQGroup Testers
Miki - From the /lib/accesslib.php file:

// context definitions
define('CONTEXT_SYSTEM', 10);
define('CONTEXT_PERSONAL', 20);
define('CONTEXT_USER', 30);
define('CONTEXT_COURSECAT', 40);
define('CONTEXT_COURSE', 50);
define('CONTEXT_GROUP', 60);
define('CONTEXT_MODULE', 70);
define('CONTEXT_BLOCK', 80);

Peace - Anthony
 
Average of ratings:Useful (3)
Picture of Miki Alliel
תשובה ל: Re: תשובה ל: Re: Query to find teacher of a particular course in db
Group Translators

Ok

thanks a lot

 
Average of ratings: -
ME
Re: תשובה ל: Re: תשובה ל: Re: Query to find teacher of a particular course in db
 

but here roleid is use as 3. is its always so.... what if it get changed???

 
Average of ratings: -
Picture of james mergenthaler
Re: תשובה ל: Re: תשובה ל: Re: Query to find teacher of a particular course in db
 

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.

 
Average of ratings:Useful (4)
Picture of james mergenthaler
Re: תשובה ל: Re: Query to find teacher of a particular course in db
 

this is useful...thanks

 
Average of ratings: -
ME
Re: תשובה ל: Re: Query to find teacher of a particular course in db
 

Thanks James this is what i was looking for smile

 
Average of ratings: -
Pragnesh Karia
Re: Databases: Query to find teacher of a particular course in db
 

Nice post , this is really helpful.

 
Average of ratings: -