Home GitHub

Sorting and Pagination

Last updated

This page shows how orderBy(), limit(), offset(), and total() combine into a paginated query.

orderBy()

MySQLPool.table("users").orderBy("created_at", "DESC").orderBy("id");
// ... ORDER BY `created_at` DESC, `id` ASC
Rule Detail
Default direction ASC
Case The direction is uppercased first, so "desc" equals "DESC"
Invalid direction Logs console.error("Invalid order direction:", ...) and skips the call without throwing
Column name Backtick-quoted when it contains no .
Multiple calls Joined with commas in call order

limit() and offset()

const page = 3;
const perPage = 20;

MySQLPool
  .table("users")
  .limit(perPage)
  .offset((page - 1) * perPage);
// ... LIMIT 20 OFFSET 40

Both numbers are written into SQL without placeholders. Pass only integers computed in your code; convert request parameters with Number.parseInt and check the range first.

total()

total() wraps the query in a subquery and uses COUNT(*) OVER() so every row carries the total number of matching rows (src/MySQLPool.ts:254-256), removing the extra COUNT(*) query from pagination.

interface UserRow {
  total: number;
  id: number;
  name: string;
}

const rows = await MySQLPool
  .table("users")
  .select("id", "name")
  .where("status", "active")
  .total()
  .orderBy("id", "DESC")
  .limit(10)
  .offset(0)
  .get<UserRow>();

const total = rows[0]?.total ?? 0;

Generated SQL:

SELECT COUNT(*) OVER() AS total, data.*
FROM (SELECT `id`, `name` FROM `users` WHERE `status` = ?) AS data
ORDER BY `id` DESC LIMIT 10 OFFSET 0
Caveat Detail
MySQL version Window functions need MySQL 8.0 or higher
Sort column ORDER BY applies to the outer query, so only the subquery's output column names work; orderBy("users.id") fails because the outer query has no users
Column clash If the inner query already returns a total column, the outer result has two
Page out of range With no rows there is no total, so fall back with ?? 0
中文