I want to learn as a list "students taking course A is also taking these courses". How can I make a query for that. Any help?
Thanks
You'll need to find the context ID of the courses you're after (using get_context_instance) and look up role assignments for the student role in those contexts from the role_assignments table.
If you need help with the actual SQL itself, you'll need to be a bit more specific about what you're trying to do. Do you want all students in course A but also in all of courses B, C, D..., or also in one of B, C, D..., or something else?
If you need help with the actual SQL itself, you'll need to be a bit more specific about what you're trying to do. Do you want all students in course A but also in all of courses B, C, D..., or also in one of B, C, D..., or something else?
Actually I need the query itself. I can not write one. I do not know programming. and my English is limited. What I want is a list as below.
Course name Students who takes Course X is also taking courses below
Course A Course B
Course C
Course M
Course C Course A
Course Z
and so on ...
I will use this list to make the exam schedule. So exams will not be overlapped. I really appreciate any help in query. Thanks in advance.
I see, so you want all the other courses taken by students on a given course. I don't think you'll find it very easy to do this purely in SQL, I think you'll at least need to run a query for each course in your system. A basic script to do this would look something like this, might not be spot on though:
<?php
include config.php; // Assuming this is in the root of your Moodle installation.
<?php
include config.php; // Assuming this is in the root of your Moodle installation.
$courses = get_records(course); // Get all the courses on the system
foreach ($courses as $course){ // For each course
echo '<h1>'.$course->fullname.'</h1> // Print its name
Students taking this course also take: // Print a list of other courses
<ul>';
$sql = 'SELECT fullname
FROM '.$CFG->prefix.'course AS c
-- Get the context id of each course
JOIN '.$CFG->prefix.'context AS con WHERE con.contextlevel = 50 AND con.instanceid = c.id
-- Get the role assignments for each course context
JOIN '.$CFG->prefix.'role_assignments AS ra ON roleid = 5 AND con.id = ra.contextid
-- Only select courses that users in the current course are enrolled on.
WHERE ra.userid IN (
SELECT DISTINCT userid
FROM '.$CFG->prefix.'role_assignments as ra1
JOIN '.$CFG->prefix.'context AS con1 ON contextlevel = 50 AND con1.id = ra1.contextid
WHERE con.instanceid = '.$course->id.'
);';
$othercourses = get_records_sql($sql);
foreach ($othercourses as $othercourse) {
echo '<li>'.$othercourse->fullname.'</li>';
}
echo '</ul>';
}
?>