Modify a WHERE clause to depend on component state?

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?

Bump…any thoughts anyone?

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({{}})

return x_data.filter(x => 
	!"test") &&
	) ||
	{{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.

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.

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’


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.

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.

You can workaround this limitation in some cases using the approach Dominic outlined here!