Param dropdown for group_id in data explorer query

[ganncamp (G Ann Campbell)]

I’d love to be able to specify something like this:

-- [params]
-- groups.id :group_id

to get a parameter input that’s a dropdown of groups. I’ve named the value group_id here because I assume that’s what such a dropdown would give me.

[JammyDodger]

I think this would be really useful too. Something like the existing user_id selectable parameter but for groups. Even better if it could accept multiple values.

[ganncamp (G Ann Campbell)]

@JammyDodger

the existing user_id selectable parameter

Wait, what?

Can you point me to where it shows how to use that?

[JammyDodger]

You can use something like:

-- [params]
-- user_id :user

SELECT *
FROM posts
WHERE user_id =:user
LIMIT 5

And that will bring up a dropdown where you can autocomplete in a user.

However, I have just noticed that the first time you run it at the moment it errors and doesn’t bring up the parameter box, but does if you refresh the page. I shall see if I can get someone to check that out.

[pmusaraj (Penar Musaraj)]

Ask (and wait) and you shall receive… we just added support for a :group_list parameter in this PR.

Here is an example of the feature in use:

-- [params]
-- group_list :groups

SELECT g.id,g.name 
FROM groups g 
WHERE g.name IN (:groups) 
ORDER BY g.name ASC

That should give you the following in the UI:

[JammyDodger]

This looks great. I’m already looking forward to putting this into action.

[ganncamp (G Ann Campbell)]

My topic got closed before I could reply to it…

Is there a way to limit the new -- group_list parameter type to a single group? I’ve subbed it into our existing reports, but they’re all written expecting a simgle group name and in testing I found myself flubbing it fairly frequently.

[JammyDodger]

I’ve reopened and slipped it over.

Though I’m not sure what you mean? Can you give an example of what the issue is?

[ganncamp (G Ann Campbell)]

I mean that I run the query for my first group. Then when I want to switch groups, I add a group instead → error

[JammyDodger]

Oooh. That’s an interesting one. I’ve been using it more like:

WHERE g.name IN (:groups)

Hiding a little bit of Jammy Stupid

WHERE g.id IN (:groups)

Though this would still allow you to accidentally put more than one in even though you’d avoid the error.

[ganncamp (G Ann Campbell)]

I’d rather have the error than a random selection from the list

[JammyDodger]

To be honest, I’ve just checked that and I think I must have dreamt using it for group_ids as it does not like that at all. I shall strike that from the record. No idea where I’ve got that idea from. I could’ve sworn I used it just the other day.

[ganncamp (G Ann Campbell)]

BTW, I completely neglected to say:

This is fabulous!!! Thank you!!!

[pmusaraj (Penar Musaraj)]

@ganncamp

Is there a way to limit the new -- group_list parameter type to a single group?

No, there’s no way to limit it to a single group. Not that I know of.

The parameter from group_list will be an array if there is more than 1 item selected and a string if only 1 item is selected.

So when there is just one item, you can do this:

-- [params]
-- group_list :groups

SELECT g.id,g.name 
FROM groups g 
WHERE g.name = :groups 
ORDER BY g.name ASC

But since it’s an array, this below will work for both a single item and multiples:

-- [params]
-- group_list :groups

SELECT g.id,g.name 
FROM groups g 
WHERE g.name IN (:groups)
ORDER BY g.name ASC

But there is no way to tell the UI “only accept 1 item as the selection”.

[nat (Natalie T)]

@saradev’s docs here Using Parameters in Data Explorer Queries highlights the full list of params for data explorer that sometimes have a dropdown, and sometimes do not. (Parameter list on Github)

Here’s a table of params, also indicating if it has a specific control for it.

param	dropdown available
int
bigint
boolean
null boolean
string
date
time
datetime
double
user_id
post_id
topic_id
string_list
category_id
group_id
int_list
string_list
user_list
group_list

Ideally the dropdowns used and shown in data explorer would have similar look and feel as the core app. Let us figure out how we can do this.

[Lhc_fl (Linca)]

We recently redesigned the entire param input box. Most types have their own drop-down boxes and support validation with semantic error prompts now.

Here’s the latest table of params indicating if it has a specific control for it.

param	dropdown available
int
bigint
boolean
null boolean
string
date
time
datetime
double
user_id
post_id
topic_id
category_id
group_id
int_list
string_list
user_list
group_list

@ganncamp You might want to know about this change

[jordan-violet]

Can you explain how these dropdowns work? For the life of me I have not been able to get a dropdown where I can define a list of strings available in that dropdown.

[nat (Natalie T)]

@jordan-violet a late response, but @SaraDev did a writeup here on available parameters, and the OP had been updated by Linca:

Using Parameters in Data Explorer Queries

Parameters are a powerful tool that can be used in Data Explorer queries on Discourse. Parameters allow for more dynamic and customizable queries, and instead of hard-coding values in your queries, you can declare variables that will prompt for input when the query is run. Declaring a Parameter To declare a parameter, you can use the following syntax: -- [params] -- int :parameter_name = 10 The parameter’s section of the query will always start with -- [params], followed by each type of par…