I have created a ‘Database Custom Query’, and I need to order the results dynamically.
Per my screenshots below, if I hardcode the ‘ORDER BY’ value into the query, the results return as expected. However, if I pass a parameter to it instead, it is ignored and the results are ordered by their IDs.
Has anyone got any ideas?
I don’t think this is possible with Wappler. I asked a similar question here and George commented:
You can’t create dynamic sql with them like having dynamic column names or sorting order based on parameters… This is how PDO prepared statements work with their parameters.
I appreciate this is to with PDO Prepared Statements, but it’s a big limitation of custom queries. I think there are ways round this - while maintaining the security advantages of parameterized queries.
I hit this once as well. I went with a stored procedure so I could pass parameters in and deal with it in MySQL.
I’d be very much interested in more details on stored procedures as I stumbled upon another case where LIMIT can’t be provided dynamically with a custom query (and I couldn’t use the paged version either).
Do you remember how you implemented this or know another topic that could help ?
There’s tons of docs online about creating the actual stored procedure in mysql (or your flavor of db) so take a look on google for that to get started. For Wappler, you just call the procedure from a custom action:
The Expression column is what will be used at runtime, and the Test Value column allows you to put some sample data in and run the procedure (by clicking the play button near the top.) It will give you the results and store the schema for choosing in the data picker.
Awesome thanks, that’s the piece of info I was after. 
