Sneak Under The SQL Hood Of Moodle With This Presentation

For developers, getting acquainted with the relational model that characterizes the information architecture of Moodle is a basic skill. But for the rest of us, getting to know how Moodle functions underneath can offer us some insight or welcome intellectual challenge.

Post Pages - Post Inline - WIRIS

Previously we have reviewed the work of Moodler Marcus Green, who showed us a bird’s-eye view of the tables and connections that make up the Moodle data model.

This time, it is the turn of James MacNee, developer at West College Scotland, and a pride SMUG. Member of the Scottish Moodle User Group, that is.

MacNee delivered his workshop, Running SQL Queries on the Moodle Database as requested by SMUG. It takes us further into the detail of columns rows, keys and selection of the data for Moodle. The possibilities of SQL queries are limitless: verifying editing permissions of administrative roles, track the course path of a cohort of students, analyze engagement and feedback by instructor controlling by gender or other characteristics; you name it.

To begin, MacNee presents us with the toolbox, which includes:

Trying out different queries, to list users and permissions for example, could arouse one’s curiosity, but if the results are to cryptic or massive, the opposite can be the case. MacNee suggests applying limits to the number of results or use aggregators for numerical values, such as averages.

Using the tools to make changes on the database directly, MacNee warns, is “technically possible“. But this should be done with extreme caution and subject matter expertise.

The good things about the plugins is that upon installation, they become available right away for admins, on the “Site Administration” panel in Moodle. phpMyAdmin works as a different environment, and by definition it only works with MySQL databases, in any case the default. You’re better off verifying.

The results offered by the tools are, in the words of MacNee, “elegant“. The reports can be set up to share to other, perhaps less technically savvy, or they can be generated as “dumps” in the command line, for personal or more informal situations.

See MacNee’s slides below:

[slideshare id=69918432&doc=smug-161207152149]

Suggest an interesting SQL query in the comments for your fellow MoodleNews readers!

moodlerooms-logoThis Moodle Practice related post is made possible by: MoodleRooms the open source learning experience by Blackboard. Rediscover Moodle. Click here to learn more.


Previous articleVoting About To Close At The Moodle Users Association Project Development Cycle
Next article3.2.1 Go! January Updates For All Active Moodle Versions


  1. Using sed manually might cause encoding issues when configured incorrectly. Please, if you want to find/replace data in the db, always use the (hidden) admin tool at /admin/tool/replace :-).

  2. […] Another wide-ranging tool to show detailed student activity in Moodle comes courtesy of the Ad-hoc contributed reports plugin. It takes advantage of the SQL database engine that powers Moodle to let you query the data and produce any report you can think of. Its usefulness is proportional to your familiarity with the relational model and the specific syntax of the database engine, which by default is MySQL. See it at work here. […]


Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.