MultiSelect in SQL query is not working

Queries and Resources
,
1

  • Goal:

  • I am trying to create a filtering mechanism linking the state of the MultiSelect to a SQL Query that is sent to a PostgreSQL database.

  • Steps:

  • I've tried to parse the multiSelect.value in several ways: transforming it to a string and parsing the array column into a string as well, but didn't work because of the same error.

  • Details:

  • AND ( {{ _.isEmpty(contentTypeSelect.value) }} OR "content_types_list" = ANY ({{ contentTypeSelect.value }}) );

Here is the error:
column "review_summary" does not exist

"review_summary" is actually one of the string values within the array's column, I don't know why it is interpreting it like it was a column

  • Screenshots:

Here is how it looks like the column's value I'm trying to work with the multiselect filtering
image

2

Hey @Daniel_Pereira_de_Souza! It looks like your current approach isn’t working as expected because it’s adding "" around the string. We just need to adjust the logic a bit and handle it within SQL instead.

Here is an example:

AND (
    COALESCE({{ search.value }}, '') = ''  -- Case for empty search
    OR (
        field_to_search ILIKE {{ search.value }} || '%' 
        OR field_to_search ILIKE {{ search.value.slice(1).toUpperCase() }} || '%'
    )  -- Non-empty search
)

Let me know if you have any questions about this or need further clarification! :blush:

3

Thanks for your reply, @Derek_Watts!

Unfortunately, this didn't work. It gave me actually the same error column "review_summary" does not exist.

I'm not sure why this happens. The array of strings seems to be properly identified on the SQL Query:

image

But any operation I do on this array of strings gets the first string as it would be a column value (surrounded by double quotes).

Is there any best practice to handle such array operations with dynamic values injected into the query? I've got this one using ANY from another topic, but the solution is not working for me.

4

Here is a screenshot of the error:

image

And I've tried the solution this way:

AND (
      COALESCE({{ contentTypeSelect.value }}, '') = '' OR (
    content_types_list ILIKE {{ contentTypeSelect.value }} || '%' OR content_types_list ILIKE {{ contentTypeSelect.value.slice(1).toUpperCase() }} || '%'
      )
  )
5

@Daniel_Pereira_de_Souza Maybe I'm misunderstanding what you're trying to accomplish. Is your goal to use the array as the value for the search?

If that's the case, you should convert the array into a comma-delimited string and use it properly within your query.

For example:

SELECT *
FROM your_table
WHERE column_name IN ('value1', 'value2');

To convert the array into a string like "value1,value2".

You should ensure that the array elements are wrapped in single quotes (as required in SQL IN clauses). For example:

{{ yourArray.map(value => `'${value}'`).join(',') }}

This would convert the array into a properly formatted string for the SQL IN clause, like:

SELECT *
FROM your_table
WHERE column_name IN ('review_summary', 'reading_age');
6

Hey @Daniel_Pereira_de_Souza,

your Screenshot looks like the "content_types_list" column is not a TEXT column, but a JSON column that contains array of strings. Is that correct?

In that case you should be able to use

SELECT * 
FROM my_table
WHERE (
  {{ multiSelect.value.length === 0 }} 
  OR EXISTS (
    SELECT 1
    FROM jsonb_array_elements_text(content_types_list) AS element
    WHERE element = ANY ({{ multiSelect.value }})
  )
);

to return each row where "content_types_list" overlaps with "multiSelect.value ".

1 Like