SQL querying to allow dynamic fields

💬 Queries and Resources
1

Hey everyone :wave:

I’m building a Retool app that filters data based on multiple inputs a dropdown, textbox, and date. I want the query to build its WHERE clause dynamically based on whatever filters the user fills in. Right now, if one of the fields is empty, the query breaks or returns nothing. Is there a way to handle multiple inputs dynamically that I am missing?

Thanks!

2

A way to set this up is something like this

SELECT *
FROM table
WHERE 1=1
{{ dropdown.value ? AND value = {{ dropdown.value }} : '' }}
{{ textInput.value ? AND value ILIKE '%' || {{ textInput.value }} || '%' : '' }}
{{ (startDate.value && endDate.value) ? AND created_at BETWEEN {{ startDate.value }} AND {{ endDate.value }} : '' }}

the reason to use 1=1 is because is nothing is selected then you will receive a error or no items so it is in a sense a default case which will return everything.

3

Hey @Treydog24,

Yes, this is entirely possible. Could you please share one of your queries with your where clauses?

4

Here’s an example to demonstrate how to dynamically build WHERE clauses in Retool based on user inputs from a dropdown (selectStatus), a textbox (searchClient), and a date range (dateRange1). This example assumes a basic clients table with fields like name, status, and created_at.

SELECT *
FROM clients
WHERE
  (
    {{ !searchClient.value.length }} -- if no text input, ignore
    OR name ILIKE '%' || {{ searchClient.value }} || '%'
  )
  AND (
    {{ !selectStatus.value }} -- if no dropdown selected, ignore
    OR status = {{ selectStatus.value }}
  )
  AND (
    {{ !dateRange1.value.start || !dateRange1.value.end }} -- if no date range selected, ignore
    OR created_at::date BETWEEN {{ dateRange1.value.start }}::date AND {{ dateRange1.value.end }}::date
  )
ORDER BY created_at DESC;

This should get you started. If you need specific help, just post your query here. :slight_smile:

2 Likes