SQL report of Cohorts on Courses

SQL report of Cohorts on Courses

by Tim Roper -
Number of replies: 4

We have a system whereby students are registered on Cohorts and Cohorts are assigned to courses. This is so we can keep the same Course but de-register Cohorts when a student has finished studying.

We need a report of which student is registered on which Cohort and which Cohort is assigned to which Course


We have worked out how to get Students in Coorts but having trawled through all the Moodle tables nowhere can I find a link between Cohorts and Courses....

Does anybody know? Can anybody help?

Thanks

Tim

Average of ratings: -
In reply to Tim Roper

Re: SQL report of Cohorts on Courses

by Albert Ramsbottom -

Maybe there is something in here

https://docs.moodle.org/27/en/ad-hoc_contributed_reports


If this helps please rate it smile


Albert

Average of ratings: -
In reply to Albert Ramsbottom

Re: SQL report of Cohorts on Courses

by Tim Roper -

Hi Albert

Thanks for the link  - A very useful site but unfortunately hasn't got what we need...

Thanks anyway

Tim

Average of ratings: -
In reply to Tim Roper

Re: SQL report of Cohorts on Courses

by Vani Bheemreddy -

Hi


I am also lost searching for link between courses and cohorts. Did you ever find a solution?

TIA

Average of ratings: -
In reply to Tim Roper

Re: SQL report of Cohorts on Courses

by Pavel Lomov -

Try this query - it is not so optimal because it represent cohort, which members have role 'student' in some courses.

SELECT DISTINCT
h.name AS "Cohort",
c.id AS "ID course",
c.fullname AS "Course name"
FROM prefix_cohort AS h
JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid
JOIN prefix_user AS u ON hm.userid = u.id
JOIN prefix_user_enrolments AS ue ON ue.userid = u.id
JOIN prefix_enrol AS e ON e.id = ue.enrolid
JOIN prefix_role_assignments AS ra ON ra.userid = u.id
JOIN prefix_context AS ct ON ct.id = ra.contextid AND ct.contextlevel = 50
JOIN prefix_course AS c ON c.id = ct.instanceid AND e.courseid = c.id
JOIN prefix_role AS r ON r.id = ra.roleid AND r.shortname = 'student'
WHERE 
UPPER(h.idnumber) LIKE UPPER(:idcohort) 
OR
UPPER(h.name) LIKE UPPER(:cohortname) 
AND e.status = 0 AND u.suspended = 0 AND u.deleted = 0
AND (ue.timeend = 0 OR ue.timeend > NOW()) AND ue.status = 0
ORDER BY h.name

PS: K.O - Query sholbe used in Ad-hoc database queries plugin

Average of ratings: Useful (2)