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" |
| Column name | Backtick-quoted when it contains no . |
| Multiple calls | Joined with commas in call order |
| Invalid direction | Any value other than ASC/DESC prints Invalid order direction: through console.error and adds no sort for that call |
limit() and offset()
- const page = 3;
- const perPage = 20;
- MySQLPool
- .table("users")
- .limit(perPage)
- .offset((page - 1) * perPage);
- // ... 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.- 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