Paging

Most of the queries can be paged using the page or range method.

const result = await Person.query()
  .where('age', '>', 20)
  .page(5, 100);

console.log(result.results.length); // --> 100
console.log(result.total); // --> 3341

There are some cases where page and range don't work.

In modifyGraph or modifiers:

// This doesn't work because the query `qb` fetches the
// `children` for all parents at once. Paging that query
//  will have not fetch 10 results for all parents, but
// instead 10 results in total.
const result = await Person.query()
  .withGraphFetched('children')
  .modifyGraph('children', qb => qb.page(0, 10));

When withGraphJoined is used:

// This doesn't work because of the way SQL joins work.
// Databases return the nested relations as a flattened
// list of records. Paging the query will page the
// flattened results which has alot more rows than
// the root query.
const result = await Person.query()
  .withGraphJoined('children')
  .page(0, 10);

On delete queries:

// Throws: offset() can't be used with a delete query.
// Deletes have no offset, so the query would delete the wrong rows.
await Person.query().delete().page(2, 10);

// MySQL: page 0 has no offset, so this works like limit(10).
// postgres and SQLite: knex rejects a limit on a delete with
// "Aborted query compilation: `limit` has no effect on a `delete` statement".
await Person.query().delete().page(0, 10);

Paging ​

Paging