Friday, January 13, 2017

Getting to grips with the Moodle database

I've been a Moodler for many years and my work has been very much at the front end: web-based site administration, course creation and online facilitation. Every now and then, I do data analysis using Moodle reports and offer Moodle support to AuthorAID partner institutions. I've had a lot to do in each of these areas and I've been happily busy. But I had a nagging feeling about operating only on the surface of Moodle. So I decided to take the plunge into the world of databases with the unique 'MySQL Reporting for Moodle' course offered by HRDNZ, a Moodle Partner in New Zealand. I completed this course a couple of months back and I might write a course review at some point. For now let me just say that the main thing I got out of the course was an awareness and appreciation of the Moodle database.

Today I had the opportunity to work on the database of the Moodle site I administer. With more than 18,000 users and courses going back to 2011, it's not a small database. My goal was to get data related to forum ratings in a particular course, eg, how many posts were rated 'useful', which users received ratings, and which users gave ratings. The only way to do this, as far as I know, is to work with the Moodle database. There are no front-end reports that give you this information.

I actually made a post on a Moodle community forum yesterday asking for help, and today I posted a solution. It's not a detailed solution and definitely not the best. I'm sure there's a better way to do the whole thing using SQL queries. But for those who don't know SQL -- and those like me who have a smattering but would rather stick to CSV data for the moment -- my solution might be useful. Take a look if you're interested.