 |
Summary | Discourse Data Explorer allows you to make SQL queries against your live database, allowing for up-to-the-minute stats reporting. |
 |
Repository Link | https://github.com/discourse/discourse-data-explorer |
 |
Install Guide | How to install plugins in Discourse |
If you’re looking for examples or support for any custom queries, you can find lots of topics in our Data & reporting category under the sql-query tag. If there’s not one to suit your particular needs, please feel free to create a new topic to ask the community for some help.
Query Help
After installing the plugin, head to /admin/plugins .
Click on the “Settings” button, and turn on data explorer enabled, then return and reload the page.
There should now be a tab in the stacked nav called “Data Explorer”. Select it or head to /admin/plugins/explorer , then use the + , Import or Run Query buttons to get started.
Default Queries
On a fresh install, the data explorer now ships with several queries that can help you draw insights from your forum’s activity. Open any query and click Run Query to try it out.
Here's what the stock queries look like.
Note: Default queries are picked up from this file: discourse-data-explorer/lib/discourse_data_explorer/queries.rb at main · discourse/discourse-data-explorer · GitHub
If you have an awesome query that you want everyone to have, make a PR just like this one.
Default queries cannot be edited, feel free to copy the sql and paste it into a new query if you’d like to modify them.
Writing Queries
When you click any of the Edit buttons on a query view, or start a new Explorer Query, you are presented with a screen like the one below:
The top two boxes are the name and description of the query. The name is what you’re selecting in the dropdown box, and the description should be used for things like explaining what to put in the query parameters or to describe what data is being queried.
The left pane is where you write the SQL query. Minor syntax highlighting and checking is provided.
Importing Queries
For an example, let’s import one that I wrote. Download this file, then open the import dialog and select the file to be uploaded or paste the query in the text box. Click Import to save it and then click Run Query to see the results.
most-common-likers.dcquery (1).json (442 Bytes)
Looks like all of the Discourse developers like each other quite a bit 
Searching For Queries
If you’ve accumulated too many awesome queries, no problem! The search bar can help you filter through titles and descriptions.
Allow non-staff groups to run queries
You can add specific groups (including moderators) to a query to grant them access to it:
After saving, you can find it from the Groups page for that particular group on the Reports tab:
Click any query to open it and then the Run button to see the results. You can also bookmark group reports by clicking the Bookmark button.
Sharing queries with predefined parameters
If you have included user-defined parameters in your query, you can share a link to the query and include the parameters in the URL so they will be pre-filled when the page loads.
You must include parameters in the URL in this format params={"parameter_name":"value"}. Separate multiple parameters with commas. For example:
discourse.example.com/g/my-group/reports/6?params={"param1":"value1","param2":"value2"}
Result Limits and Exporting Queries
With large queries, you may notice that there is a limit on the number of rows that the Data Explorer will display. By default, the limit is set to 1000 rows. This is to prevent excessively large queries from slowing down or disrupting the performance of your Discourse instance.
If you want to bypass this limit, you have a few of options:
- Pagination: You can paginate your results. Instead of trying to return all results at once, you can write your query to return a specific range of results. You can then adjust this range to view different sections of your data. This won’t increase the limit, but it will allow you to view all your data in sections.
- Increase the limit: If you have access to the Discourse server and are comfortable making changes to Discourse settings, you can increase the limit. However, this should be done with caution. Increasing the limit can put a lot of load on your server if you’re not careful, especially if multiple users are running large queries at the same time.
-
Download the Results: You also have the option to download the query results instead of viewing them directly in Discourse by clicking either the
JSONorCSVbutton after running the query. Data downloaded from a query in JSON or CSV format will have a much higher results limit of 10,000 rows. For programmatic purposes, you want want to opt for the Json format, or if you’d like to work on the data in a spreadsheet application, the CSV format might be more convenient.
It’s generally a good idea to try and write more efficient queries, or to filter your data so that you’re only retrieving what you need, rather than trying to bypass the limit.
You can also export the SQL query itself in .JSON format by clicking the Export button. Queries that are exported using this method can then be imported into other Discourse sites, or used with third party applications.
Query Plan
The “Include query plan” checkbox can be used for better understanding and optimizing your SQL queries.
When you check this box and run a query, Discourse will display the sequence of operations used to access data in the Discourse database. This can include operations like scans, joins, sorts, and other database actions. By examining the query plan, you can see exactly how the database is executing your query.
The Data Explorer Tutorial Series
We also have a dedicated sql-tutorial series about the Data Explorer! If you’re interested in learning more about writing SQL queries in Discourse, we highly recommend reading through the topics in this series:
- Data Explorer Tutorial - Part 1 - Writing Your First Query
- Data Explorer Tutorial - Part 2 - Discourse SQL Basics
- Utilizing Parameters in Data Explorer Queries
- Formatting Data Explorer Table Results
Last edited by @rishabh 2024-10-16T05:34:13Z
Check document
Perform check on document:
