Param dropdown for group_id in data explorer query

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.

4 Likes

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

4 Likes

Wait, what?

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

1 Like

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. :+1:

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. :slight_smile:

4 Likes

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:

8 Likes

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

3 Likes

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.

I’ve reopened and slipped it over. :+1:

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

1 Like

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

1 Like

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. :thinking:

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

1 Like

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. :slight_smile: No idea where I’ve got that idea from. I could’ve sworn I used it just the other day. :derp:

1 Like

BTW, I completely neglected to say:

This is fabulous!!! :confetti_ball: Thank you!!! :tada:

3 Likes

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”.

1 Like

@saradev’s docs here Utilizing 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 :white_check_mark:
bigint :white_check_mark:
boolean :white_check_mark:
null boolean :white_check_mark:
string :white_check_mark:
date
time
datetime
double :white_check_mark:
user_id :white_check_mark:
post_id
topic_id
string_list
category_id
group_id
int_list
string_list
user_list :white_check_mark:
group_list :white_check_mark:

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.

6 Likes

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


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

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

@ganncamp You might want to know about this change :wink:

9 Likes