Sorting and Pagination

To get rows in order, such as the fields of a form by position, list the columns in orderBy:

const fields = await db.field.where({ formId }).orderBy("position").select("id", "label")

Columns sort in ascending order. To sort in another direction, pass an object of columns with their directions:

const forms = await db.form.orderBy({ published: "desc", name: "asc" }).select("id", "name")

A direction is "asc" or "desc", optionally with the placement of NULL, such as "desc nulls last". Without it, NULL comes last in ascending order and first in descending order.

Column keys and objects can be mixed, such as orderBy({ published: "desc" }, "name"), and each orderBy adds its columns after those of earlier calls.

The column and the direction can come from variables, such as the sort of a list that the user chose:

const forms = await db.form.orderBy({ [sort.column]: sort.direction }, "id").select("id", "name")

Expressions

To sort by expressions, such as the length of a name, pass a callback that returns one or an array of them:

import { desc } from "kern-orm"

const forms = await db.form
  .orderBy(form => [desc(form.name.length()), form.name])
  .select("id", "name")

An expression sorts in ascending order. Wrap it in desc to sort in descending order, or in orderBy with a direction, such as orderBy(form.name, "desc nulls last").

For an order that operators do not cover, such as by the bytes of text rather than by the rules of the locale, return a sql fragment:

import { sql } from "kern-orm"

const forms = await db.form.orderBy(form => sql`${form.name} COLLATE "C"`).select("id", "name")

Selected fields

To sort by a field you select, such as a computed score, take it from the second parameter of the callback:

import { desc, sql } from "kern-orm"
import * as t from "kern-orm/types"

const forms = await db.form
  .select("id", "name")
  .select(form => ({
    score: sql(t.real)`similarity(${form.name}, ${search})`,
  }))
  .orderBy((form, selected) => desc(selected.score))

The rows are sorted by the same values that the records get.

After pluck or get, the second parameter is the expression of the value.

To sort groups of rows by their aggregates, see Grouping.

When you select a field only under a condition and otherwise fill it with literal, sort by it under the same condition:

import { literal } from "kern-orm"

const fields = await db.field
  .select(field => ({
    id: field.id,
    hint: withHints ? field.hint : literal(null),
  }))
  .orderBy((field, selected) => (withHints ? selected.hint : []))

Related records

To sort by an aggregate of related records, such as forms by the number of their fields, call it on the relation in the callback:

import { desc } from "kern-orm"

const forms = await db.form.orderBy(form => desc(form.fields.count())).select("id", "name")

The callback receives the relations of the table beside its columns. Sort the same way by whether related records exist, such as desc(form.fields.exists()), by a value of a related record, such as field.form.get("name"), or by the value of any other query of one value.

To also get the number, select it and sort by the selected field, so that the statement counts the fields once:

import { desc } from "kern-orm"

const forms = await db.form
  .select("id", "name")
  .select(form => ({
    fieldCount: form.fields.count(),
  }))
  .orderBy((form, selected) => desc(selected.fieldCount))
// forms: { id: string; name: string; fieldCount: number }[]

Pagination

To get one page of rows, call limit and offset after sorting:

const page = await db.form.orderBy("name", "id").limit(20).offset(40).select("id", "name")

Sort by columns that tell every row apart, such as id last, so that pages neither repeat nor skip rows.

To get a page together with the number of rows of all pages, select both in $select, and call count on the query without limit and offset:

const published = db.form.where({ published: true })

const page = await db.$select({
  forms: published.orderBy("name", "id").limit(20).offset(40).select("id", "name"),
  total: published.count(),
})
// page: { forms: { id: string; name: string }[]; total: number }

To get the first row in order, call take after sorting; with offset before it, take gets the row at that position:

const second = await db.form.orderBy("name", "id").offset(1).select("id", "name").take()

To sort and paginate the related records of each row, see Related Records.