CODEWITHSUNDEEP
sqlmoodle
MOZ
MOZ

Reputation: 758

Moodle trainer profiles and their courses sql query

i want to get list of trainers and their courses:

I am using following query

SELECT u.id, 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

i am only getting single course, need help on this.

Upvotes: 0

Views: 176

Answers (2)

Russell England
Russell England

Reputation: 10241

If you are using $DB->get_records_sql() then the first column needs to be unique. So you need to combine the userid and the course id.

$sql = "SELECT CONCAT(c.id, '_', u.id) AS uniqueid,
                    u.id AS userid,
                    u.firstname,
                    u.lastname,
                    u.email,
                    c.id AS courseid,
                    c.fullname,
                    r.id
            FROM {user} u
            JOIN {role_assignments} ra ON ra.userid = u.id
            JOIN {role} r ON r.archetype = :archetype AND r.id = ra.roleid
            JOIN {context} cx ON cx.id = ra.contextid AND cx.contextlevel = :context
            JOIN {course} c ON c.id = cx.instanceid";
$params = array('context' => CONTEXT_COURSE, 'archetype' => 'teacher')
$trainers = $DB->get_records_sql($sql, $params);

Upvotes: 0

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

Try below one

SELECT u.id, u.firstname, u.lastname, u.email, c.fullname
from mdl_context cx 
Left join mdl_course c ON cx.instanceid = c.id
Left join mdl_role_assignments r  on  r.contextid = cx.id
Left join mdl_user u on u.id = r.userid
WHERE r.roleid =3
AND cx.contextlevel =50

Upvotes: 1

Related Questions