Apply multi-select filter within an SQL WHERE clause using IN

Apps (Frontend)
1

Dear app builders,

I want to use a Multi-Select Filter within an SQL WHERE clause using IN. If the filter contains values, it’s all fine. However, if no value is selected in the filter, the transformation of {{filters.myFilter.id.values | list?separator=}} is completely empty (not NULL, not empty string etc. as far as I can see in the console) which causes an ugly SQL error in the console because it seems like an argument is missing.

Example: SELECT * FROM inputTable WHERE id IN ({{filters.myFilter.id.values | list?separator=}})

I tried to put '' around the filter like '{{filters.myFilter.id.values | list?separator=}}' but this leads to an error if several values are selected in the filter, the SQL clause thinks we have one string and not several values.

This should be a very common use case so I hope there is a solution! :slight_smile:

1 Like
2

I had this problem in the past and used a workaround with regex:

SELECT 
    * 
FROM inputTable 
WHERE 
   # Check whether any filter is selected
   ({{filters.myFilter.id.values | length}} = 0)
   # Regex match (concatenated with |) for the id
OR id ~ replace(
        '^{{filters.myFilter.id.values | list?quote=NOQUOTE&separator=|}}$',
        'NOQUOTE',
        ''
    )

I am somehow sure there is a more elegant way :wink:

3

Thanks!! The solution posted by @katharina.selig is applicable to PostgreSQL.

That solution works for SparkSQL :slight_smile:

SELECT 
    * 
FROM inputTable 
WHERE
     (
        id RLIKE replace(
          '^{{filters.myFilter.id.values | list?quote=NOQUOTE&separator=|}}$',
          'NOQUOTE',
          ''
        )
      )

Note: The NOQUOTE property is necessary because if leaving the quote empty or a dash, it has an influence on the regex.