Pagination showing wrong offset total

[Philip_J]

Wappler Version : 3.2.1
Operating System : Windows 10
NodeJS, Docker, Postgres 11.x project

Expected behavior

When creating a paged query record, the total number of records in the pagination data should match the total number of records in the actual query.

Actual behavior

The total number of records showing in the pagination total count are vastly different to the actual number of records in the returned query.

This picture shows that the query result only has 7 records (using pagination for there will be more records later) but the total showing in the offset data is 5425 or 218 pages at 25 records per page.

This only occurs in a certain query where i am returning a list of records and then when i add a data metric which uses a SUM aggregate, the query then ends up like this.

How to reproduce

As above, not sure if this can be reproduced, but in this below query, it works as expected until i add the highlighted column. Once i add it, the paged will shows 5425 results in the offset total.

I’ve also tried the last join as LEFT as well and it made no difference.

Bottom line is that the query only returns 7 records in this instance, but something is causing a bug.

[patrick]

It seems that it doesn’t include the group by for the count, please test the following update.

db.zip (1.2 KB)

Place it in lib/core.

[Philip_J]

Hey @patrick. Thanks for the quick reply. I tested this out (after restarting Wappler) and while the total number of offset records has reduced, its still not quite right.

Here is the results with the exact same query as yesterday:

I noticed in your response you said it hadn’t included the group for COUNT. Just wanted to point out it was a SUM as the aggregate. Not sure if that makes a difference.

[patrick]

In the server action step for paged queries does actual do 2 queries to the database, first it does a query to get the count and then the second will get the data with the defined offset and limit. The count query is a stripped query that should be fast and only return the record count, with the complex query like in your case with an aggregate function in it, it seems to be not correct.

The code was actually from before the aggregate functions where added, so it didn’t include the extra filtering in the count query.

I will post a new updated file here as soon as I have one.

[Philip_J]

Thanks @patrick

[patrick]

Have a new update that you can test.

db.zip (1.3 KB)

[Philip_J]

Yep. This has worked. Thanks again Patrick