I made a multiselect dropdown filter on my app and I want my table to filter the data based on that filter.
The dropdown filter consists of many lines of item (extra services in this case), and the Extra Service field is a string with multiple items on every entry.
I initially used this code but it doesn't work as intended since the positioning of these items is random, hence showing no entry because there is no entry with the exact value as what my query compared to.
WHERE `Extra Service` ILIKE (CASE WHEN {{multiselect1.value.map(value => `${value}`).join(", ")}} is null THEN '%%' ELSE {{'%' +multiselect1.value.map(value => `${value}`).join(", ")+ '%'}} END)
Hence after searching alternatives, I found that SIMILAR TO might be the better syntax instead since there might several items to be filtered from. However, I've encountered this error and haven't found a workaround for this. Kindly see my query below:
SELECT * FROM {{Raw.data}}
WHERE `Extra Service` SIMILAR TO (CASE WHEN {{multiselect1.value.length}} == 0 THEN '%%' ELSE {{'%(' +multiselect1.value.map(value => `${value}`).join("|")+ ')%'}} END)
Here is the error message I received:
Expecting 'EOF', 'WITH', 'RPAR', 'IN', 'LIKE', 'ARROW', 'DOT', 'ORDER', 'CARET', 'EQ', 'SLASH', 'EXCLAMATION', 'MODULO', 'GT', 'LT', 'GTGT', 'LTLT', 'NOT', 'UNION', 'INTERSECT', 'EXCEPT', 'AND', 'OR', 'PLUS', 'STAR', 'GROUP', 'LIMIT', 'OFFSET', 'END', 'ELSE', 'REGEXP', 'TILDA', 'GLOB', 'NOT_LIKE', 'BARBAR', 'MINUS', 'AMPERSAND', 'BAR', 'GE', 'LE', 'EQEQ', 'EQEQEQ', 'NE', 'NEEQEQ', 'NEEQEQEQ', 'BETWEEN', 'NOT_BETWEEN', 'IS', 'DOUBLECOLON', 'GO', 'SEMICOLON', got 'LITERAL'"
Can someone tell me what did I miss on this? Thanks in advance.
