Hi Retool team,
I have a text input component for users to enter ids that are separated by commas and the ids are used as a filtering condition in the query. For example, the user may enter 123, 456 while the text component value becomes "123, 456".
I have tried the method = ANY({{ IDsFilter.value.split(', ').map(i => parseInt(i)) }} but it doesn't work. I am using Snowflake db.
Thank you
Hi @doris.l,
You might want to be using:
WHERE SEQUENCE_ID IN FLATTEN({{ IDsFilter.value.split(', ').map(i => parseInt(i)) }}). This should look like "...IN FLATTEN(123, 456)" for it to make it work.
I work with BigQuery mostly so had to check the "UNNEST" equivalent in Snowflake. Not sure if that works but what you want to aim is having this look in your 'WHERE' statement: WHERE id IN (123, 456, 78, 90). I would use a transformer for this, returning an array of those ids. Then in your snowflake query, I would FLATTEN(transformer.value).
SQL can filter using an array like condition. if you want to filter out, just add NOT before IN.
Let me know if this works.
Hello @jocen,
Thank you so much for the feedback. Using a transformer is good advice because I need to use the flatten ID in more than one place.
I could flatten the ids string into the individual numbers using SPLIT_TO_TABLE function. In the screenshot example, the transformer outputted two ID values. However, when I used the transformer output {{IDs.data.VALUE}} in the WHERE clause in another query, it only returned one ID's data. Not sure why it happens.
Do you mind sharing some of your wisdom here? Thank you.
Hi @doris.l
Does this formatting from our docs work for you?
WHERE array_contains(SEQUENCE_ID, array_construct( {{IDs.data.VALUE}}) )
Hi @Tess,
Thank you for the help! Unfortunately, the method didn't work. No data is returned at the end.
Do you have any other suggestions on this? Thank you
Hi @doris.l
How about this formatting?
It sounds like you'd replace multiselect1.value with IDs.data.VALUE and NAME with SEQUENCE_ID
Hello @Tess,
Thank you so much for getting back to me!
Not sure why the solution is not working for my case. But based on your suggestion and the documentation you shared earlier, I found the below solution works.
select
*
from
table
where
{{ textinput.value }} like '%' || table.id || '%'