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:

Jeff Mitchell
blank

Jeff Mitchell

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.

2 thoughts on “Data and E-Learning Part 11: Ad-hoc database queries

  • 7th May 2021 at 9:16 am
    Permalink

    Great post Jeff !
    I especially liked the screenshot of an error – far too many guides and tutorials assume that everything goes perfectly – and it’s reassuring when someone shows how even a simple type can cause an error message 🙂

    Reply
    • blank
      7th May 2021 at 9:35 am
      Permalink

      Thanks Stu. When writing code most of your time is spent fixing errors and they can be pretty confusing to track down. I’m sure there’s a post in there…

Add a reply or comment...

%d bloggers like this: