Tuesday, 5 May 2015

Count all activities on Moodle

It's that time again - I get to produce a report from the Moodle database.

The purpose of this blog is more or less a placeholder. I've written up my SQL:

SELECT `mdl_course`.`fullname` as 'Course Name'
, mdl_course.id as 'Course ID'
, sum(mdl_modules.id=1) as 'Assignment'
, sum(mdl_modules.id=2) as 'Chat'
, sum(mdl_modules.id=3) as 'Choice'
, sum(mdl_modules.id=4) as 'Data'
, sum(mdl_modules.id=5) as 'Forum'
, sum(mdl_modules.id=6) as 'Glossary'
, sum(mdl_modules.id=7) as 'HotPot'
, sum(mdl_modules.id=12) as 'Quiz'
, sum(mdl_modules.id=14) as 'SCORM'
, sum(mdl_modules.id=15) as 'Survey'
, sum(mdl_modules.id=16) as 'Wiki'
, sum(mdl_modules.id=17) as 'Workshop'
, sum(mdl_modules.id=22) as 'Feedback'
, sum(mdl_modules.id=25) as 'TurnitinTool'  
FROM mdl_course

join mdl_course_modules on mdl_course.id = mdl_course_modules.course
join mdl_modules on mdl_course_modules.module = mdl_modules.id

group by 'Course name'

ORDER BY 'Course ID' ASC


and as you can see it's very pretty and sensible and should (theoretically) return the number of each activity type that features in courses on the site. What actually returns is:

Course NameCourse IDAssignmentChatChoiceDataForumGlossaryHotPotQuizSCORMSurveyWikiWorkshopFeedbackTurnitinTool
GC VLE10302310289724715898929911122391501655

While this is surprisingly handy, it has to be said it is

  1. inaccurate - we have many assignments. But I think this is a result of the assignments now being a wholly separate situation from the other module types, and therefore not being registered on the module list. I need to look into this again.
  2. not what I need. I need each course to be listed and the activity types *per course* to be clearly identified. 
I have absolutely no idea why it isn't working as it should, but I have a feeling my joins are causing the issue.