How to create a dynamic query without changing resource settings?

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.

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

Thanks man. Seems it’s working :slight_smile:

2 Likes

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

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?)

Thanks @alex-w for the link.