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