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.
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.
Once you’ve installed the Data Explorer, you can start using it to run SQL queries.
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
- 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.
Create Newto create the query.
From here we can start building out our first query.
The Default New Query Page:
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
views fields from the
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:
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
Run again to see what changed:
There we go - our results are now sorted in
descending order by views!
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!
The numbers you’re seeing next to the topic are the number of
repliesthe topic has.
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.
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.