Skip to content
Active Record
Esc
↑↓navigate↵open⌘Jpreview
On this page

Ordering and pagination

Sort a query’s records, and split them into pages.

At a glance

To Write
sort by attributes order({ publishedAt: "desc", title: "asc" })
place NULLs order({ publishedAt: { direction: "desc", nulls: "last" } })
replace the order reorder({ createdAt: "desc" }), or reorder() to drop it
sort by a list of values inOrderOf("status", ["published", "draft"])
skip and take rows limit(20).offset(40)
get one page with its total paginate({ page, perPage: 20 })
get a page without counting paginate({ page, count: false })
PropType
page?number | string | null

The page to read. Text from a query string works as it is.

Typenumber | string | null
Default1
perPage?number | string | null

Records on each page, up to maxPerPage.

Typenumber | string | null
Default25
count?boolean

Whether to count every matching record for total and totalPages.

Typeboolean
Defaulttrue

The Model

The examples use this Model, whose attributes come from its table through the schema active-record schema generates:

import { ApplicationRecord } from "./application-record";

export class Post extends ApplicationRecord {}
// Generated by `active-record schema` from migrations/ — don't edit.
// schema: 136cbf4aca25

import { defineSchema, type TableAttributes } from "@forestfuture/active-record";
import { d1 } from "@forestfuture/active-record/d1";

/** posts — last changed by 0001_create_posts.sql */
export const posts = {
  id: { type: "string", null: false, generateId: true },
  title: { type: "string", null: false },
  status: "string",
  publishedAt: "datetime",
  createdAt: { type: "datetime", null: false },
  updatedAt: { type: "datetime", null: false },
} as const;

/** The database's adapter and binding, its tables (with their primary keys), and the Models built on them. */
export const schema = defineSchema({
  adapter: d1({ binding: "DB" }),
  tables: {
    posts: { attributes: posts, primaryKey: "id" },
  },
  models: { Post: "posts" },
});

declare module "../models/post" {
  interface Post extends TableAttributes<typeof posts, (typeof Post)["attributes"]> {}
}
CREATE TABLE posts (
  id TEXT PRIMARY KEY NOT NULL,
  title TEXT NOT NULL,
  status TEXT,
  published_at DATETIME,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);
CREATE TABLE posts (
  id UUID PRIMARY KEY,
  title TEXT NOT NULL,
  status TEXT,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL
);

Ordering records

Sort by each key in turn with order, and page with limit and offset:

import { function column(attribute: string): ColumnReference
Names the column of `attribute` inside an `sql` fragment, quoted and mapped (ADR-0004).
column
, function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql } from "@forestfuture/active-record";
import { class PostPost } from "./models/post"; const const newest: Post[]newest = await class PostPost.order<typeof Post>(this: typeof Post, spec: SafeSql | OrderSpec<Post>): Relation<Post, ScopesOf<typeof Post>>
Sorts by each key in turn (`{ createdAt: "desc" }`, or `{ role: { direction: "asc", nulls: "last" } }`), or by an `sql` fragment expression. Repeated calls append.
order
({
publishedAt?: OrderDirection | {
    direction: OrderDirection;
    nulls?: NullsPlacement;
} | undefined
publishedAt
: "desc",
title?: OrderDirection | {
    direction: OrderDirection;
    nulls?: NullsPlacement;
} | undefined
title
: "asc" }).Relation<Post, ScopesOf<typeof Post>>.toArray(): Promise<Post[]>toArray();
class PostPost.order<typeof Post>(this: typeof Post, spec: SafeSql | OrderSpec<Post>): Relation<Post, ScopesOf<typeof Post>>
Sorts by each key in turn (`{ createdAt: "desc" }`, or `{ role: { direction: "asc", nulls: "last" } }`), or by an `sql` fragment expression. Repeated calls append.
order
({
publishedAt?: OrderDirection | {
    direction: OrderDirection;
    nulls?: NullsPlacement;
} | undefined
publishedAt
: { direction: OrderDirectiondirection: "desc", nulls?: NullsPlacement | undefinednulls: "last" } }); // unpublished last, on both
class PostPost.order<typeof Post>(this: typeof Post, spec: SafeSql | OrderSpec<Post>): Relation<Post, ScopesOf<typeof Post>>
Sorts by each key in turn (`{ createdAt: "desc" }`, or `{ role: { direction: "asc", nulls: "last" } }`), or by an `sql` fragment expression. Repeated calls append.
order
(function sql(strings: TemplateStringsArray, ...values: unknown[]): SafeSqlsql`lower(${function column(attribute: string): ColumnReference
Names the column of `attribute` inside an `sql` fragment, quoted and mapped (ADR-0004).
column
("title")})`);
class PostPost.order<typeof Post>(this: typeof Post, spec: SafeSql | OrderSpec<Post>): Relation<Post, ScopesOf<typeof Post>>
Sorts by each key in turn (`{ createdAt: "desc" }`, or `{ role: { direction: "asc", nulls: "last" } }`), or by an `sql` fragment expression. Repeated calls append.
order
({
title?: OrderDirection | {
    direction: OrderDirection;
    nulls?: NullsPlacement;
} | undefined
title
: "asc" }).Relation<Post, ScopesOf<typeof Post>>.reorder(spec?: SafeSql | OrderSpec<Post> | undefined): Relation<Post, ScopesOf<typeof Post>>
Replaces the order so far; `reorder()` removes it.
reorder
({
createdAt?: OrderDirection | {
    direction: OrderDirection;
    nulls?: NullsPlacement;
} | undefined
createdAt
: "desc" }); // replaces the order
class PostPost.order<typeof Post>(this: typeof Post, spec: SafeSql | OrderSpec<Post>): Relation<Post, ScopesOf<typeof Post>>
Sorts by each key in turn (`{ createdAt: "desc" }`, or `{ role: { direction: "asc", nulls: "last" } }`), or by an `sql` fragment expression. Repeated calls append.
order
({
createdAt?: OrderDirection | {
    direction: OrderDirection;
    nulls?: NullsPlacement;
} | undefined
createdAt
: "desc" }).Relation<Post, ScopesOf<typeof Post>>.limit(count: number): Relation<Post, ScopesOf<typeof Post>>limit(20).Relation<Post, ScopesOf<typeof Post>>.offset(count: number): Relation<Post, ScopesOf<typeof Post>>offset(40); // page 3

