Data Explorer Plugin

@riking Am I able to submit queries via the discourse api? If so, how? Thanks!

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?

11 Likes

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?

1 Like

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.

4 Likes

8 posts were split to a new topic: Unable to select groups in data explorer

  1. After making the query available for group everyone, what’s the link to access it for the website visitors?

  2. Is there a way to “public” a result of a query to a public static page? (without the Run button, 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.

5 Likes

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?

1 Like

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.

3 Likes

How can I prevent Data Explorer from cooking the results? Example:

SELECT group_id, user_id FROM group_users

What I get:

image

What I want:

image

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.

6 Likes

Don’t get me wrong, I like it too. But occasionally I need the numbers. Your suggestions works for me, thanks.

3 Likes

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… “key”: “value”


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?

Hey there!

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:

4 Likes