How to use the multiselect component

💬 App Building
1

I've been using single select components so far with no problem, but now I'm trying to use a multiselect I run into trouble.

I've gone back to trying a simple example with the syntax I've seen in a few other posts...

SELECT Item_Id FROM tbl_Items WHERE Item_Id = ANY ({{ multiselect1.value }});

The code window shows me the current value of the multiselect as...

Screenshot 2025-03-12 235239

But when I Preview the code I get...

"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '24, 25, 23, 594 )' at line 2"

I see that "ANY" is mainly used with the results of a subquery, but I haven't found a way to make it accept the result of the multiselect.value.

Is there a "correct" syntax I should use?

2

I think you can use STRING_SPLIT({{mulitselect1.value.toString()}}, ',').

SELECT Item_Id FROM tbl_Items WHERE Item_Id = ANY (STRING_SPLIT({{mulitselect1.value.toString()}}, ','));

4

Thanks for the suggestion but it doesn't seem to accept STRING_SPLIT.

  • message:"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'STRING_SPLIT ( '594,25,24,23' , ',' ) )' at line 5"
5

I have made some progress...

To start with it seems my mariadb doesn't support the "= ANY" comparison, so I switched to "IN" and that works fine for manual queries where I log into my server directly (ie outside Retool)...

SELECT Item_Id FROM tbl_Items WHERE Item_Id IN (24,23,25,594);

Gives...
+---------+
| Item_Id |
+---------+
| 23 |
| 24 |
| 25 |
| 594 |
+---------+

In Retool I can convert the multiselect array to a string...

Screenshot 2025-03-13 123329

And the code window info shows the string (with double quotes around it).

But the query then only finds a match for the first key number in the string (24).

Screenshot 2025-03-13 123618

So I wondered - what is being sent in the query?

I activated logging on my mariadb server and find the query being sent is...

SELECT Item_Id FROM tbl_Items
WHERE Item_Id
IN
(
'24,23,594,25'
)

I checked using that query manually direct to mariadb and get the same result - just one match of 24.

So it seems like the single quotes Retool is putting around the string is spoiling the query.

So far I haven't found an easy way to get rid of them...

6

Just in case anyone thinks of it

I tried to remove the quotes using slice() ...

Screenshot 2025-03-13 125005

But as you see it just removes the first and last digit before adding quotes.

At the server I get...

SELECT Item_Id FROM tbl_Items
WHERE Item_Id
IN
(
'4,23,594,2'
)

7

I don’t know MariaDB well, but did you try IN with the STRING_SPLIT code? For IN, you need to pass a string of strings (‘1’,’2’,’3’) not a string made of strings (‘1,2,3’).

8

Yes, that was the first suggestion I received, but mariadb doesn't support STRING_SPLIT.

9

Finally I "stumbled" across a solution...

I started trying some more of the methods available on multiselect1.value...

At first "flat()" didn't seem to make much difference - the code window still shows it as an the same array...

Screenshot 2025-03-13 134808

But the query sent to the server is fine...

SELECT Item_Id FROM tbl_Items
WHERE Item_Id
IN
(
594, 23, 24, 25
)

So that (.flat()) works for me with mariadb - I don't know how it is for the other variants of databases.

What do others use for multiselect?

10

How about

{{ multisect.value.map(item => `"${item}"`).join(',') }}
11

Thanks, I'll check that out, but I think {{ multiselect1.value.flat() }} is a bit simpler (see my previous post)

1 Like
12

I tried out your suggestion alongside my solution

At the sever I get...

      # OR tI.Item_Id IN ({{ (multiselect1.value.flat()) }})
       OR tI.Item_Id IN ( 774, 782 )

     # OR tI.item_Id IN ({{ multiselect1.value.map(item => `"${item}"`).join(',') }})
       OR tI.item_Id IN ( '\"774\",\"782\"' )

Looking carefully you can see that the "map join" version ends up with single quotes around the whole string.

So I'll stick to the one that works - multiselect1.value.flat()

13

Now that I have multiselect working I have found a small "snag".

I have a default value set, but since all selected items have a small "x" next to them to allow removal, it is now possible to de-select all items so the multiselect is completely empty.

Then the query is sent with an empty "IN () " which causes an error response.

14

You should be able to set the Disable Query (only run when)condition in the advanced tab of the query with something like "don't run if list is empty". You can use similar logic to disable whatever triggers the query as well.

15

Depending on what you are getting on the server you can try:

{{
  multiselect1.value.flat() == undefined
  // or multiselect1.value.flat().length == 0 if the return is an empty array []
    ? '0'                         // whatever you want your fallback to be
    :  multiselect1.value.flat()  //otherwise just give the values
}}

To your earlier question, using Postgres, the syntax is = ANY( {{ array }} ) with no need to fiddle with the array. Like you, I don't understand why .flat() on a flat array makes any difference...

p.s. @pyrrho's approach is also good if you just don't want it to fire the query

1 Like
16

Thanks!

I settled on...

{{ multiselect1.value.length ? multiselect1.value.flat() : '0' }}

...which works fine!

1 Like
17

Interestingly, in a similar situation, where I just changed a table from single selected row to multiple selected rows, the basic array from selectedRowKeys...

WHERE tPbsub.Package_booking_Id IN ({{ table19.selectedRowKeys }})

...works fine and at the server I get the keys with no single quotes around them.

WHERE tPbsub.Package_booking_Id IN ( 35, 37, 38 )

So in that case ".flat()" was not needed.