Using an array in query

How do I do X?
1

Hello, i have a query where i want to filter the where clause based on a checkbox. To transform the array to a readable list in the query i use the selectedValues.join(', ') method. For one value this works, but as soon as i check multiple values this fails.

There are a lot of values to be checked, and they change a lot, so the solution provided here:

does not work for me here.

The datasource is a MS sqlserver

Can anybody help me out?

SELECT
locatie,
“sdf potmaat”,
teelt,
uitgangsmateriaal,
leverancier

FROM stamgegevens.producten

WHERE locatie IN ({{CheckboxGroup1.selectedValues.toString()}});

2

@Wobbel Do you have Prepared Statements turned ON? If yes, can you try turning it off and see if the query works?

3

I have tried it, and the query then stops working. I get the following error:
Invalid column name ‘’

which is weird, because it is in the where clause of the query and not the select.

The query evaluated as followed:

SELECT
teelt_id,
locatie,
“sdf potmaat”,
teelt,
uitgangsmateriaal,
leverancier

FROM stamgegevens.producten

WHERE locatie IN (S4);

I think is should cast the resulting value to string, i have tried the String() function and the to_String() method, but it doesnt work.

4

@Wobbel Could you also try the below code

WHERE locatie IN {{"'" + CheckboxGroup1.selectedValues.join("', '") + "'"}}

5

After tinkering with it for a little, the following solution works,

WHERE locatie IN ({{“'” + CheckboxGroup1.selectedValues.join(“', '”) + “'”}});

Thanks a lot for pointing me in the right direction!

1 Like
6

I tried this in a MS SQL and it does not work when Prepared Statement is enabled. No error messages, just no results are returned.

7

Can you share a screenshot of the query?

8

Here is the query:

Query
Query976×390 11.1 KB

If I disable ‘Use Prepared Statement’, I get the results I expect.

But when ‘Use Prepared Statement’ is enabled, here is what happens:
Query Response

There are no errors, just an empty response.

9

This is a known bug. The IN and ANY clauses do not work with Use Prepared Statement turned ON. You can track this bug here: [Bug]-[11200]:Unable to use IN or ANY clause with prepared statements turned on for MySQL · Issue #11259 · appsmithorg/appsmith · GitHub
In the meantime, the workaround is to turn Use Prepared Statement turned OFF.

1 Like