SQL WHERE condition for when multiselect is empty or has value(s)

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.

1 Like

Instead of !multiselect1.value have you tried multiselect1.value.length === 0 ?
I think the component always returns a value, such as an empty array, so you would want to check if the array is populated or not.

2 Likes

Thanks Dave!! Second time you have helped me. Very much appreciated. :clap:

Using the standard table products, and unit_price_cents column as the value of the multiselect.

The syntax is;

select 
  * 
from 
  products
WHERE 
  ( ( {{ multiselect1.value.length === 0}} ) OR unit_price_cents = ANY( {{ multiselect1.value}} ) )
1 Like