Calling order again adds more keys. reorder replaces the order so far, and reorder() removes it.

Ordering by a list of values

Sort records by where their value comes in a list with inOrderOf:

import { class PostPost } from "./models/post";

const const byStatus: Post[]byStatus = await class PostPost.OverloadedStatics.inOrderOf: <typeof Post, "status">(this: typeof Post, attribute: "status", values: readonly (string | null)[], options?: InOrderOfOptions) => Relation<Post, ScopesOf<typeof Post>>
The class's Relation ordered by `attribute`'s place in `values` (#350).
inOrderOf
("status", ["published", "draft"]).Relation<Post, ScopesOf<typeof Post>>.toArray(): Promise<Post[]>toArray(); // only those
const const everyPost: Post[]everyPost = await class PostPost.OverloadedStatics.inOrderOf: <typeof Post, "status">(this: typeof Post, attribute: "status", values: readonly (string | null)[], options?: InOrderOfOptions) => Relation<Post, ScopesOf<typeof Post>>
The class's Relation ordered by `attribute`'s place in `values` (#350).
inOrderOf
("status", ["published"], { InOrderOfOptions.filter?: boolean | undefined
Keep only the records whose value is in the list (the default); `false` keeps every one, those not in it last.
filter
: false }).Relation<Post, ScopesOf<typeof Post>>.toArray(): Promise<Post[]>toArray();
  • By default, only records whose value is in the list are kept. Pass filter: false to keep the rest, after them.
  • Values are cast as where’s are, and null places NULL. The list is bound as one value, so it can be any length.
  • An order written after it sorts the records that tie, and last() reverses it.
  • It can’t be combined with distinct(), which throws IncompatibleRelation.
  • The database looks each record’s value up in the list, so a long list costs more on a large table. Keep it to the values you mean to order by.

Paginating

paginate resolves to one page of records and the total they’re a page of:

import { class PostPost } from "./models/post";

export async function function listPosts(request: Request): Promise<Response>listPosts(request: Requestrequest: Request): interface Promise<T>
Represents the completion of an asynchronous operation
Promise
<Response> {
const const page: string | nullpage = new var URL: new (url: string | URL, base?: string | URL) => URL
The **`URL`** interface is used to parse, construct, normalize, and encode URLs. It works by providing properties which allow you to easily read and modify the components of a URL. [MDN Reference](https://developer.mozilla.org/docs/Web/API/URL)
URL
(request: Requestrequest.Request.url: string
The **`url`** read-only property of the Request interface contains the URL of the request. [MDN Reference](https://developer.mozilla.org/docs/Web/API/Request/url)
url
).URL.searchParams: URLSearchParams
The **`searchParams`** read-only property of the URL interface returns a URLSearchParams object allowing access to the GET decoded query arguments contained in the URL. [MDN Reference](https://developer.mozilla.org/docs/Web/API/URL/searchParams)
searchParams
.URLSearchParams.get(name: string): string | null
The **`get()`** method of the URLSearchParams interface returns the first value associated to the given search parameter. [MDN Reference](https://developer.mozilla.org/docs/Web/API/URLSearchParams/get)
get
("page");
const const posts: Page<Post>posts = await class PostPost.order<typeof Post>(this: typeof Post, spec: SafeSql | OrderSpec<Post>): Relation<Post, ScopesOf<typeof Post>>
Sorts by each key in turn (`{ createdAt: "desc" }`, or `{ role: { direction: "asc", nulls: "last" } }`), or by an `sql` fragment expression. Repeated calls append.
order
({
publishedAt?: OrderDirection | {
    direction: OrderDirection;
    nulls?: NullsPlacement;
} | undefined
publishedAt
: "desc" }).
Relation<Post, ScopesOf<typeof Post>>.paginate(options?: PageOptions & {
    count?: true;
}): Promise<Page<Post>> (+2 overloads)
One page of records and its total, by this Relation's order or the primary key, in one round trip (#100). With `count: false`, no `COUNT`: the page has no total.
paginate
({ PageOptions.page?: string | number | null | undefined
The page, from 1: a number, or untrusted text such as a query string's `"2"`.
page
, PageOptions.perPage?: string | number | null | undefined
Records on a page, a number or text as `page` is.
perPage
: 20 });
return
var Response: {
    new (body?: BodyInit | null, init?: ResponseInit): Response;
    prototype: Response;
    error(): Response;
    json(data: any, init?: ResponseInit): Response;
    redirect(url: string | URL, status?: number): Response;
}
The **`Response`** interface of the Fetch API represents the response to a request. [MDN Reference](https://developer.mozilla.org/docs/Web/API/Response)
Response
.function json(data: any, init?: ResponseInit): Response
The **`json()`** static method of the Response interface returns a Response that contains the provided JSON data as body, and a Content-Type header which is set to application/json. The response status, status message, and additional headers can also be set. [MDN Reference](https://developer.mozilla.org/docs/Web/API/Response/json_static)
json
(const posts: Page<Post>posts); // { records, page, perPage, total, totalPages, hasNext, hasPrevious }
}
  • page and perPage take numbers, or text from a query string. Missing, null, or empty, they mean the first page and the default size. Anything that isn’t a whole number from 1 throws TypeError.
  • Pages sort by the Relation’s order, then by the primary key, so records that tie keep their pages between requests.
  • A page past the last has no records, but still has the real total.
  • includes() preloads the page’s associations, with one more query per association.
  • On D1, the count, the records, and their preloads take one round trip. On Postgres they run one after another.
  • A Relation with its own limit or offset throws IncompatibleRelation.

Skipping the count

Pass count: false when you only need to know whether there’s a next page:

import { class PostPost } from "./models/post";

const { const records: Post[]records, const hasNext: booleanhasNext } = await class PostPost.order<typeof Post>(this: typeof Post, spec: SafeSql | OrderSpec<Post>): Relation<Post, ScopesOf<typeof Post>>
Sorts by each key in turn (`{ createdAt: "desc" }`, or `{ role: { direction: "asc", nulls: "last" } }`), or by an `sql` fragment expression. Repeated calls append.
order
({
publishedAt?: OrderDirection | {
    direction: OrderDirection;
    nulls?: NullsPlacement;
} | undefined
publishedAt
: "desc" }).
Relation<Post, ScopesOf<typeof Post>>.paginate(options: PageOptions & {
    count: false;
}): Promise<CountlessPage<Post>> (+2 overloads)
One page of records and its total, by this Relation's order or the primary key, in one round trip (#100). With `count: false`, no `COUNT`: the page has no total.
paginate
({
PageOptions.page?: string | number | null | undefined
The page, from 1: a number, or untrusted text such as a query string's `"2"`.
page
: 2,
PageOptions.perPage?: string | number | null | undefined
Records on a page, a number or text as `page` is.
perPage
: 2,
count: false
`false` skips the `COUNT`: the page has no `total` or `totalPages`.
count
: false,
});

Counting reads every matching row, and D1 bills each row it reads. Without it, the page has no total or totalPages.

Setting the page size

Set perPage and maxPerPage on your app’s base class to change the default size and its cap:

import { Model } from "@forestfuture/active-record";
import { schema } from "../db/schema";

export abstract class ApplicationRecord extends Model {
  static override schema = schema;

  static override perPage = 20; // when paginate() isn't given a perPage
  static override maxPerPage = 50; // a larger perPage is capped at this
}

By default, a page holds 25 records, and none holds more than 100.

Last updated on