Passing Parameters via additionalScope for SQL Queries

💬 Queries and Resources
1

I have a query where I prepare parameters to be passed via additionalScope for another query. These parameters are essentially a list of IDs that I want to use as a filter in the WHERE clause of the second query using IN.

The problem arises because the additionalScope is always passed as a string, which causes issues when trying to use it in the SQL query. Specifically, the string format causes syntax errors when the query is executed.

For example, I pass a list of IDs like "(1,2,3,4)" as a string, but when used in the IN clause, it leads to errors since the values are not correctly formatted for SQL.

Error: invalid input syntax for type integer: "(1,2,3,4,5,6,7,8)"

Goal: I need to pass these parameters in a way that they can be correctly interpreted by SQL as a list of integers, allowing the query to function without syntax errors.

Query sample:
select * from custos where id_centro_de_custo in {{custos}}

1 Like
2

Hey there @Felipe_R and welcome to the forum!

So, you need to pass the additional scope as an arrays of integers, something like [1,2,3,4,5] (this should be quite easy to achieve with JS, but let me know if you need guidance on this), and then in your second query change your syntax to WHERE id_centro_de_custo = any ({{custos}})

With regards to this syntax, there is something related to prepared statements that makes IN not work as usual, but to be honest I've never quite grasped why

3

Hey @Felipe_R

You may prefer to use this Retool team forum for exploring additional scope.

3 Likes
4

Hey @Felipe_R

You can also use this kind of the process for the use of additional scope :

First create the js for it
additionalScope: {
ids : "pass array of the ids"
}
use the promise method for it

and after that use that ids in sql
SELECT *
FROM custos
WHERE id_centro_de_custo = ANY ({{ids}});

4 Likes
5

Thanks a lot Miguel,

However I tried a simple test runnig the following query and it didn't work.

Select: SELECT * FROM custos WHERE centro_de_custo_id = ANY ([1,2,3])

Error: Consultas failed (0.417s):syntax error at or near "["

There's a weird behavior in the execution order of the queries I don't quite understand though!

6

Hey @Felipe_R,

It won't work if you hard code it. See example below:

image
image844×286 35.2 KB

I'm referring to a multiselect component which has [7,8] as value. However, the query, for some reason unknown to me, runs as any (7,8), removing the curly brackets and returning my result.

However if I hard code it to = any ([7,8]) I get the same error you mention.

7

If you use SELECT * FROM custos WHERE centro_de_custo_id = ANY ( ARRAY[1,2,3] ) it will work. The syntax is checking for the existence of the value in an array, and in Postgres, you can't just pass a string, you need to wrap it in ARRAY for it to be treated as such.

2 Likes
8

This cheatsheet might be helpful as well :slightly_smiling_face:

CleanShot 2025-01-22 at 11.52.37@2x
CleanShot 2025-01-22 at 11.52.37@2x1956×674 70.7 KB

CleanShot 2025-01-22 at 11.53.25@2x
CleanShot 2025-01-22 at 11.53.25@2x1594×1026 112 KB

1 Like
9

:wave: just checking in, @Felipe_R :slightly_smiling_face: Did you sort this out?

10

As an alternative solution as I had this in a workflow when interacting with an MSSQL database.

Declare and set the string array at the start of the query. Then do a string split and cast to get it into a number array where you need it.

image

11

Hi everyone,

Thanks a lot for the tips. I ended up moving jobs so I could not finish testing the application although I'm starting a new one now and I intend to use Retool here, I'll come back to it and let you guys now how (hopefully) I sorted this out :smile:

2 Likes