I'm trying to set a query's ORDER BY using form field values, but the query runs as if there are no ORDER BY statements in the query at all. I've read a lot of threads in the community and I can't seem to find a single similar thread.
I am employing a RETOOL Database Resource in this project.
Working
SELECT
*
FROM
bidllama_truckloads
WHERE
status = ANY({{allTruckloadsStatusDropdown.value}})
ORDER BY delivery_eta DESC;
Not Working
SELECT
*
FROM
bidllama_truckloads
WHERE
status = ANY({{allTruckloadsStatusDropdown.value}})
ORDER BY
{{allTruckloadsSortField.value+" "+allTruckloadsSortDirection.value}}
The one that's not working is producing valid SQL...
SELECT * FROM bidllama_truckloads WHERE status = ANY(Ordered,Shipped,Pending Delivery,Complete,Processing,Staging) ORDER BY delivery_eta DESC;
Here's what my Query looks like in RETOOL... please tell me this is possible and I'm just doing something wrong. It feels like what I'm trying to do is out of execution order or something.
I thought perhaps an animated GIF might help someone digest what I'm experiencing.
In this screen capture I flip the Input-Component's value between "DESC" and "ASC" and you can see that the Query-Component registers the change, then the query executes as if there is no ORDER BY clause at all.
Thank you for your help! I'm very grateful.
NOTE: you might need to open this image in a new tab for full resolution.
Some Additional context for the seeker... this is how I began, but this seems to fail outright with an error that looks like a regex replace? Not sure why this fails either.
So I guess I need to setup a hosted resource without prepared clauses, or find a more creative solution using REST or GraphQL (which I was trying to avoide because I'm in a crunch).
