---
title: Ordering and pagination
description: "Sort a query’s records, and split them into pages."
seo:
  description: "Sort Active Record queries with order, reorder, and inOrderOf, place NULLs the same on D1 and Postgres, and paginate with a page total or without a count."
---

## 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 })`                             |

| Prop | Type | Default | Description |
| - | - | - | - |
| `page?` | `number \| string \| null` | `1` | The page to read. Text from a query string works as it is. |
| `perPage?` | `number \| string \| null` | `25` | Records on each page, up to maxPerPage. |
| `count?` | `boolean` | `true` | Whether to count every matching record for total and totalPages. |

## The Model

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

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

export class Post extends ApplicationRecord {}
```

```ts schema.ts
// 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"]> {}
}
```

```sql D1
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
);
```

```sql Postgres
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`:

```ts
import { column, sql } from "@forestfuture/active-record";
import { Post } from "./models/post";

const newest = await Post.order({ publishedAt: "desc", title: "asc" }).toArray();

Post.order({ publishedAt: { direction: "desc", nulls: "last" } }); // unpublished last, on both
Post.order(sql`lower(${column("title")})`);
Post.order({ title: "asc" }).reorder({ createdAt: "desc" }); // replaces the order
Post.order({ createdAt: "desc" }).limit(20).offset(40); // page 3
```

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

:::note
On D1, NULLs sort first in ascending order and last in descending order. Postgres does the opposite. Place them with `nulls: "first"` or `nulls: "last"` to sort them the same on both.
:::

## Ordering by a list of values

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

```ts
import { Post } from "./models/post";

const byStatus = await Post.inOrderOf("status", ["published", "draft"]).toArray(); // only those
const everyPost = await Post.inOrderOf("status", ["published"], { filter: false }).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:

```ts
import { Post } from "./models/post";

export async function listPosts(request: Request): Promise<Response> {
  const page = new URL(request.url).searchParams.get("page");
  const posts = await Post.order({ publishedAt: "desc" }).paginate({ page, perPage: 20 });
  return Response.json(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](/d1#sending-queries-in-one-round-trip). On Postgres they run [one after another](/postgresql#reads-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:

```ts
import { Post } from "./models/post";

const { records, hasNext } = await Post.order({ publishedAt: "desc" }).paginate({
  page: 2,
  perPage: 2,
  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:

```ts title="src/models/application-record.ts"
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.

:::note
D1 limits neither the rows a query returns nor the size of its response. `maxPerPage` keeps one request from reading, and paying for, too many rows, within a Worker’s 128 MB of memory and a query’s 30 seconds.
:::

> **Compared with Rails**
>
> `inOrderOf` is Rails’ `in_order_of`, `filter: false` included. Rails writes the list into a `CASE`, one bound value per item. Here the list is one bound value, so it fits D1’s limit of 100.

> **Beyond Rails**
>
> Rails leaves pagination to gems such as Kaminari and Pagy. Active Record builds it in, and on D1 sends the count and the page together.
