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

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

由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
評比平均分數： -
In reply to MaXor blu

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

由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

評比平均分數：Useful (2)
In reply to Guido Vega

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

由Guido Vega發表於

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

附件 moodle_relations1.jpg
評比平均分數：Useful (5)
In reply to Guido Vega

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

由Miki Alliel發表於
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..

評比平均分數： -
In reply to Miki Alliel

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

由Miki Alliel發表於
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 

評比平均分數： -
In reply to Miki Alliel

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

由Anthony Borrow發表於
Core developers的相片 Plugin developers的相片 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
評比平均分數：Useful (3)
In reply to Anthony Borrow

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

由Miki Alliel發表於
Translators的相片

Ok

thanks a lot

評比平均分數： -
In reply to Miki Alliel

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

由Dinkar Thakur發表於

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

評比平均分數： -
In reply to Dinkar Thakur

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

由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.

評比平均分數：Useful (5)
In reply to Anthony Borrow

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

由james mergenthaler發表於

this is useful...thanks

評比平均分數： -
In reply to MaXor blu

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

由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
}

評比平均分數： -
In reply to Vu Tuan Anh

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

由eli chan發表於

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



評比平均分數： -