Load Data Explorer queries from library

It’s great to have these all in one place, rather than spread across meta!

I see you’ve organised them into your fork of the discourse-data-explorer repository. Did you have any plans to improve the plugin to access the pre-made queries directly?

I think it would be really cool to have a “load from library” button in the plugin, which allows browsing these queries. Adding new queries to the library would then just need a PR. Would that be #pr-welcome @team?

3 Likes

Yeah a nice way to share queries would be nice, I worry about vetting.

I think the best way is to ship data explorer with a large amount of built in queries, then we do PRs whenever we want to add queries.

Can you do mockups of the UI for that? I totally support adding something here.

8 Likes

I’m imagining a new button on the main plugin interface “Load from Library”

Which then opens a popup with a scrollable and filterable table of available queries

In terms of the data itself, I quite like how @SidV has stored them in their own folder, each in their own .sql file here

We would need to add some metadata to the top of the sql files. We already have [params], so maybe have something like

-- [title] Solved Answers
-- [description] Lists users with most solved topics
-- [source] https://meta.discourse.org/t/63981/4?u=sidv

-- [params]
-- string :interval = 1 year
SELECT  ua.acting_user_id, 
        count(case t.user_id when ua.acting_user_id then 1 else null end) as is_op, 
        count(case t.user_id when ua.acting_user_id then null else 1 end) as not_op,
        count(*) as total
FROM user_actions ua
LEFT JOIN topics t ON target_topic_id = t.id
WHERE action_type=15
AND ua.created_at >= CURRENT_DATE - INTERVAL :interval
GROUP BY ua.acting_user_id
4 Likes

I find the whole selection of queries from a combobox deeply flawed, and it breaks down big time when you have a lot of queries.

What we should simply do it display a full list of linkable queries on the front page, we can add an icon next to builtin to show that they are built in and not editable.

The whole “clone from library” workflow feels like busywork to me.

8 Likes

I agree about the combobox.

I was trying to keep within the existing structure of the UI, but rethinking it from scratch will probably give a better result. It does significantly increase the amount of work that this is though :wink:

4 Likes

I’m loving this initiative to make the best queries more readily available. But I wonder if it would not be safer and easier to just create a #queries category here on meta akin to the #plugin with a topic per query. Safe, approved queries could be provided there and broken queries could be moved to a #broken-queries category.

The advantage is that we can discuss, ask questions about and refine the queries better right here on meta. It’s also much simpler to implement though of course takes some gardening. :seedling:

3 Likes

Possibly, trouble is we would not have safety. How would we vet that a query or edit to a query is “safe”.

When you say “safe”, what do you mean?

I thought that the data-explorer plugin is inherently safe - it only has read-only access to the database, and there are timeouts in place to prevent performance issues.

Or are you simply talking about a query being working/not working?

Working/not working I guess. There is is also the question of if it works as described.

But yeah, I guess a category on meta can work as the source of truth, would make it very easy to add queries and discuss them.

We would have to be super careful with naming conventions and kicking out dupes.

6 Likes

Data explorer now includes a bunch of built-in queries, and we can add more via PRs to the data-explorer repo, so this is taken care of :smiley:

1 Like

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.