How to run Data Explorer queries with the Discourse API

Virtually any action that can be performed through the Discourse user interface can also be triggered with the Discourse API. For a general overview of how to find the correct API request for an action, see How to reverse engineer the Discourse API.

To run a Data Explorer query from the API, you need to make a POST request to /admin/plugins/explorer/<query-id>/run. You can find the a Data Explorer query’s ID by visiting the query on your site and getting the value of the id parameter that you’ll see in your browser’s address bar.

This example query has an ID of 20 on my site. It returns a list of topics, ordered by views, for a given date:

--[params]
-- date :viewed_at

SELECT
topic_id,
COUNT(1) AS views_for_date
FROM topic_views
WHERE viewed_at = :viewed_at
GROUP BY topic_id
ORDER BY views_for_date DESC

This query can be run from a terminal with:

curl -X	POST "https://discourse.example.com/admin/plugins/explorer/queries/20/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <your_all_users_api_key>" \
-H "Api-Username: system" \
-F 'params={"viewed_at":"2019-06-10"}'

You will need to substitute your site’s base URL, query ID, and All Users API key into the example.

Handling queries that return over 1000 rows

By default, the Data Explorer plugin returns a maximum of 1000 rows. You can bypass this limit by adding a limit=ALL parameter to the request. The query below will generate 10000 rows.

SELECT
*
FROM generate_series(1, 10000)

On my site, the query’s ID is 26. Here’s how to get all the query’s rows:

curl -X POST "https://discourse.example.com/admin/plugins/explorer/queries/26/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <your_all_users_api_key>" \
-H "Api-Username: system" \
-F "limit=ALL"

Paginating the results

For a query that returns a very large data set, you can paginate the results. Given this example query (it has an id of 27 on my site):

--[params]
-- integer :limit = 100
-- integer :page = 0

SELECT
*
FROM generate_series(1, 10000)
OFFSET :page * :limit
LIMIT :limit

you can return the first 100 rows with

curl -X POST "https://discourse.example.com/admin/plugins/explorer/queries/27/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <your_all_users_api_key>" \
-H "Api-Username: system" \
-F 'params={"page":"0"}'

You can keep incrementing the value of the page parameter until the result_count that is returned is 0.

For details about authenticating API requests, see the Authentication section of our API Documentation: https://docs.discourse.org/

9 Likes

Thanks @Simon for putting that how to together! It’s very clear, and I appreciate that you included a custom parameter in your example. In the response of a test query, I see "default_limit": 1000. One thing that I’m still not sure of is how to handle large sets with the api.

So I have two questions:

  1. Can I easily increase the default cap for result sets with >1000 rows?
  2. In a really large set (eg. > 1000000 rows) can you suggest a pattern for iterating through the subsets? My first take would be to set two parameters :limit and :offset in the query, and iteratively paginate the set with each call. But I would love to hear if you have a better solution.

Thanks again!

3 Likes

Those are good questions. I’ve updated the topic to give details about setting the query’s limit and paginating the results.

5 Likes