Modify a WHERE clause to depend on component state?

Queries and Resources
1

I'm populating a table with a list of user accounts. Some of those accounts are internal test accounts, and I'd like to either include or exclude those from the table depending on a checkbox's state.

I'm currently doing this the bad way - two separate queries. For example, if the box is unchecked, I use one query(simplified version)

SELECT * from users 
WHERE <basic criteria>

But when I check the "exclude test accounts" checkbox, I run a different query which is identical to the one above except it's got some extra WHERE conditions:

SELECT * from users 
WHERE <basic criteria> 
AND email NOT LIKE 'test%' 
AND email NOT LIKE 'fake%'

Ideally I'd love to use a single query that I dynamically modify based on the checkbox state. Any thoughts on how best to accomplish this? Is there such a thing as an if block in a Retool code template?

2

Bump…any thoughts anyone?

3

Depends how many rows the query returns, you could do it client side by creating something like this as a transformer. Assume that the checkbox checked means DO show tests:

const x_data = formatDataAsArray({{query1.data}})

return x_data.filter(x => 
	(
	!x.email.startsWith("test") &&
	!x.email.startsWith("fake")
	) ||
	{{checkbox1.value}} === true 
)

Then change the table data section to reference {{transformer1.value}}

If that doesn’t work you might need to fiddle around with the ‘startswith’ and use regexp or something, but I use something very similar on one of my apps to filter on a whole bunch of different checkboxes, dropdowns, toggles etc. Just keep adding conditions to the filter and linking them to a component.

4

Ahh client-side via transformer – that’s a good approach! That’ll probably work to a certain point (few thousand rows I presume). Thank you! I’ll do this for now.

Would still love to know if there’s a way to vary the conditions within a given query or if queries just weren’t designed for that kind of dynamic behavior.

5

Yeah I’m new to retool and probably battling with the same ‘top 10’ problems that everyone else encounters, I figured that for smaller record sets client side makes sense (and am making myself learn javascript, SQL is more my thing).

The problem I have, is how to pass something to SQL that it will recognise.

Could you not just do

WHERE <basic criteria> AND 
(
    (
    email NOT LIKE 'test%' AND 
    email NOT LIKE 'fake%' 
    ) OR
{{checkbox1.value}} IS TRUE
)
)

In that instance, by the time it gets to the query, checkbox1.value should just be true or false, so it should just be evaluating true = true or false = true. No?

Let me know if that works, would be good to put in the notebook as a ‘reference pattern’

thanks
dominic.

6

Dominic - this works perfectly and is exactly what I was after. Thanks!! The general pattern of doing a <condition 1> OR <condition 2> where condition 1 is a true/false checkbox or field is the pattern I’d forgotten to use here.

7

All of our SQL queries are converted to prepared statements to prevent SQL injection (I.E. typing DROP TABLE users into a {{textinput.value}} property), meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically. You can disable this setting in the resource setup, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. If that's something you'd like to explore, I often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.

1440×502 45.5 KB
You can workaround this limitation in some cases using the approach Dominic outlined here!