Pagination

Basic pagination

Sqlmancer supports offset pagination using the limit and offset methods.

Actor.findMany().limit(10).offset(20).execute();

When using the client inside a resolver, you can use the resolveInfo method to avoid calling the limit or offset methods altogether. Sqlmancer will look for arguments named limit and offset and modify your query builder appropriately.

resolve(root, args, ctx, info) {
return Actor.findMany().resolveInfo(info).execute();
}

Instead of adding those arguments directly to your field, you may also utilize the @limit and @offset directives.

tip

You can also use the @many directive instead of applying the @where, @orderBy, @limit and @offset directives separately.

Page types and paginate

Instead of just returning a list of a particular type, your schema can return a "Page" object instead that includes these three fields:

  • results: a list of the paginated results
  • hasMore: a boolean indicating whether there are more results to be fetched
  • aggregate: an object for aggregating the values of the results

We can use the @paginate directive to generate the appropriate "Page" type for a particular model:

type Query {
actors: Actor @paginate
}

which will be the equivalent of doing something like this:

type Query {
actors: ActorPage
}
type ActorPage {
results: [Actor!]!
hasMore: Boolean!
aggregate: ActorAggregate!
}
type ActorAggregate {
count: Int!
avg: ActorAvg!
sum: ActorSum!
min: ActorMin!
max: ActorMax!
}

Each model provides a matching paginate method that can be used to populate such a field:

Actor.paginate()
.selectAll()
.hasMore()
.limit(25)
note

The paginate method will return an empty object by default. Calling hasMore will add the hasMore field to the result object. Calling any of the aggregate methods, like count will add the aggregate field to the result object. Calling any of the select or load methods will add the results field to the result object.

The PaginateBuilder returned by the paginate method includes a resolveInfo method for easy use inside a resolver:

resolve(root, args, ctx, info) {
return Actor.paginate().resolveInfo(info).execute();
}

The paginate method can be used for either paginating over a large set of results, or for aggregating field values for a particular set of records. The same methods for sorting and filtering are also available on the PaginateBuilder. You can read about aggregation in more depth here.

Keyset pagination

While Sqlmancer doesn't explicitly support cursor-based pagination, you can still utilize keyset pagination. Keyset pagination is often more performant than offset-based pagination, particularly on large data sets. The field used as the cursor needs to be sortable and unique. For example, you can use the primary key field

Film.findMany()
.where({ id: { greaterThan: someId } })
.orderBy([{ id: 'ASC' }])
.limit(10);

If you want to sort the results by a field that isn't necessarily unique, then you can combine that field with another field that is unique, like the primary key

Film.findMany()
.where({
or: [
{
stars: { lessThan: someValue },
},
{
and: {
stars: { equal: someValue },
id: { greaterThan: someId },
}
}
],
})
.orderBy([{ stars: 'DESC' }, { id: 'ASC' }])
.limit(10);