I need a query to list all courses which a student is enrolled. Help please!
For which Moodle version?
Tomek
The lastest
What have you tried?
Nothing because I started with moodle last week and I don't know the database very well yet
Hi. This is for 2.0.4. I hope it works for you.
SELECT concat( user2.firstname, " ", user2.lastname ) AS Name,
(SELECT group_concat(course.shortname separator ', ') FROM prefix_course AS course
JOIN prefix_enrol AS en ON en.courseid = course.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
WHERE ue.userid = user2.id) AS Curses
FROM prefix_user AS user2
Thank you so much!
You're welcome!
Hey Nechita,
I am using Moodle 1.9 version. I had the same query too. I want to list all the courses for which a student has enrolled for. Can you provide the query for 1.9 as well?
Thanks,
Vinay
Hi. This works on my test server.
SELECT user.firstname AS Firstname, user.lastname AS Lastname, (SELECT GROUP_CONCAT( course.shortname SEPARATOR ', ' )
FROM prefix_course AS course, prefix_role_assignments AS asg
INNER JOIN prefix_context AS context ON asg.contextid = context.id
WHERE context.contextlevel =50
AND user.id = asg.userid
AND context.instanceid = course.id) AS CourseName
INNER JOIN prefix_context AS context ON asg.contextid = context.id
WHERE context.contextlevel =50
AND user.id = asg.userid
AND context.instanceid = course.id) AS CourseName
FROM prefix_user AS user
or without group_concat
SELECT
user.firstname AS Firstname,
user.lastname AS Lastname,
course.shortname AS Course
FROM
e_user AS user,
e_course AS course,
e_role_assignments AS asg
INNER JOIN e_context AS context ON asg.contextid=context.id
WHERE
context.contextlevel = 50
AND
user.id=asg.userid
AND
context.instanceid=course.id
Hello Nicheta,
Thanks for your response. That query worked for me. I used the non-grp cat one. I have 1 more question - What does contextlevel=50 mean?? In the database I came across few other values like 40,80 etc. What difference does it make?
Also I want to list all the courses for which a teacher has been assigned to. Eg : teacher 1..the query lists his subjects - sub 1, sub 2, sub 3 & so on in which he has teacher role .
Thanks,
Vinay