Sort database queries on aggregated fields

Aggregated fields derived in a database query can’t be used to sort the output as they are not available in the field list for selection in the ‘Sorting’ tab.

An example use case is two tables, customers and invoices, joining them to output ‘total sales’, ‘total debt’ or ‘total overdue debt’ per customer for display to the user. Sorting on ‘total overduedebt’, for example, allows the user to prioritise follow up actions.

An alternative to do this through the front end using Query Manager does not appear to work if the aggregated field is included in the table to be displayed on teh web page. (This was the subject of a thread Sorting on a SUM() column of a Database Paged Query and a PHP code fix was provided but it hasn’t been discussed since). This might be a bug as the front end sorting does not work as expected with aggregated fields.

Voted. @Marek, don’t forget to vote for your own request! Every vote counts! :wink:

Thanks @brad. Do you think sorting through Query Manager not working on aggregated fields should be logged as a separate bug? It has come up in a couple of old forum threads but not been pursued as far as I can see.

Sorry for such a basic question but I am new to Wappler and finding my way around the community forum.

Hi Mark, you did it correct. A feature request was the proper way to go. :slight_smile:

Voted…

Is there any custom workaround to make sorting on aggregated fields working in the meantime?
I need to make a leaderboard that’s sorted by total sales count, being able to sort on aggregated value, points, amounts etc is crucial for my app…

Edit:

Made it work by changing the api file manually as suggested in this post:

It’s sorting now with the aggregated field.
Would be great if this could be in the Wappler UI though as I believe I will have to update this manually each time I play around in that api in the UI

If you’re using MySql, could this not be solved by using a view, then sort with the aggregated field defined there? I feel views are greatly underappreciated or underrepresented for their versatility.