I can’t manage to order a query by a form value, even if the form value has the exact same value as the column name. Simplief SQL query:
select (cost/flight_main) as home,
from test
order by {{ select.value[0] }} <- value is home
How can I order by a column that is dynamically set by a form value? I have tried many many things so far, like transformers, hiding and showing copy tables, multiple data sources per table, afterhandler scripts (will spare you the details). It only works if I hardcode the ‘order by’ column in the SQL query
FYI. I can’t paginate on the server side, so the ‘case when’ solution in the docs does not apply. Thanks for any suggestions.
Thanks for responding. Prepared statements are not disabled and the select value is never null. The issue happens with both dropdowns and text inputs. I can see that the value is there before the query is kicked off .
Fun fact is that it works if I change the ID name of the component from {{ select.value }} to ‘home’,.
select (cost/flight_main) as home,
from test
order by {{ home.id }} <- rename id
Of course this does not solve my problem as the ID is not dynamic, but it proves that the error happens with the dynamic value and not in the DB.
Hmmmmm. So I was able to repro this locally. It seems to be isolated to using strings as the columns - but if you use an integer (e.g. ORDER BY 1) it works pretty well. So what you could do is have your display values in the dropdown map to the integer that represents their order in the query. Here's a quick example I put together - in the dropdown settings, I'm mapping the display value (the name of the column) to the order of that column in my SELECT statement.
If not, one other workaround would be to order the data on the frontend instead of the backend. I'm not sure what you're doing with the query's data, but if you're displaying it in a table, you can set the table to programmatically sort using the table's .sort() method.
