Yes, Data Explorer queries can be run through the API. I just created a #howto topic for this. Can you have a look at it and let me know if anything in the topic isn’t clear: How to run Data Explorer queries with the Discourse API?
A post was merged into an existing topic: How to run Data Explorer queries with the Discourse API
Is it possible to connect to the db from an external tool like Sequel Pro?
You would need to expose the port and set a password for the database. Have at How to move from standalone container to separate web and data containers for example of setting a password.
EDIT: I just saw that you are in the customer group, which implies you’re a hosted customer. That almost certainly means that no, you can’t have direct access to the database.
8 posts were split to a new topic: Unable to select groups in data explorer
After making the query available for group
everyone, what’s the link to access it for the website visitors?
Is there a way to “public” a result of a query to a public static page? (without the
Runbutton, and with the results table shown instantly).
OR have a tag that can be embedded in a topic message
I think the group queries are only meant to be used by custom groups. The reports are accessed by clicking the Reports link on the group’s page:
I’m not sure if this is intended to work, but queries that are made available to trust level members can be found at
/g/trust_level_<level>/reports. For example
https://community.example.com/g/trust_level_0/reports. Since there is no route for the
everyone group, this won’t work for that group, but using trust level 0 should accomplish the same thing for most cases.
Thanks Simon, all this information is available in the very first message of this topic. However, unfortunately, it does not solve the issue I described above.
Would it be possible to create a Slackbot that will be fetching data from Data Explorer?
Are there even any endpoints I can hit to obtain the data I query in Data Explorer? I checked public Discourse API but actually found nothing
Totally doable. You can inspect the API call your browser makes for details. It gives you all the info and works wonders for pretty much every action done on Discourse.
How can I prevent Data Explorer from cooking the results? Example:
SELECT group_id, user_id FROM group_users
What I get:
What I want:
Export the data to a file, is the typical way you handle that.
The plugin has the “use the whatever_id to get the fancy” feature. I like it, but it did catch me a bit by surprise at first.
What I have done when I’ve wanted the numbers not the fancy is to use aliases eg.
… whatever_id AS whateverid …
Not as easy to read without the underscore but it gets the numbers.
Don’t get me wrong, I like it too. But occasionally I need the numbers. Your suggestions works for me, thanks.
Is there a way to format a column to show an icon with a link? I just query the uploads, but URLs in the result are so long.
Is there a way to force the JSON export to be JSON with “key”:“value” pairs?
We are exporting a huge amount of data, the JSON payload currently looks like:
44788, 19363, 1, 22, 21, 41, 4020
So just the value part with no key…
In order to forward it on to a place where we can chart it over time (charting the view/like counts to see which posts get more popular being a basic example), we need the JSON to come in “key”:“value” pairs…
"views": 44788, "userId": 19363, "replyCount":1, "avgTime": 22, "likeCount": 21, "categoryId":141, "wordCount": 4020
I’m struggling to find anything I can add on to the SQL, but maybe I’m missing something. Any one here have any tips?
How would I go with Data Explorer queries about fetching number of solutions provided per users let’s say per month?
There are a couple of example data explorer queries at the bottom of the plugin’s topic - they could be useful as a starting point:
The problem with this query at the bottom is that it gives a number of accepted solutions so the sum of the ones someone accepted as a solution as well as the ones that someone given and were accepted by someone else as a solution. I want to create a query that will fetch users and the number of solutions only they themselves provided.