I am looking for a solution to do a SQL query (in SQL Database Connector) that contains a ‘IN’ operator whose input is a parameter that should be a list of text (for example ‘user1@gmail.com’,‘user2@gmail.com’). Basically, the structure of the request is like this:
SELECT user_id FROM user_list WHERE user_email IN ($1)
I have tried different formats to input the list of emails (with ', without ', etc.) but nothing has worked so far. Any ideas on how we would solve this?
@phuthi, I saw in one of your post dating back to 2017 that you were facing the same challenge at the time. Have you found a way to make it work since then?
Below a screenshot of the exact request that we are trying to make.
Thank you @joeyg for your response. I had already gone through this thread but I understand that it works for a list of numbers. Have you found a way to make it work for a list of texts as well?
Thanks again!
Yes, I’ve run into problems with lists of texts in the SQL connector. If memory serves, I think I got around it using SQL list handling functions - for example MYSQL has a function called FIND_IN_SET. Maybe whatever SQL DB you are using has something similar. Basically, if the result of the function is > 0, then you know the text is in the list of texts.
In Bubble, one of my MYSQL queries looks like this: SELECT FIND_IN_SET(?,?) AS Result limit 1;
I made both parameters (1 - the text I’m searching for and 2 - the list of texts) as type text in the SQL connector query. Maybe try playing around with that or something similar?
We just found a workaround with Postgres : we use the function regexp_split_to_table to split the big string (parameter $2 below) which include multiple mails separated by a comma ‘,’ and we manage to use this expression within a IN clause like this : WHERE user_app.mail IN (SELECT * FROM regexp_split_to_table($2, ‘[, ]’))
This expression enable us to split the big string $2 into a list / table of multiple string, each substring including 1 mail as they were all separated by a comma (,) within $2
