Getting IN() function to work from multiselect table with Azure/SQL server

Queries and Resources
1

Hi,

Can anyone tell me how to get IN() to work from a multiselect table? I assume it's something to do with the format of JSON object verses what the server is expecting?

I have the following query:

SELECT
 dbo.GetResourcesOnEvents.EventID, dbo.GetResourcesOnEvents.ResourceName
FROM
 dbo.GetResourcesOnEvents
WHERE
  dbo.GetResourcesOnEvents.EventID IN {{formatDataAsObject(SelectedEvents.selectedRow.data).EventID}};

And the multiselect table SelectedEvents returns an array of integers!

Retool in
Retool in1920×880 122 KB

Hovering on the green JS code is showing me the two EventIDs that I have selected in the table (top right of the screen).

But I get no results (nothing showing in the listbox (on the left - SelectedResources)

If I use = 7823 directly in the query - I get the desired results!

Retool results
Retool results1920×875 125 KB

See the list view showing Len and Kim.

Here is my data.

Retool SQL Server data
Retool SQL Server data3843×2062 385 KB

2

Hi @jclutterbuck, have you tried mapping it instead of using formatDataAsObject?

i.e:

{{SelectedEvents.selectedRow.data.map(x => x.EventID)}}

If that does not work, can you follow this tip from Justin/Brad: Using (IN) with a list of values from a Retool component or state

Lemme know how you go

Regards,

3

To select multiple rows from a table and pass it properly into a SQL statement you need to use string_split in addition to formatDataAsObject:

image
image801×656 35.3 KB

4

To reduce the amount of SQL every time you want to do an IN statement consider creating a table function on your DB like so
image

5

Hi @jocen

Thanks for your reply. I don;t want to take up your time, but if you had a couple of minutes to explain what that does I'd be grateful. I understand the map function but x => x.EventID?

(I did manage to get it to work with the answer below)

Thanks

6

Thanks @Nicolas

This works!

I'm so grateful. I'm still learning (aren't we all) and some of the SQL in your solution will take me a while to understand how it works. but for now I can continue with my project.

Thanks,

Jeremy

7

Glad it worked out for you, the code can be reduced to below:
image

The reason why I had the select * from () was because I simplified the trimming, convert and string_split into the function above so I can call it like:

business unit in (select * from dbo.f_array(########) )

@retool the JS script solution above was also provided to me when I was struggling with filtering. For heavy SQL only users like myself I feel that a new value can be added to components that would concatenate selected text into one value, separated out by a comma.

So in my example above I selected 10094, 10148, the value that needs to be passed into sql for the IN filtering to work is: '10094,10148'.

I don't see this as a SQL injection, but just another value that can be used in filtering. This simplification would simplify SQL queries that require multiple IN clauses, which I have many apps and queries with.

8

I see. That explains quite a bit, thanks.

I can see the advantage if Retool was to provide this as a standard value of a component.

Thanks again.

Jeremy