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

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

by MaXor blu -
Number of replies: 14
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: -
In reply to MaXor blu

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

by Guido Vega -
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)
In reply to Guido Vega

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

by Guido Vega -

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

Attachment moodle_relations1.jpg
Average of ratings: Useful (5)
In reply to Guido Vega

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

by Miki Alliel -
Picture of 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: -
In reply to Miki Alliel

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

by Miki Alliel -
Picture of 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: -
In reply to Miki Alliel

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

by Anthony Borrow -
Picture of Core developers Picture of Plugin developers Picture of 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)
In reply to Anthony Borrow

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

by Miki Alliel -
Picture of Translators

Ok

thanks a lot

Average of ratings: -
In reply to Miki Alliel

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

by Dinkar Thakur -

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

Average of ratings: -
In reply to Dinkar Thakur

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

by james mergenthaler -

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 (5)
In reply to Anthony Borrow

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

by james mergenthaler -

this is useful...thanks

Average of ratings: -
In reply to james mergenthaler

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

by Dinkar Thakur -

Thanks James this is what i was looking for smile

Average of ratings: -
In reply to Guido Vega

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

by Pragnesh Karia -

Nice post , this is really helpful.

Average of ratings: -
In reply to MaXor blu

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

by Vu Tuan Anh -

In your question, you need a query to get teacher. There is a way help you get contact of course of a specific course and print as html code without using query as the following:

$course = $DB->get_record('course', array('id' => $courseid), '*', MUST_EXIST);
require_once($CFG->libdir. '/coursecatlib.php');
$tmpCourse = new course_in_list($course);
var_dump($tmpCourse);
//Add teacher name
// display course contacts. See course_in_list::get_course_contacts()
if ($tmpCourse->has_course_contacts()) {
    $html .= html_writer::start_tag('ul', array('class' => 'teachers'));
    foreach ($tmpCourse->get_course_contacts() as $userid => $coursecontact) {
        $name = $coursecontact['rolename'].': '.
            html_writer::link(new moodle_url('/user/view.php',
                array('id' => $userid, 'course' => SITEID)),
                $coursecontact['username']);
        $html .= html_writer::tag('li', $name);
    }
    $html .= html_writer::end_tag('ul'); // .teachers
}

Average of ratings: -
In reply to Vu Tuan Anh

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

by eli chan -

Thank you for sharing, it works for me to export teacher name in excel.



Average of ratings: -