We have a Database Paged Query (DPQ) configured with a Responsive Table Generator with sortable headers.
in the DPQ we have a column which is Aggregate as SUM, ref image:
in runtime, when sorting the TotalQuantity (the summed up column name) by clicking table header, the sorting does not happen as expected.
refer image for clarity:
how to get this list to sort properly DESC or ASC - please help?
Hey guys, requesting help on this please.
@wappler_ambassadors - any help here would be awesome please.
I honestly don’t know. But, I’ll throw a random thought out while you wait for another response. I wonder if adding the sum as a variable or something would help? Something to store the number as a final number rather than an aggregation.
Where are you doing the summing, app connect or the server connect data query?
Thank you.
i’ll try this. but not sure how to tie this up with the sorting function!
update: can create a variable inside of a cell to hold the summed up value of the cell. but how to get this working with the sorting function of bootstrap table generator in wappler?!
summing is done in the Database Paged Query itself.
Weird should work.
Just a thought, try sorting the query in app connect by setting the table source to connection.data.query.sort(TotalQuantity)
(Not sure in that needs quotes,around the name)
scInventory.data.queryInventory.data.sort(TotalQuantity)
i did this. no change in the result! sorting is still broken as before.
Hi Nishkarsh,
I just created a basic example of what you are trying to do, and it works fine for me, so maybe this will help the topic along…
Here’s what I did:
A basic paged query on server connect with a SUM column of “total”
On the app connect side, a basic sorting query:
And a bootstrap table
With a paging element:
And of course, setup the server connect action to get the data:
I didn’t change from the default settings on these, just filled in the data source, etc.
Comes out like this:
Is this what you are trying to do?
–Ken
Hi @mebeingken, am I missing something here? In your last screenshot/animation, on the ‘Total asc’ you have a positive ‘20892.00’ in the middle of a load of negative numbers. So that is not working.
Good eyes Neil! Indeed, it would appear there is a problem here. I added a unique key upon seeing your post and that did not help, so I’ll dig a little deeper and file a bug report if it persists.
Thanks for the detailed note Ken. I have done the same.
and as Neil rightly pointed out, it is not working as expected.
I’ve got this to work by editing the server action file directly and by adding conditions to the ORDER BY section - at least using COUNT(). I imagine it will work for SUM too.
It seems that it won’t work if the sort parameter is given the same name as the aggregate column name. Eg in my case, the alias name was ‘subjectcount’; if I used this name for the GET parameter, the sorting would include the column I used for the aggregate column - ie it sorted by ID, then subjectcount. Once I changed the parameter name, it worked correctly. Of course it’s necessary to edit the table header as generated by Wappler’s.
Apart from being fiddly editing action files, it can also be frustrating as Wappler tends to overwrite the changes you make. It’s best to use an external editor and be careful when using the Server Action ‘Save’ button if you make more changes.
It will be great when this is fixed.
I don’t think this was every fixed and I don’t think there is a way of doing it with a custom query. I thought I had found a solution by editing the server action file, but that doesn’t work now. If I try to edit this query by clicking on the Database Query step, this appears:
Is there a way of sorting by aggregate fields in queries (standard or custom)?
that warning looks like in DB you do not have that field any more.
and it shouldn’t look like that. it should look proper like a modal - not all curled up.
The field is there. In fact the query runs, but it no longer sorts by the aggregate field - well it does, but by an additional field which of course means the sort order is incorrect.
The change I made was to add the aggregate (count) field manually, here:
. and that worked. Now it doesn't - [quote="nshkrsh, post:17, topic:13025, full:true"]
that warning looks like in DB you do not have that field any more.
and it shouldn't look like that. it should look proper like a modal - not all curled up.
[/quote]
an additional field is added to the ORDER BY statement, perhaps because Wappler doesn't recognise the field I added, so it includes what it considers a valid value. I imagine another result of not recognising the field is the broken query builder display.
In any case, it would be really good if this were fixed. It's one of those strange issues which is potentially a serious omission, but hasn't been acknowledged or commented on by the Wappler team (AFAIK).
The new bug indicators are great, but there are bugs yet to be categorised - and some which have yet to be acknowledged, like this one. Is there any news/update on this topic?
I don’t think there is any easy way to resolve this, even with a custom query (but perhaps I’m wrong). I think the problem is that the field which is used as an expression for COUNT() for example is also added to the ORDER BY statement, in addition to the aggregate alias (which should be included) - resulting in what seems a more or less random order.
I don’t think this report is a bug? Maybe more a feature request. Maybe @patrick can join in
