How do I dynamically filter by multiple dropdown boxs - WILL THIS WORK?

Queries and Resources
, ,
1

Dynamic filtering is a must have feature in an app that comes to replace our company use in Google sheets.

A user can select values from multiple multi select and when he clicks on 'Apply' button the following PostgreSQL query gets triggered:

SELECT *
FROM ports
WHERE 
    (
        {{ !portsPhoneNumberInput.value }} OR phone_number ILIKE {{ '%' + portsPhoneNumberInput.value + '%' }}
    )  

   AND ( carrier_id = ANY( {{ portsCarriersSelect.value.length === 0 ? portsCarriersSelect.data.map(val => val.id).concat([null]) : portsCarriersSelect.value}})
   )

   AND... (more multi selects)
;

So, if the user didn't select any value, I will pass all the possible values to the ANY statement. that would work, except that we also have NULL values in there, and ANY cannot valuate NULL therefor the result is not as expected. (PostgreSQL calculate NULL as UNKNOWN while ANY only returns TRUE or FALSE)

One thing we can do is to eliminate NULL values by having a DEFAULT set to an id that is treated like null - that would work but may cause bugs in the future if we forget about it, and in general it's not good practice.

Do you have a better solution?
Any help would be much appreciated!

P.S. while writing this I started wondering if !portsCarriersSelect.value can replace portsCarriersSelect.value.length === 0.

2

Writing it actually made me think about it again and I was able to solve it.
It's funny that I didn't thought of it before as I already used it this SQL statement :innocent:.

What I needed to do is to add this to the statement:
{{ portsCarriersSelect.value.length === 0 }} OR

Basically checking if user selected something and if not, returning TRUE for each row.

I've been searching for a way to filter with multiple select boxs for quite some time, and no seems to write anything clear about it, so I'll post a guide in the 'tips and tricks' category whenever I have some free time (Unless any of you beat me to it, and please do :writing_hand:)

Here's the complete working query:

SELECT *
FROM ports
WHERE
      (  
        {{ !portsPhoneNumberInput.value }}
        OR phone_number ILIKE {{ '%' + portsPhoneNumberInput.value + '%' }}
      ) 

  AND (
        {{ portsUsersSelect.value.length === 0}} 
        OR assigned_to_user_id = ANY({{portsUsersSelect.value.length === 0 ? portsUsersSelect.data.map(val => val.id) : portsUsersSelect.value}})
      )
      
  AND (
        {{ portsCarriersSelect.value.length === 0}} 
        OR last_carrier_id = ANY({{portsCarriersSelect.value.length === 0 ? portsCarriersSelect.data.map(val => val.id) : portsCarriersSelect.value}})
      )
      
  AND (
        {{ portsCustomersSelect.value.length === 0}} 
        OR assigned_use_to = ANY({{portsCustomersSelect.value.length === 0 ? portsCustomersSelect.data.map(val => val.names) : portsCustomersSelect.value}})
      );
3 Likes