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.
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
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.
Perhaps it wasn’t presented as a bug initially. This obviously happens when it’s not known if an issue is a bug or just not knowing how to use a feature. I think it should have been turned into a bug topic.
If you use Query Builder to create a query with aggregate columns and use this query with the table generator, including column sorting options, the sorting will fail for the aggregate columns - because an extra column is included. I would have thought this was a bug.
Could you create a new bug topic for this? It is the best way to go, otherwise bug report and tracking get lost in the noise of longer topics like this one.
I’ve created a new topic for this. For some reason, none of the Wappler team contributed to this thread, so it was never confirmed as a bug - at least I assume it is.
Just to make sure, the problem is that when you make the columns sortable with the table generator, sorting on the aggregate columns do fail. I don’t believe it was possible to order on the aggregate columns in the query builder, but the table generator is not aware of the aggregate columns and makes them sortable. Will see what we could improve.
btw. Which server model do you use, ASP/PHP or node?
Yes, that’s correct. Using the table generator, the sort headers appear as you would expect, and sort - it’s just that the sorting isn’t correct. Eg instead of:
ORDER BY `count_total` DESC
the query includes:
ORDER BY `stock_id` DESC, `count_total` DESC`
This using PHP.
I have updated the sort, here the updated file. Place it in dmxConnectLib/modules.
dbconnector.zip (1.6 KB)
Update will also be included in today’s Wappler update.
1 Like
Great - I’ve just tested it and the issue is indeed fixed. Thanks.