Pagination, Filtering and Sorting

# OFFSET — page numbers, a total count, jump anywhere
GET /problems?page=2&limit=20
{ "items": [...], "page": 2, "limit": 20, "total": 137 }

SELECT * FROM problems ORDER BY created_at DESC LIMIT 20 OFFSET 20;

#   + numbered pages, "showing 21-40 of 137"
#   - a row inserted at the top while reading shifts everything, so
#     page 2 repeats an item from page 1
#   - OFFSET 100000 makes the database count and discard 100,000 rows

# CURSOR — keyset, stable, and fast at any depth
GET /problems?limit=20&cursor=eyJpZCI6NDIsInRzIjoi...

SELECT * FROM problems
WHERE (created_at, id) < (:last_created_at, :last_id)   -- id breaks ties
ORDER BY created_at DESC, id DESC
LIMIT 21;                                -- one extra: is there more?

#   + no drift, constant cost at any depth
#   - no page numbers, no jumping, and a total is a separate query

# Choosing: an admin table people navigate by page -> offset.
# An infinite feed, or a table with millions of rows -> cursor.

# The cursor must be opaque (base64 of the sort key), so its shape
# can change later — and it must encode the sort, or changing the
# sort mid-pagination returns nonsense.

GET /problems?topic=arrays&difficulty=easy&difficulty=medium
              &sort=-created_at&q=two+sum&page=1&limit=20

# Conventions worth adopting:
#   repeat a key for multiple values   difficulty=easy&difficulty=medium
#   a leading minus for descending     sort=-created_at
#   q for free text
#   snake_case for field names, matching the response

# NEVER interpolate a sort column into SQL
order = request.query_params["sort"]
query = f"SELECT * FROM problems ORDER BY {order}"     # injection

# Allow-list it, always
SORTABLE = {"created_at": Problem.created_at, "title": Problem.title,
            "difficulty": Problem.difficulty}
field, desc = parse_sort(request)          # "-created_at" -> ("created_at", True)
col = SORTABLE.get(field)
if col is None:
    raise AppError(422, "bad_sort", f"Cannot sort by {field}")

# Cap the page size, or someone requests limit=100000
limit = min(int(params.get("limit", 20)), 100)

# Every sortable column needs an index, and the index must match the
# ORDER BY exactly — including the tiebreaker:
CREATE INDEX ix_problems_created_id ON problems (created_at DESC, id DESC);

# Counts are expensive on large tables. Options, in order:
#   omit the total and return has_more (cheapest, usually enough)
#   an approximate count from statistics
#   a cached count, refreshed periodically

# And on the client: filters belong in the URL (see the React
# track), so a filtered view is shareable and survives a refresh —
# which also means the query key and the URL stay in step.

// 1. The client filters what the server already filtered.
//    Fetching everything and filtering in JavaScript works for 50
//    rows and fails at 50,000 — and it means the data was sent.
//    Filter on the server; the client only requests.

// 2. Page state in the URL, not in component state — otherwise a
//    refresh drops the user back to page 1 and a shared link shows
//    something different.
const page = Number(searchParams.get('page') ?? 1)

// 3. Reset the page when a filter changes, or the user lands on an
//    out-of-range page with an empty list that looks like a bug.

// 4. Handle an out-of-range page explicitly rather than returning an
//    empty array that renders as "no results":
if page > total_pages and total > 0:
    raise AppError(404, "page_out_of_range", "That page does not exist")

// 5. Keep the previous page visible while the next loads, or every
//    page change flickers through a skeleton (placeholderData in
//    TanStack Query).

// 6. Agree what an empty result means: no data at all, or nothing
//    matching the filter? They deserve different messages, and only
//    the client can tell if it knows whether filters are active.