Sorting and Pagination

Last updated


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

orderBy()


  1. MySQLPool.table("users").orderBy("created_at", "DESC").orderBy("id");
  2. // ... ORDER BY `created_at` DESC, `id` ASC

RuleDetail
Default directionASC
CaseThe direction is uppercased first, so "desc" equals "DESC"
Column nameBacktick-quoted when it contains no .
Multiple callsJoined with commas in call order
Invalid directionAny value other than ASC/DESC prints Invalid order direction: through console.error and adds no sort for that call

limit() and offset()


  1. const page = 3;
  2. const perPage = 20;
  3. MySQLPool
  4. .table("users")
  5. .limit(perPage)
  6. .offset((page - 1) * perPage);
  7. // ... LIMIT 20 OFFSET 40

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.
  1. interface UserRow {
  2. total: number;
  3. id: number;
  4. name: string;
  5. }
  6. const rows = await MySQLPool
  7. .table("users")
  8. .select("id", "name")
  9. .where("status", "active")
  10. .total()
  11. .orderBy("id", "DESC")
  12. .limit(10)
  13. .offset(0)
  14. .get<UserRow>();
  15. const total = rows[0]?.total ?? 0;

Generated SQL:
  1. SELECT COUNT(*) OVER() AS total, data.*
  2. FROM (SELECT `id`, `name` FROM `users` WHERE `status` = ?) AS data
  3. ORDER BY `id` DESC LIMIT 10 OFFSET 0


中文