Hey,
I want to create a MultiSelect Dropdown Menu that filters a table in Retool. I want to filter the Postgres Query witht the Where column IN ({{...}}) statement. Unfortunately the Dropdown (which is also loaded by a database column) returns the array values in the array format with double quotes ["a", "b", "c"] but it seems that postgres has a problem with that and expect a single quote array ['a', 'b', 'c']. Can you help with that ?
Thank you
Hi! Resurrecting this because I think I'm having a similar issue. I managed to get single quotes around all my array items, except the whole array is enclosed in double quotes. Is there any way to avoid this? @Justin, to answer the question about postgres error, it actually doesn't error for me, because it's searching (I think?) for a big ole string in the WHERE clause.
I've tried a couple of different methods, passing an array, passing a joined string, with and without parenthesis, etc. I'm guessing I must be making a fundamental mistake cause somewhere..
Edit: solved it by disabling prepared statements.
Opening this again - is there any way to use prepared statements, but have retools interpolations return single quoted strings? Could this be a feature request? I'd like to be able to search by string literals in postgres, but also have some basic protection against SQL injection, which I think can be possible?
Resurrecting this because I'm having a similar issue. The docs @everett_smith linked are helpful and I did give it a shot, but seem to be optimized for numeric types.
In its basic form, I'm attempting a filter based on a MultiSelect. User selects one or many state abbreviations from a list of 50 states. This queries on a column containing state abbreviations. Like Mark, in effect it seems as though this
SELECT DISTINCT party from main where reg_state IN ({{multiselect1.value.map(d => `'${d}'`).join(',')}})
is interpreted as:
SELECT DISTINCT party from main where reg_state IN ("'MI','OH','IN'")
So the JS is creating a single double quoted string comprised of single quote encapsulated state abbreviations.
Any suggestions for getting those double quotes to drop out in the returned JS?
(postgres into Redshift)
Hey @redfiel3, Does SELECT DISTINCT party from main where reg_state = ANY ({{multiselect1.value}}) work on your end?
\
Ahhh it does. Thanks so much.
You bet!
Hi there, I'm having a similar problem.
Trying to implement server-side pagination with PostgreSQL, cannot find a way to implement ordering ASC|DESC, the query fails since asc/desc is getting quoted.
Please help?
Hey @Jose_Luis_Pereira! Hopping in here to try helping, thank you for waiting for quite some time.
This looks like a prepared statements issue, since ASC or DESC are reserved words in SQL. Anytime you try to make a column name or reserved word dynamic, you’ll need to disable prepared statements on your resource.
https://docs.retool.com/docs/sql-queries#security
You could also probably write two separate queries (one for ASC and one for DESC) and then write a JS query to conditionally trigger the one you want.
Let me know if you have any questions about this!