Data and E-Learning Part 11: Ad-hoc database queries

The previous post in this series introduced the Ad-hoc database queries plugin, a powerful tool for getting reporting data out of your Moodle installation. In this article we will look at how you can create a query using the plugin.

SQL

The plugin uses SQL commands to pull data out of Moodle. SQL is a language that is used for interacting with databases. We won’t be covering the specifics of SQL in these articles. There are several versions of SQL that your Moodle installation could be using which will change the specific commands required. There are plenty of websites that you can learn about SQL from and HRDNZ run an introductory course to using SQL with Moodle a couple of times per year. For these articles we will focus more on how you can use the plugin and the data it pulls out rather than the queries themselves.

Creating a query

From the Ad-hoc database queries page click on Add a new query.

We will work through each setting in order on the page. Select the category the query will sit in from the Category drop-down menu.

Provide a name for the query and a description. The name should make it clear what the query does and the description (optional) can be used to explain in more detail or to point out any filtering that the query performs.

Enter the code for the SQL query you wish to run. The code must be correct for the version of SQL your Moodle database uses.

You can decide who can access the query, the maximum number of rows to return and schedule a desired time and frequency for the report to be run. For scheduled reports you can have the report sent to a specific directory on your server or emailed to a user. Click Save changes once you have completed setting up the query. If there are errors in your query you will see a red box around the field with the error and an error message will appear beneath.

The error messages aren’t always the most helpful. In the example above there is a spelling mistake (with red highlighting beneath it). Fixing the error will allow the query to run successfully and the screen will show the results of the query (for on-demand queries).

Viewing the query results

Click on the name of the query (under the relevant category) to view the results of the query. In our example query the number of active users in the system is displayed.

We have seen in this article how to set up a query using the Ad-hoc database queries plugin. In the next article we will look at some of the approaches we can take to extracting data using this plugin.

You can view the previous posts in this article series here:

• Part 1: The Gold Mine
• Part 2: Data Types
• Part 3: Understanding Your Data
• Part 4: Developing Questions
• Part 5: Combining Datasets
• Part 6: Limitations of Data
• Part 7: Extracting Data From Moodle
• Part 8: Moodle Grader Report
• Part 9: Moodle Grade History
• Part 10: Ad-hoc database queries plugin

• About
• Latest Posts

Jeff Mitchell

P&C Reporting and Data Analyst at Fisher & Paykel Appliances

Jeff is passionate about the role of learning and development, and has a specific interest in how people and organisations can be developed in order to achieve their potential. Jeff has a keen interest in information technology and specifically data analysis and the e-learning space.

Latest posts by Jeff Mitchell (see all)

• Version Control – Git GUI – 7th March 2023
• Version Control – Git Command Line Tool – 7th February 2023
• Version Control – Creating Repositories – 7th January 2023