"Order by" not working for dynamic dropdown value (MySQL)

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.

Hey there @ForgetAbout, and welcome to the community! A couple of questions that might help you debug this:

  • Have you disabled prepared statements in your resource settings?
  • Is the select value ever null? Is this a multiselect or a dropdown?

Hi Justin,

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.

Gotcha. Can you try disabling prepared statements and see if that works?

Does not make a change

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.

Could this work for your use case?

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.

+1 on this~
the order by 1,2,3 solution doesn't work for AlaSQL~