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.
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.
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.
Wait, what?
Can you point me to where it shows how to use that?
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.
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:
This looks great. I’m already looking forward to putting this into action.
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.
Though I’m not sure what you mean? Can you give an example of what the issue is?
I mean that I run the query for my first group. Then when I want to switch groups, I add a group instead → error
Oooh. That’s an interesting one. I’ve been using it more like:
WHERE g.name IN (:groups)
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.
I’d rather have the error than a random selection from the list
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.
BTW, I completely neglected to say:
This is fabulous!!! Thank you!!!
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”.
@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 | |
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.
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