Sorting on a SUM() column of a Database Paged Query

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:
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:


sorting done Desc but list is shown in random order.

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

1 Like

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.

1 Like

Thanks for the detailed note Ken. I have done the same.
and as Neil rightly pointed out, it is not working as expected.

Perhaps @Teodor or @patrick can help us here.

2 Likes

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.

1 Like