How to multiselect component on table columns with NULL?

Queries and Resources
1

I use multicolumn for ENUM type [‘pending’,‘partially_fulfilled’,‘fulfilled’,‘returned’,‘exchanged’] and query like this:

WHERE  
  (orders.status = ANY ({{ order_status_multi.value }}))

Now the problem is that the order.status column in DB table can contain also NULL values and this causes that when I do not select any of the values [‘pending’,‘partially_fulfilled’,‘fulfilled’,‘returned’,‘exchanged’] in the multi-select box the query returns no rows.

I would probably do this with changing the values to

['pending','partially_fulfilled','fulfilled','returned','exchanged','null']

and visible values like this:

['Pending','Partially Fulfilled','Fulfilled','Returned','Exchanged','Undefined']

then I would like to somehow map the ‘Undefined’ in the query to IS NULL for that column
to get a query like this:

WHERE
(orders.status = IS NULL OR orders.status = ANY ({{ order_status_multi.value }}))

How to achieve this?

1 Like
2

If null was an option in the array order_status_multi.value (multiselect component?), then choosing null/‘Undefined’ there would also return rows where order.status = null in addition to any of the selected options.

If you want to return rows where order.status = null if no values are selected in the multiselect, you could do something like this:

WHERE (orders.status = ANY ({{ order_status_multi.value.length == 0? [null] : orders_status_multi.value }}))

Does that help here? It might also be worth noting that multiselect1.value is an array of the selected values, and multiselect1.displayValue is an array of the selected display values

3

I had a similar problem what I did was:

    SELECT
      Shop,
      Month,
      SUM(ProductA) as Units_ProductA,
    FROM
      {{ rawQuery.data }}
    WHERE
      1
      AND Shop = CASE WHEN {{ shopFilter.value }} <> '_default_' THEN {{ shopFilter.value }} ELSE Shop END
    GROUP BY
      Shop,
      Month

Then on my “shopFilter” I setup the initial value as _default_. So if the user doesn’t select the shop the query would be:

SELECT
  Shop,
  Month,
  SUM(ProductA) as Units_ProductA,
FROM
  {{ rawQuery.data }}
WHERE
  1
  AND Shop = Shop
GROUP BY
  Shop,
  Month

If the user selected a shop then the query would be like:

SELECT
  Shop,
  Month,
  SUM(ProductA) as Units_ProductA,
FROM
  {{ rawQuery.data }}
WHERE
  1
  AND Shop = {{ shopFilter.value }}
GROUP BY
  Shop,
  Month
1 Like
4

@alex-w. Unfortunately, this solution doesn’t work because = ANY (NULL) does not select the fields with NULL

5

@Alejandro thank you for the tip unfortunately this is only suitable for dropdown with single select. I use multiselect dropdown.

6

Is the null value in your multiselect null or 'null'? If it is the value is null and orders.status = null, then orders.status = ANY ( {{ [null] }} ) should work. That is for postgres at least, an array inside of ANY ( )

I think you could use a statement like this to help manage the null scenario otherwise: https://docs.retool.com/docs/sql-query-faq#how-do-i-show-all-my-data-when-a-filter-is-not-in-use

7

@alex-w I have no null value in the multiselect:

['pending','partially_fulfilled','fulfilled','returned','exchanged']

If I add null to array is it possible to check against length like you mentioned?

order_status_multi.value.length == 0

8

Hi everyone,
I checked all the documentation on this but I have the exact same problem and wanted to check if there was any solution in the meantime.

My select field works with the following integration in my postresql code:
WHERE ({{! select1.value }} OR "Location"."internalName" = {{ select1.value }})

Somehow, a multiselect field, within the same query won´t work with the following code:
WHERE ({{! multiselect1.value }} OR "Location"."internalName" = ANY({{ multiselect1.value }})

I get that the multiselect1.value is an array other than the select1.value that is only one value. I think that is why the ! solution is not working with the multiselect1.value. I tried .length = 0 and other workarounds but none of them worked. Like ckissi I want to see all the data when multiselect1 is not used and filter for selected locations when it´s used...

9

I found the solution:

WHERE ({{!! multiselect1.value.length == 0 }} OR "Location"."internalName" = ANY ({{ multiselect1.value }}))

1 Like