WHERE IN filter based on

💬 Queries and Resources
1

Hello again,

I can't find this in the docs or here, so I would like to ask your help on this one.
I would like to filter my sql data with some sort of "WHERE IN" construct.

Let's say I have a table with "Allow selecting multiple rows" enabled. This table has a field 'myDate' in it and I have selected 3 rows.

The Component inspector shows:

tblTest.selectedRow.data['0'].myDate "2020-11-11"
tblTest.selectedRow.data['1'].myDate "2020-11-13"
tblTest.selectedRow.data['2'].myDate "2020-11-16"

My query is as folows:

SELECT * FROM testquery
WHERE
myDate = {{ tblTest.selectedRow.data['0'].myDate }}

Which resolves into:

SELECT * FROM testquery
WHERE
myDate = '2020-11-11'

This works for the first date (first selected row), but what I actually want is this:

SELECT * FROM testquery
WHERE
myDate IN ('2020-11-11', '2020-11-13', '2020-11-16')

What should I put in the query to achieve this?

2

Hey @mm79, good to hear from you again :slight_smile:

What will work here is highly dependent on your SQL flavor, so let's separate this into two things:

  1. Formatting your dates

You can use .map here. So something like tblTest.selectedRow.data.map(row => row.myDate) should generate the array you're looking for.

  1. Formatting your IN clause

IN might work. If it doesn't, I've also seen WHERE myDATE = ANY(<datearray>).

So in summary, the final SQL should look something like:

SELECT *
FROM testquery
WHERE myDate = ANY({{ tblTest.selectedRow.data.map(row => row.myDate) }})

Let me know if this helps!

3

Hi Justin,

I think I’m almost there, but it does not work yet. I’m using SQL Azure, so I think I need the WHERE myDate IN option. (in sql server the ANY operation only works when using a subquery)

What I did is (using the array in the query):

WHERE	
  	myDate IN 
    (
      {{ tblTest.selectedRow.data.map(row => moment(row.myDate).format("YYYY-MM-DD")) }}
    )

And then I tried (using a composed string in the query):

WHERE	
  	myDate IN 
    (
      {{ tblTest.selectedRow.data.map(row => "'" + moment(row.myDate).format("YYYY-MM-DD") + "'" ).join(", ")}}
    )

But I still get the error (in each case):

  • message:“Conversion failed when converting date and/or time from character string.”

Running the query in TSQL works, assuming that this is the statement that is pushed to the sql server, or at least, this is what works in TSQL:

  WHERE 
    myDate IN ('2020-11-15', '2020-11-16', '2020-11-17')

By the way: is there a way to catch the SQL statement that is sent to the SQL server? I’ve had multiple occasions already where that would be very convenient.

Thanks again,
Michel

1 Like
4

Oh wait… I might have found it in the SQL Cheatsheet: https://docs.retool.com/docs/sql-query-faq

5

Got it! :smiley:

WHERE	
  		myDate 
    IN (
    	SELECT
      	convert(date, value)
    	FROM
      string_split({{ tblTest.selectedRow.data.map(row => moment(row.myDate).format("YYYY-MM-DD")) }}, ',')
 		 )
1 Like
6

Hey nice! Glad you were able to figure it out :slight_smile:

7

Oh, I almost forgot.. can you also have a look at this?:

8

What do you mean by catch? Like show the final SQL statement that gets sent to the server?

9

Yes, exactly that Justin

10

Interesting idea. We do that for our REST API / GraphQL integrations, but haven’t thought about doing it for SQL quite yet. There’s a lot less uncertainty with SQL statements - the only thing you wouldn’t get in the existing editor preview (when you hover over the statement) is prepared statements, so those little $1 things you see. What are you missing from the current setup?

11

Well, for example, if you have a look at this issue: Is this a bug? data undefined in selectedRow it's sometimes hard to debug.
My query returns no results there although I was under the impression that it should, given the parameter values and the query.
Not being able to see what statement is sent to the sql server (I just get 0 results in the Preview) makes it hard sometimes.
And sometimes I get data type errors (int vs string / datetime formats).

Oh, I found something now. After clicking preview and then in the query editor a message appears below the query window. Didn't know that!

image

12

Haha yes that’s what I was referring to! Clicking on any code block in Retool should give you that preview, which helps figure out what’s getting sent / run.

13

This is great indeed. It would be nice if there was a way to copy it too… :wink: