How to create a dynamic query without changing resource settings?

Queries and Resources
#1

How to create a dynamic query without changing resource settings "Disable converting queries to prepared statements"?

Here is my situation. I have a dropdown with values "pending", "fulfilled" ... It's for order status. In our DB we use enum type for this column.

So I need to create WHERE statement where I can change the column value like:

    WHERE status= {{ order_status.value }}

It works ok if I select one of the options from the dropdown. But I need also the ability to select all orders regardless of the status so actually I need to remove this "status= {{ order_status.value }}" completely from the query. Is this any workaround for this?

I don't want to use conditional data source for the table that shows the data as there are additional 2 dropdowns so the number of combinations is higher.

1 Like
Table filters for calculated columns
#2

Hey @ckissi! I’m not sure if I 100% understand your use case, but let me know if this might help. If you want to not filter if the dropdown is empty (or like an “all” option), you can do this:

WHERE {{ !order_status.value }} OR status = {{ order_status.value }}

This way if the dropdown is empty, it just evaluates to WHERE true and returns all results. Does this work?

1 Like
#3

Thanks man. Seems it’s working :slight_smile:

2 Likes
#4

Awesome! This is a pretty common question, and I use this pattern in almost every one of my apps :slight_smile:

#5

I wanted to surface the SQL Cheatsheet here, which includes this tip and some other DB type specific quirks for matching in arrays (who knew ID in [1,2,3] could come in so many flavors?)

#6

Thanks @alex-w for the link.

#7

Hi,

does this technique work in Salesforce SOQL?
I'm trying this in SOQL but got an error message like this.

message:"
where  true or enterpriseId__c=null
      ^
ERROR at Row:5:Column:7
unexpected token: 'true'"

with salesforce resource, you can't write apex code so virtually can't we use dynamic queries in Retool?

#8

Hey @takkme!

Would you mind sharing more about the properties of the column you're trying to filter on? It doesn't look like the strategy is directly portable to SOQL but there may be some other creative solution available, e.g.

select id, name from account where name like {{textInput1.value ? `'${textInput1.value}'` : "'%'"}}