Moodle courses without students and teachers

Michael Ghens

A good place to start with Moodle ad-hoc reports can be found here: ad-hoc contributed reports. Moodle rooms have a tool that allows you to create reports. Issue with it is that it does not always run sql that can be run with other tools.

These are two reports that I did for this tool.

First one was for total number of courses with no users by category.

SELECT *
FROM
  (SELECT cr.shortname,
        cr.fullname,
        COUNT( ra.id ) AS enrolled
  FROM `mdl_course` cr
  JOIN `mdl_context` ct
  ON ( ct.instanceid = cr.id )
  LEFT JOIN `mdl_role_assignments` ra
  ON ( ra.contextid     = ct.id )
  WHERE ct.contextlevel =50
  GROUP BY cr.shortname,
        cr.fullname
  ORDER BY `enrolled` ASC
  ) courses
WHERE enrolled = 0;

The second was to list the names of the courses.

SELECT DISTINCT
  (SELECT name FROM mdl_course_categories WHERE id=catid
  )                AS Semester,
  COUNT(shortname) AS empty_class
FROM
  (SELECT cr.shortname,
        cr.fullname,
        cr.category    AS catid,
        COUNT( ra.id ) AS enrolled
  FROM `mdl_course` cr
  JOIN `mdl_context` ct
  ON ( ct.instanceid = cr.id )
  LEFT JOIN `mdl_role_assignments` ra
  ON ( ra.contextid     = ct.id )
  WHERE ct.contextlevel =50
  GROUP BY cr.shortname,
        cr.fullname
  ORDER BY `enrolled` ASC
  ) courses
WHERE enrolled = 0
GROUP BY semester
ORDER BY catid DESC;

Comments