Pagination Filtering Sorting

Who this is for

Backend engineers who build list endpoints (feeds, catalogs, admin lists) and need fast, stable, predictable responses with pagination, filtering, and sorting.

Prerequisites

• Comfortable with HTTP, JSON, and RESTful endpoints
• Basic SQL (SELECT, WHERE, ORDER BY, LIMIT/OFFSET) or equivalent ORM knowledge
• Understanding of indexes and primary keys

Why this matters

• Product lists: “Show 20 most recent orders, filter by status=paid, date last month, sort by created_at desc.”
• Activity feeds that do not shuffle when new items arrive.
• Admin dashboards that search and filter quickly without overloading the database.
• APIs that are predictable for clients and safe from unbounded queries.

Concept explained simply

Pagination controls how many items you return and which slice. Filtering narrows the set by conditions (status=paid). Sorting decides the order (created_at desc). Together, they shape fast, predictable API lists.

Mental model

Think of a long bookshelf. Filtering is choosing a shelf section (e.g., only “science”). Sorting is arranging books by publish date. Pagination is taking just one small stack at a time. Do these consistently and you avoid reshuffling or losing your place.

Design choices

Recommended parameter conventions

• Offset style: ?limit=20&offset=40 (cap limit, e.g., max 100)
• Page style (alternative): ?page=3&page_size=20
• Cursor style: ?limit=20&after=CURSOR (or before for backward)
• Sorting: Single param with comma-separated fields; prefix - for descending. Example: ?sort=-created_at,id
• Filtering: Keep it explicit. Examples: ?status=paid, ?created_at[gte]=2024-01-01, ?amount[lte]=100, repeated params for multi-value: ?status=paid&status=shipped

{
  "data": [ /* items */ ],
  "page": {"limit": 20, "offset": 40, "total": 1234},
  "sort": ["-created_at", "id"],
  "filters": {"status": ["paid", "shipped"]}
}
-- or cursor style --
{
  "data": [ /* items */ ],
  "page": {"limit": 20, "next": "CURSOR", "prev": null}
}

Worked examples

Example 1 — Offset pagination with deterministic sorting

Goal: Get paid orders, newest first, deterministic within same timestamp.

// Query params: ?limit=20&offset=40&sort=-created_at,id&status=paid
SELECT id, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id ASC
LIMIT $1 OFFSET $2;
// Always include a tiebreaker (id) for stable order

Example 2 — Cursor pagination for a feed

Cursor encodes the last seen sort keys: created_at and id. Sort is created_at DESC, id DESC.

// Decode cursor into (last_created_at, last_id)
SELECT id, created_at, body
FROM messages
WHERE (created_at, id) < ( $last_created_at, $last_id )
ORDER BY created_at DESC, id DESC
LIMIT $limit;
// Next cursor is the last row's (created_at, id) re-encoded

Example 3 — Combined filtering + sorting with indexes

List users registered in 2024, active only, sort by last_login desc, tie-break by id.

// Query params: ?active=true&registered_at[gte]=2024-01-01&registered_at[lt]=2025-01-01&sort=-last_login,id
SELECT id, email, active, registered_at, last_login
FROM users
WHERE active = true
  AND registered_at >= '2024-01-01'
  AND registered_at < '2025-01-01'
ORDER BY last_login DESC, id ASC
LIMIT $limit OFFSET $offset;
// Helpful index: (active, registered_at, last_login, id)

Performance tips

• Cap limit (e.g., default 20–50, max 100).
• Whitelist sortable fields; reject unknown ones.
• Reject unconstrained full scans (e.g., huge limits without filters on very large tables).
• For deep navigation, prefer cursor pagination to avoid large OFFSET costs.
• Return only needed columns to reduce payload size.

Common mistakes and self-check

• Missing tiebreaker in ORDER BY leads to flickering order. Self-check: Add , id as final key.
• Allowing any sort field. Self-check: Validate against a whitelist.
• Large OFFSET on huge tables. Self-check: Measure query plan; switch to cursor.
• Unbounded limit. Self-check: Enforce max limit on the server.
• Non-stable cursor (cursor missing sort keys). Self-check: Include all ORDER BY columns in the cursor.

Exercises

Do these in a sandbox or local project. They mirror the exercises below for reference and progress tracking.

1. Offset pagination with deterministic sorting: Build GET /orders supporting limit, offset, sort, and status filter. Include a tiebreaker in ORDER BY.
2. Cursor pagination for messages: Build GET /messages with limit and after cursor based on created_at DESC, id DESC.

• Limit is capped (e.g., max 100)
• Sorting fields are whitelisted and validated
• Stable ORDER BY with tie-breaker
• Filters validated and typed (bool/date/enum)
• Response includes page metadata (next cursor or total)

Practical projects

• Catalog API: Products list with category filter, price range filter, sort by popularity/price/name, both offset and cursor modes.
• Audit log feed: Cursor pagination by created_at,id, filters by actor and action type, forward and backward navigation.
• Admin Users: Advanced filters (active, role, created_at range), compound indexes, and server-side validation for safe sorting.

Learning path

• Before this: REST fundamentals, database indexing basics.
• This module: Pagination strategies, filter operators, safe sorting.
• Next: Caching list endpoints, search, rate limiting, and API reliability patterns.

Next steps

Take the quick test to check your understanding. Available to everyone; log in to save your progress.

Mini challenge

Extend an existing list endpoint to support multi-field sort (?sort=-created_at,id), add a tiebreaker, cap limit, and return a next cursor when using cursor mode. Validate sort fields against a whitelist.