Ordering and pagination

Order by sortable fieldsCopy Anchor Link

import { db } from "./db";

const query = db
  .selectFrom("Account")
  .select(["Id", "Name", "AnnualRevenue"])
  .orderBy("AnnualRevenue", "desc", "last")
  .orderBy("Name", "asc")
  .orderBy("Id")
  .limit(25);

orderBy(field, direction?, nulls?) accepts "asc" or "desc", plus "first" or "last" for null placement.

Calls append ordering terms. Add a stable tie-breaker such as Id when you paginate so rows with the same earlier sort value still have a deterministic order.

The field must be generated as sortable. Geolocation uses distance ordering, and the virtual polymorphic .Type qualifier is not sortable.

Limit and offsetCopy Anchor Link

import { db } from "./db";

const secondPage = db
  .selectFrom("Account")
  .select(["Id", "Name"])
  .orderBy("Name")
  .orderBy("Id")
  .limit(25)
  .offset(25);

limit(n) accepts a non-negative safe integer. offset(n) accepts a safe integer from 0 through 2000.

A later limit() or offset() replaces the earlier value. clearLimit(), clearOffset(), and clearOrderBy() remove those clauses from the new builder they return.

Use OFFSET for small page-based UIsCopy Anchor Link

LIMIT plus OFFSET is convenient when a user moves through a modest number of pages.

It is not the right tool for exporting large datasets. Salesforce caps OFFSET at 2000 rows, and each page is a separate query. Data that changes between requests can also shift rows between pages.

Normal .execute() already follows Salesforce query locators when one query produces multiple response pages. .executeAll() is for QueryAll semantics, not for ordinary pagination.

For large result processing, use the REST iterators or an application-level keyset strategy.

Keyset exampleCopy Anchor Link

A simple ID cursor can resume after the last returned row:

import { db } from "./db";

export async function loadPage(afterId?: string) {
  let query = db
    .selectFrom("Account")
    .select(["Id", "Name"])
    .orderBy("Id")
    .limit(100);

  if (afterId !== undefined) {
    query = query.where("Id", ">", afterId);
  }

  return query.execute();
}

This is an application pattern rather than a built-in Kysoql cursor API. If you sort by a non-unique field such as CreatedDate, include a tie-breaker and carry both values in the cursor.

Aggregate and child-query differencesCopy Anchor Link

Grouped aggregate queries can use LIMIT and OFFSET after grouping. Bare COUNT() has a narrower API.

Child subqueries do not have normal offset support. Salesforce's child-query offset is a Pilot feature exposed through pilot.offset() and has additional restrictions.

Apex binds have their own limit and offset rules.