Passing a list to SQL Query "IN"

Help & Support 💻 How-to's
1

I am trying to pass a list of values to a SQL statement as in:

SELECT * FROM XYZ WHERE index IN (‘xyz0’,‘xyz1’,‘xyz2’)

The list in the WHERE statement is the issue. The list to pass is from a textarea ui item.

I know this could be done with a dropdown or other list (with multi select enabled), but the input needs to accomodate anything the user would paste in, like a list of tracking numbers for example.

Any ideas?

2

Hey @lbyjeff,

One thing that you could do is to take the value, split the values by some predefined character, e.g. input as a comma seperated list xyz0, xyz1, xyz2, ... and split by comma, or any character really (good options are characters like newline \n, semicolon ;, etc.).

From there then just add the values inline in the query with either map() or reduce():

With map()

SELECT * FROM XYZ WHERE index in ( {{ui.textArea.value.split(",").map( v => "'"+v+"'" ) }} )

With reduce()

SELECT * FROM XYZ WHERE index in ( {{ ui.textArea.value.split(",").reduce((acc, val, i, arr) =>
  acc += "'" + val + "'" + (i !== arr.length - 1 ? ", " : ""), "") }} )

But yeah, map() is definitely easier.

3

I will give that a shot. thanks!

1 Like
4

I would suggest splitting it to 2 step

  1. Javascript code step return [‘xyz0’,‘xyz1’,‘xyz2’]
  2. SQL select
1 Like
5

This looks more like what I’m after. Thank you, I’ll try this one.

6

I need more assistance on this, I know nothing of javascript…

I have the filter on the action/load table, which normally works.

the data is coming from a text box (i can’t tell which component type it is because once i rename it, it doesn’t say what kind of component it is)

I have the text box’s data in the filter, but how do i get the multi line into a ‘single quoted’, comma delimited list that the filter will apply properly?

I have the <>code step in the action before the load item (as you suggested)
Next is the the “load table” with the single filter of fieldname IN {{ui.component.value}}

The query returns nothing.

The data in the text box contains 3 items, separated by line feed/returns.

Thanks!!

7

Hi @lbyjeff,

In the first “Javascript code” step you should have something similar to this

return {{ ui.component.value.split(/\r?\n/) }}

This code should split your string by end of the line and return array of strings. Test it carefully, run only this step and check what it returns. If it doesn’t return array of strings please share what you have in {{ ui.component.value }}

In the second “Load table” step add filter: fieldname IN {{ data }}
{{ data }} returns result of the previous step, as I said before it should be array of strings.

2 Likes
8

What @Konstantin_Danovsky suggested before is basically what I told you, but as an extra step before sending the request to the DB, so that in the Load Table action you can simply enter {{data}} for the IN filter. You could also put the snippet of code I told you (with map()) in there and leave the previous step away, depends on how you want to do it.

But overall the concept is the same:

  1. Add some text input component to the page, as example let’s take a text area

  2. Create an action

  3. Grab the text area’s value and split the text by a pre-determined character (as we discussed before, any character would be valid, but preferably some delimiter like \n (newline), , (comma), \s whitespace, etc.)

    Note: This can be done either as an extra step, returning the array, so that you only need to enter {{data}} in the Load Table step for the IN filter, or inline inside a SQL Query or Load Table step. Me and @Konstantin_Danovsky described both ways above.

If you still have issues, you can tell us specifically what troubles you.

1 Like
9

Thank you! this worked perfectly!