Pagination Filtering Sorting

Why this matters

APIs often return long lists: users, orders, logs, products. Pagination, filtering, and sorting keep responses fast, predictable, and cheap. As an API Engineer, you will:

• Expose list endpoints that scale to millions of records.
• Protect databases with safe limits and indexes.
• Give clients precise controls to fetch exactly what they need.

Concept explained simply

Think of a giant spreadsheet. Pagination chooses which rows to show. Filtering hides rows that do not match a rule. Sorting orders the rows so the user can navigate predictably.

Mental model

• Always sort first, then paginate. If you paginate first, results jump around.
• Use deterministic tie-breakers (e.g., sort by created_at then id) so order is stable.
• Bound responses with defaults and maximums to protect your system.

Key patterns

Pagination styles

• Offset + limit: ?offset=40&limit=20. Simple, great for small pages; may slow down on huge offsets.
• Page + size: ?page=3&size=20. Similar to offset (offset = (page-1)*size).
• Cursor (keyset): ?after=<cursor>. Uses the last row's sort key(s) for fast, stable pagination on large datasets.

Filtering

• Simple equality: ?status=shipped
• Ranges: ?created_from=2025-01-01&created_to=2025-01-31
• Sets: ?status=in:shipped,cancelled (or repeatable params)
• Text search (prefix): ?q=report (document limits and behavior)
• Whitelist allowed filters to avoid unsafe queries.

Sorting

• Single or multiple fields: ?sort=created_at,-id (minus means descending).
• Always include a unique tie-breaker (e.g., id) in the sort list.
• Document default sort and supported fields.

Response design

Return consistent envelopes with data, meta, and navigation hints.

{
  "data": [ {"id": 101, "name": "..."}, {"id": 102, "name": "..."} ],
  "meta": {
    "total": 12450,
    "limit": 20,
    "offset": 40,
    "sort": ["created_at:desc", "id:desc"],
    "filters": {"status": "active"}
  },
  "links": {
    "self": "/users?offset=40&limit=20&status=active&sort=-created_at,-id",
    "next": "/users?offset=60&limit=20&status=active&sort=-created_at,-id",
    "prev": "/users?offset=20&limit=20&status=active&sort=-created_at,-id"
  }
}

{
  "data": [ {"id": 98765, "created_at": "2025-01-15T10:30:00Z"}, ... ],
  "meta": {
    "size": 25,
    "sort": ["created_at:desc", "id:desc"],
    "has_more": true
  },
  "pageInfo": {
    "endCursor": "eyJjcmVhdGVkX2F0IjoiMjAyNS0wMS0xNVQxMDozMDowMFoiLCJpZCI6OTg3NjV9"  
  },
  "links": {
    "self": "/orders?size=25&sort=-created_at,-id",
    "next": "/orders?size=25&sort=-created_at,-id&after=eyJjcm..."
  }
}

query {
  orders(first: 25, after: "cursor") {
    edges { cursor node { id created_at } }
    pageInfo { endCursor hasNextPage }
  }
}

Worked examples

1. Users list, offset
   Request: GET /users?offset=0&limit=50&sort=-created_at,-id&status=active
   Why: Simple admin screens; easy jumping to pages; ensure limit has a max.
2. Orders, cursor
   Request: GET /orders?size=25&sort=-created_at,-id&after=CURSOR
   Why: Infinite scroll with large tables; avoids slow large offsets.
3. Products with filters and multi-sort
   Request: GET /products?category=in:books,electronics&price_to=50&sort=price,-rating,-id&page=2&size=20
   Why: Merchandising needs fine control by price then rating; tie-break by id.

Performance & safety defaults

• Default limit: 20–100; hard max to prevent abuse.
• Always specify an ORDER BY matching an index.
• Add tie-breaker to ORDER BY (e.g., id).
• Whitelist sortable/filterable fields.
• Validate parameter types and ranges.
• For cursor pagination, generate opaque, signed cursors.

Exercises

Complete the tasks below, then compare with the solutions. These mirror the graded exercises further down.

• I kept limits and max limits in mind.
• I sorted deterministically and used a tie-breaker.
• I documented filters and their behavior.

Common mistakes and self-check

• Paginating before sorting — Causes records to shift across pages. Fix: Always ORDER BY before LIMIT/OFFSET.
• No tie-breaker — Equal values lead to unstable pages. Fix: Append unique id to sort list.
• Unbounded limits — Risk of large responses. Fix: default and max limits.
• Arbitrary filters — Leads to slow full scans. Fix: whitelist fields and validate types.
• Leaky cursors — Predictable or editable. Fix: opaque, signed cursors with expiration if needed.

Practical projects

• Build a catalog API with offset pagination and two filters, deploy locally, and test with page sizes 10/50/100.
• Add cursor pagination to a feed endpoint with a signed cursor containing sort keys.
• Implement a reporting endpoint that supports multiple sort fields and exports the first 1,000 items safely in chunks.

Learning path

1. HTTP and JSON conventions
2. Resource modeling
3. Pagination, filtering, sorting (this lesson)
4. Caching and ETags
5. Rate limiting and quotas
6. Monitoring and SLOs

Who this is for

• API Engineers and Backend Developers creating list endpoints.
• Full-stack Engineers integrating data-heavy UIs.
• Data/Platform Engineers building internal APIs for teams.

Prerequisites

• Comfort with HTTP requests and JSON.
• Basic SQL (ORDER BY, WHERE, LIMIT/OFFSET).
• Familiarity with indexes and performance basics.

Next steps

• Add request validation so unsupported sort/filter fields return clear errors.
• Document defaults, max limits, and examples in your API docs.
• Practice converting an offset-based endpoint to cursor-based for a large dataset.

Mini challenge

Design the query parameters and response envelope for a /logs endpoint that supports:

• Cursor pagination by timestamp DESC, id DESC.
• Filters: level (in: info, warn, error), service, timestamp_from, timestamp_to.
• Default size 50, max 200.

Sketch the JSON for data, meta, pageInfo, and links. Ensure opaque cursors and a deterministic sort.

Ready to test yourself?

Try the Quick Test below. Everyone can take it for free; only logged-in users will have their progress saved.