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
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}'` : "'%'"}}
9

I am trying to insert dynamic data from a previous step into a sql query using an IN operator. I keep getting an error because retool is surrounding the output with double quotes. I've tried using .replace to remove them but it doesn't work. Here is the error:

invalid input syntax for type uuid: "'e93be242-762c-49ef-80d2-db85952d93c2','27984df9-ebd5-4278-a45e-18b54c5ef66b'"

As you can see, retool is adding a double quote around the dynamic data.

10

Using arrays in SQL can be a bit tricky, especially with prepared statements since it depends on the type of SQL you're using. There's a cheatsheet here that might help get you the exact syntax you need! Let me know if that helps or just raises more questions :sweat_smile:

11