Hi all,
Like the title points it out, I am looking for a sql query to get a list of students in each course,
It's not really a problem, but the natural way to do that for me is using the table mdl_course, mdl_enrol, and mdl_user_enrolments.
But, after looking at this forum, I found out that everyone use mdl_course, mdl_role_assignments and mdl_context.
I would like to understand why,
If anyone can help me,
Thanks
In reply to John Smith
Re: Databases: SQL get list of students in each course
by james mergenthaler -
Martin, everything in moodle is about context and roles. When students are enrolled in a course, they are enrolled in the role of students in the context of a course. When users are assinged course creation rights for all the english courses, they are assigned the role of course_creator, in the context of a category. Etc. That rol_assignments table is kinda the core for queries about people in the system. "roles in context". That mdl_uers_enrollments table does not have a direct relationship with the course table. The mdl_enrol tables contains information about the type of enrollment a course is using. Someday moodle may use FK (foreign keys) which will make the relationships more clear, IMHO.
A query like this to your question:
SELECT
c.id AS courseid,
c.fullname,
u.username,
u.firstname,
u.lastname,
u.email
FROM
mdl_role_assignments ra
JOIN mdl_user u ON u.id = ra.userid
JOIN mdl_role r ON r.id = ra.roleid
JOIN mdl_context cxt ON cxt.id = ra.contextid
JOIN mdl_course c ON c.id = cxt.instanceid
WHERE ra.userid = u.id
AND ra.contextid = cxt.id
AND cxt.contextlevel =50
AND cxt.instanceid = c.id
AND roleid = 5
ORDER BY c.fullname
For more information, moodle roles and context
In reply to james mergenthaler
Re: Databases: SQL get list of students in each course
by John Smith -
OK, thanks for the explanation, my understanding is better now
In reply to james mergenthaler
Re: Databases: SQL get list of students in each course
by yuki yuki -
Your query is giving results who are locally assigned (suppose course->assign roles)...
but when a user assigned globally meaning assign roles in system (Users->Permission->Assign system roles) then how to get those users by query?. Can you please give me the hints/or that query?
but when a user assigned globally meaning assign roles in system (Users->Permission->Assign system roles) then how to get those users by query?. Can you please give me the hints/or that query?
have a look at these apis, might help you
get_enrolled_sql
get_enrolled_users
get_role_users
Can you please elaborate Mr Ankit?
Whats these? I am newbie
Whats these? I am newbie