Guides / Select

Order and paginate

Sort results and limit the rows a query returns.

The examples use the users table from Build a SELECT.

Order rows

Wrap ordering terms in orderBy():

import { desc, from, orderBy, select } from "qubu"

const ordered = select({ id: users.id, name: users.name }, from(users), orderBy(desc(users.name)))

Use asc() or desc() for each term. The selected dialect controls the identifier quoting and any dialect-specific ordering syntax.

Paginate results

Choose fetchFirst() or offset() for pagination:

import { desc, eq, fetchFirst, from, offset, orderBy, select, where } from "qubu"

const page = select(
  { id: users.id, name: users.name },
  from(users),
  where(eq(users.id, 7)),
  orderBy(desc(users.name)),
  offset(20),
  fetchFirst(20),
)

The rendered clauses follow SQL order: FROM comes before WHERE, followed by ORDER BY and pagination. The active dialect decides whether pagination uses standard FETCH syntax or a driver-specific LIMIT form.

Make the limit optional

Pair a pagination clause with omit when the row bound is optional at runtime:

import { fetchFirst, omit } from "qubu"

declare const pageSize: number | undefined

const page = select(
  { id: users.id, name: users.name },
  from(users),
  pageSize === undefined ? omit : fetchFirst(pageSize),
)

When pageSize is undefined, the query emits no pagination. Because the row bound may be absent, conditional pagination does not narrow the query's cardinality. An unconditional fetchFirst(1) retains its existing zero-or-one inference.