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. 
Something like the existing user_id selectable parameter but for groups. Even better if it could accept multiple values. 
2 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.
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.
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:
6 Likes
This looks great. 
I’m already looking forward to putting this into action. 
2 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.
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. 
I’d rather have the error than a random selection from the list
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. No idea where I’ve got that idea from. I could’ve sworn I used it just the other day. 
1 Like
BTW, I completely neglected to say:
This is fabulous!!! 
Thank you!!! 
2 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