How can I add multiple WHERE clauses for multiple inputs?

How do I do X?
1

Hi team, how can I make the WHERE clause is applied only when an input field is filled with value? The point is, if a user did not enter any ID to the Input1 or Input2 field, the page returns query without any Where conditions. The condition applies only when the Input field is populated. The trick like the below worked in Retool, but I see it’s creating an error with Appsmith. Would you share your thoughts?

SELECT
*
from test_table t

// make the WHERE clause conditional - only when the input_id field is filled
where
( {{ !input_id1.text }} or p.id = {{ input_id1.text}} ) and
( {{ !input_id2.text }} or p.id = {{ input_id2.text}} )
//

order by id desc
limit 100

2

Hi Shwan,

Can you try turning off prepared statements on the settings tab? That should hopefully solve your problem. Let me know if this works!

3

Thank you, arangasai. But no good luck with the prepared statements option.

4

Copying Hetu’s response from discord:

Sure this is possible. Since you can write any JavaScript inside the {{ }} you can even have complex if statements. You would have to wrap your code inside an IIFE to use block statements or you could also use ternary expressions Example

Select * from raw_app_data.events   {{ EvenIdInput.text ? `Where event_id = ${EvenIdInput.text}`: ""
5

Hi @shawnagie ,

Did @sumit solution work for you?

6

Thank you, all. It works like a charm!! I also thanked Hetu in the Discord thread.

2 Likes
7

Glad that it worked for you :slight_smile:

For reference, if you wanted to do this in plain SQL you could COALESCE and use an or in the condition if you have a value that can “toggle” or change:

Example:

SELECT
  *
FROM
  raw_app_data.events
WHERE
  COALESCE({{ EvenIdInput.text}}, '') != ''
  AND (
    {{ EvenIdInput.text}} = events.event_id
  )

Though, I think it’s cleaner with the JavaScript :slight_smile:

8

Thank you for the ideation, dvaun. I like the idea of having ‘true’ as part of the WHERE to make the code multi-purpose. So I will go like this, to process ‘OR’ of multiple input fields.

select
*
from raw_app_data r  
where true

{{ EventIdInput.text ? ` and r.id = ${EventIdInput.text}`: ""
}}

{{ UserIdInput.text ? ` and r.user_id = ${UserIdInput.text}`: ""
}}