Select Box In WHERE clause

App Building
1

Hi There:

I have created a select box that successfully filters based upon the selection using the following.

SELECT Products.product_name, DailyWork.due_date, WorkAssignments.cartons_assigned
FROM WorkAssignments
JOIN Crews on WorkAssignments.Crews_id = Crews.id
JOIN DailyWork on WorkAssignments.DailyWork_id = DailyWork.id
JOIN Products on DailyWork.Products_id = Products.ID
WHERE WorkAssignments.Crews_id ={{SelectCrewFilter.selectedItem.id}}

This works ok, but was hoping to have it just display all results when there is no selection made in the select box. Right now, it is correctly displaying nothing because it evaluates to null. What is the proper way to change

WHERE WorkAssignments.Crews_id ={{SelectCrewFilter.selectedItem.id}}

So it'll show all results until a selection is made and then filter

Thanks in advance!

2

Two SQL queries and some javascript is how we have achieved this in the past.

if SelectCrewFilter == null trigger SQLquery1
else trigger SQLquery2

3

This is a working version of that javascript that is currently in one of our apps.

if (textInput1.value === '' || null) {
  return(localProjects.value)
} else {
  return(finalArray)
}
4

The solution above from Steven will work! But if you don't want to create multiple queries, another approach to this would be something like this:

SELECT *
FROM users 
WHERE (first_name = 'Dave' and {{checkbox1.value == true}})
1 Like
5

Thanks for this! I am still really new, where would I actually put this Javascript? I have modified it for my data, but not sure where to actually put it in the retool gui

6

Kenny's solution is more elegant.

You would trigger the two different queries where my return statements are. You know where you select your SQL query? Select a JavaScript query instead and slap that in. Then on the button to trigger your SQL query instead trigger the JavaScript query.

image

image
image737×377 21.9 KB

Its slightly more advanced/complicated. I would add to the where clause like Kenny suggested. Its simpler.

7

Stupid me! Didn't even see that JS option. Still learning, but this is awesome and helpful as was the other answer. Thanks again!

8

Everyone has to start somewhere, we started in retool a few months back and were slamming our heads against the wall at first. Once you build a couple apps and/or get some really advanced queries running it will all start to click.

Good luck!

2 Likes
9