I am trying to create a filter (where condition using SQL) based on a multiselect.
I want the results to be everything where nothing is selected in the multiselect, and to filter for all values when options are selected.
For example if a table has 10 items, 3 are type A, and 2 type B. Then when no type is selected, 10 results, when only A is selected 3 results, when A & B are selected 5 results.
From the SQL Cheatsheet
Using this syntax using ANY, I can successfully get the single or multiple items filtering correctly.
select
*
from
users
where id = ANY({{ {{ multiselect1.value }} }})
When I try to add the option (I have successfully used with single item components like selectlist or radio group) to display all when nothing is selected it instead shows nothing (the count is zero).
select
*
from
users
where
( {{ !multiselect1.value }} OR users.status = ANY( {{ multiselect1.value }} ))
I feel the problem is with {{ !multiselect1.value }}, and that as the value is an empty array, the syntax doesn't work, but not sure what to replace it with, or if I'm wrong.
Very grateful for any help.
Thanks,
Jon.