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 }) |
page?number | string | null
The page to read. Text from a query string works as it is.
number | string | null1perPage?number | string | null
Records on each page, up to maxPerPage.
number | string | null25count?boolean
Whether to count every matching record for total and totalPages.
booleantrueThe 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): ColumnReferenceNames 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): ColumnReferenceNames 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 3Calling 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 | undefinedKeep 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: falseto keep the rest, after them. - Values are cast as
where’s are, andnullplaces 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 throwsIncompatibleRelation. - 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 operationPromise<Response> {
const const page: string | nullpage = new var URL: new (url: string | URL, base?: string | URL) => URLThe **`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: stringThe **`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: URLSearchParamsThe **`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 | nullThe **`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 | undefinedThe page, from 1: a number, or untrusted text such as a query string's `"2"`.page, PageOptions.perPage?: string | number | null | undefinedRecords 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): ResponseThe **`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 }
}pageandperPagetake 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 throwsTypeError.- 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
limitoroffsetthrowsIncompatibleRelation.
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 | undefinedThe page, from 1: a number, or untrusted text such as a query string's `"2"`.page: 2,
PageOptions.perPage?: string | number | null | undefinedRecords 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.