As a Moodle administrator, extracting specific data can often feel like searching for a needle in a haystack. But what if there was a simple, straightforward way to access the information you need directly from your Moodle database? Enter Ad-Hoc Database Queries, a powerful plugin that empowers you to do just that.

What are Ad-Hoc Database Queries?

Ad-Hoc Database Queries is a read-only plugin for Moodle that allows administrators to directly interrogate the Moodle database using SQL (Structured Query Language). This means you can run custom queries to pull out specific pieces of data and information, making it incredibly useful for a variety of administrative tasks.

Getting Started: Installation

First things first, you’ll need to download and install the plugin.

1. Download the Plugin: Head over to moodle.org/plugins/report_customsql.
2. Choose Your Version: Make sure to download the version that corresponds to your Moodle installation. For example, if you’re running Moodle 3.6, download the Moodle 3.6 plugin.
3. Install the Plugin:
   • Navigate to Site Administration > Plugins > Install plugins.
   • Choose the downloaded zip file to install.
   • Select “Install plugin from the zip file.”
   • Once validation is successful, click “Continue.”

Your Ad-Hoc Database Queries plugin is now ready to use!

Running Your First Query

Now that the plugin is installed, let’s explore how to use it.

1. Access the Plugin: Go to Reports > Ad hoc database queries.
2. Add a New Query: Click “Add a new query.”
3. Create Your Query:

• Give your query a descriptive name (e.g., “Users in Course”).
• Enter your SQL query in the text area.

Let’s try a simple example: finding all users enrolled in each course.

SELECT
    c.fullname AS CourseName,
    COUNT(u.id) AS NumberOfUsers
FROM
    mdl_course AS c
JOIN
    mdl_enrol AS e ON e.courseid = c.id
JOIN
    mdl_user_enrolments AS ue ON ue.enrolid = e.id
JOIN
    mdl_user AS u ON u.id = ue.userid
GROUP BY
    c.fullname;Code language: PHP (php)

• Scroll to the bottom and click “Verify SQL text.”
• If there are no errors, click “Save changes.”

You’ll instantly see the results, showing the number of enrolled users for each course. You can even download these results as a CSV file for further analysis.

More Practical Examples

The power of Ad-Hoc Database Queries lies in its flexibility. Here’s another useful query: finding courses with missing teachers.

1. Add a New Query: Create a new query called “Courses with No Teachers.”
2. Paste the Query:

SELECT
    c.fullname AS CourseName,
    COUNT(ra.userid) AS NumberOfTeachers
FROM
    mdl_course AS c
LEFT JOIN
    mdl_context AS ct ON ct.instanceid = c.id AND ct.contextlevel = 50 -- Context for course
LEFT JOIN
    mdl_role_assignments AS ra ON ra.contextid = ct.id
LEFT JOIN
    mdl_role AS r ON r.id = ra.roleid
WHERE
    r.archetype = 'editingteacher' OR r.archetype = 'teacher'
GROUP BY
    c.fullname
HAVING
    NumberOfTeachers = 0;Code language: PHP (php)

3. Save Changes: This query will display courses that currently have no assigned teachers, helping you quickly identify where to add them.

Understanding SQL and Moodle’s Database Structure

While the Ad-Hoc Database Queries plugin makes running queries easy, having a basic understanding of SQL and how Moodle’s database tables are structured is key to unlocking its full potential. Familiarizing yourself with Moodle’s data model will enable you to write more complex and targeted queries to extract exactly the information you need.

Chris Richter from Moodle at Ricoshae

I hope this overview of Ad-Hoc Database Queries has been useful! This powerful tool is just one of many ways to optimize your Moodle administration. If you’d like to learn more about essential Moodle admin tasks, explore my other guides on topics such as clearing the cache, running cron, setting tasks, debugging, and more.

Happy Moodling!