Pagination with PostgreSQL

Hi Folks, I’m using Adonisjs 3.2 and have been using pagination with no issues when using MySQL and SQLite.
I decided to move to PostgreSQL and found that Pagination does not work.
More specifically, when using the database query builder (with Postgres) with the pagination option , the total and lastpage are always always 1 even though there are 400 records.

The pagination option in query builder always returns with a total of 1, if there is a group by as part of the query. This is not an issue in MySQL if you skip the group by. PostgreSQL is more pedantic and enforces the need for all fields that are selected to be in the group by (unless used in aggregate functions).

const ptest = yield Database
.select('id' ).from('testrecords')
.groupBy('testrecords.id')
.paginate(1,20)

{ total: 1,
currentPage: 1,
perPage: 20,
lastPage: 1,
data:
[ anonymous { id: 1 },

const ptest = yield Database
.select('id' ).from('testrecords')
//.groupBy('testrecords.id')
.paginate(1,20)

{ total: 252,
currentPage: 1,
perPage: 20,
lastPage: 13,
data:
[ { id: 1 },

Has anyone else experienced this? I would expect the total and last page to reflect the total number of records in both cases. Is this fixed in later versions of Adonisjs?

-Matt

I have the same issue with mysql. is there any solution?

Pagination breaks when you use the Database class directly. You might need to write a custom helper for the pagination to work well.

do you have any example please?

For the issue with pagination, please see this post for a pagination helper: [CODE] PaginationHelper.js - Pagination Helper for Adonis.

1 Like