Want to extract some data from your Discourse site but not sure where to start? You’ve come to the right place! This is a tutorial guide for beginners who are new to the Data Explorer. If you’re already familiar with SQL and how to use the Data Explorer, then you will most likely not need to read these topic, and may want to check out our data & reporting category instead.
The goal of this guide is to help you get a basic grasp of how to use the Data Explorer to create reports on your your Discourse site, and is the first part of our Data Explorer Tutorial series.
What is the Data Explorer?
The Data Explorer is a Discourse plugin that allows you to run SQL queries against your database and export the results. It’s a powerful tool for extracting and analyzing data from your Discourse site.
Before you can use the Data Explorer, you’ll need to install it on your site. See Install plugins on a self-hosted site if you need any assistance with this.
Once you’ve installed the Data Explorer, you can start using it to run SQL queries.
Creating our first SQL Query
The best way to get familiar with the Data Explorer, is to get some hands on experience!
Let’s get started by creating our first query:
- Head to the admin on your site and click on the
Pluginstab. - Find the
Data Explorerplugin and click on it. - Click on the
+button to view a textbox and button for creating a new SQL query. - Type in the name of your query.
- Click
Create Newto create the query.
From here we can start building out our first query.
The Default New Query Page:
Hello World
For our first query let’s create a report that will show us: The Most Viewed Topics on the Site 
Thinking of how we’ll need to create this, let’s start by selecting the title, and views fields from the topics table:
SELECT
title, views
FROM
topics
Which will show us a list of all topics on the site and the number of views they have. Click Run to see this in action:
Sorting Results
Looking at these results, you’ll notice they aren’t ordered - this is because we need to explicitly use an ORDER BY statement to sort our results as desired. Let’s add this in now:
SELECT
title, views
FROM
topics
ORDER BY views DESC
Let’s click Run again to see what changed:
There we go - our results are now sorted in descending order by views!
Formatting Results
With this query, wouldn’t it be nice if we could click directly on the name of the topic and be taken to the topic? Good thing we can do this with Formatted Table Results!
Let’s change the title section of our query to id as topic_id to see this in action:
SELECT
id as topic_id,
views
FROM
topics
ORDER BY views DESC
Now the titles of the topics are displayed as hyperlinks!
repliesthe topic has.
Adding Parameters and WHERE statements
For our final addition to the query, let’s modify the query so that we only see topics with more than X number of views. To do this we’ll need to add a parameter, and a WHERE statement. For our parameter, we’ll use an int (integer), and the WHERE statement will filter the results so that only topics with more the minimum number of views are displayed.
-- [params]
-- int :min_views= 10
SELECT
id as topic_id,
views
FROM
topics
WHERE views > :min_views
ORDER BY views DESC
When running this query, you’ll now see that there’s a min_views parameter that you can use to change the minimum number of views topics are required to have to be displayed in the results.
Wrapping Up
Hopefully you’re starting to get an idea of how Data Explorer queries are built after going through these steps, however, we’ve barely scratched the surface in this topic!
The Data Explorer, and more generally, SQL is a very, very broad subject. If you’re interested in learning more about the Data Explorer and SQL Queries, we highly recommend reading the other topics in this series.