paginate does not work when called on a stored query object

[aviel-lanzman]

Package version

21.5.1

Describe the bug

Description
There seems to be an issue with the paginate method when attempting to use it on a query stored in a variable.

Steps to Reproduce

1. Create a query object using Post.query():

const query = Post.query();

2. Attempt to call paginate on the stored query object:

query.paginate(page, limit);

3. Observe that the method does not work as expected.

In contrast, calling paginate directly in the query chain works fine:

Post.query().paginate(page, limit); // Works

Expected Behavior
The paginate method should work even when called on a stored query object, like this:

const query = Post.query();
query.paginate(page, limit); // This should work

Actual Behavior
The paginate method only works when called directly in the query chain. When called on a stored query object, it fails to execute as expected.

Possible Root Cause
The issue might be related to how Lucid handles the query state internally. Storing the query in a variable might lose certain required configurations or bindings needed by paginate.

Environment Details
AdonisJS Version: v6
Lucid ORM Version: 21.5.1
Node.js Version: v20.15.0
Operating System: Ubuntu 24.04
package manager: pnpm

Reproduction repo

No response

[thetutlage]

Can you please share how do you verify the behavior of pagination working and not working? Maybe share the complete example and the result you get + expected output.

[aviel-lanzman]

public async index({ request }: HttpContext) {
    const query = Post.query().select(...this.selectFields)

     const qs = request.qs()

      // pagination logic
    const page = qs.page || 1 
    const limit = qs.perPage || 5
    query.paginate(page, limit)

    // sort logic
    if (qs.sortBy) {
      query.orderBy(qs.sortBy, qs.sortDesc ? 'desc' : 'asc')
    }

    return query
  }

OR

public async index({ request }: HttpContext) {
     const qs = request.qs()
     const query = Post.query()
  
      // pagination logic
    const page = qs.page || 1
    const limit = qs.perPage || 5
    query.paginate(page, limit)

    return query
  }

result =

[
{
  "id": 1,
  "content": "post number 1",
},
{
  "id": 2,
  "content": "post number 2",
},
{
  "id": 3,
  "content": "post number 3",
},
{
  "id": 4,
  "content": "post number 4",
},
{
  "id": 5,
  "content": "post number 5",
},
{
  "id": 6,
  "content": "post number 6",
},
{
  "id": 7,
  "content": "post number 7",
},
{
  "id": 8,
  "content": "post number 8",
},
{
  "id": 9,
  "content": "post number 9",
},
{
  "id": 10,
  "content": "post number 10",
},
{
  "id": 11,
  "content": "post number 11",
},
{
  "id": 12,
  "content": "post number 12",
},
{
  "id": 13,
  "content": "post number 13",
},
{
  "id": 14,
  "content": "post number 14",
},
{
  "id": 15,
  "content": "post number 15",
},
{
  "id": 16,
  "content": "post number 16",
},
{
  "id": 17,
  "content": "post number 17",
},
{
  "id": 18,
  "content": "post number 18",
},
{
  "id": 19,
  "content": "post number 19",
},
{
  "id": 20,
  "content": "post number 20",
},
]

I expect to receive the structure of a page (meta and data) and also receive the number of rows I request from the page.
like:

  "meta": {
    "total": 20,
    "perPage": 5,
    "currentPage": 1,
    "lastPage": 4,
    "firstPage": 1,
    "firstPageUrl": "/?page=1",
    "lastPageUrl": "/?page=4",
    "nextPageUrl": "/?page=2",
    "previousPageUrl": null
  },
  "data": [
{
  "id": 1,
  "content": "post number 1",
},
{
  "id": 2,
  "content": "post number 2",
},
{
  "id": 3,
  "content": "post number 3",
},
{
  "id": 4,
  "content": "post number 4",
},
{
  "id": 5,
  "content": "post number 5",
},
]
}

@thetutlage Thanks for the quick response! ☺️

[thetutlage]

In the first example, it does not work because the paginate method executes the query and not mutates the query builder. Make sure to always call the paginate method at the end.

public async index({ request }: HttpContext) {
  const query = Post.query().select(...this.selectFields)

    const qs = request.qs()

    // pagination logic
  const page = qs.page || 1 
  const limit = qs.perPage || 5
-  query.paginate(page, limit)

  // sort logic
  if (qs.sortBy) {
    query.orderBy(qs.sortBy, qs.sortDesc ? 'desc' : 'asc')
  }

-  return query
+  return query.paginate(page, limit)
}