Topic

Pagination & filtering

Definition

Pagination bounds a list response to a page of a requested size, and filtering narrows that page to rows matching client-supplied criteria, both expressed as validated query parameters that a service applies to its database query.

In simpler words

Instead of returning every ticket at once, the API returns one page at a time and can narrow that page by status or search text.

Center this on a list query DTO (a ListTicketsQueryDto) and query-builder pagination in a list service.

Open official example (opens in new tab)

After this you can

Read the { data, meta } contract for a list endpoint
Explain why limit needs an upper bound
Add an optional filter safely with parameterized conditions

The query shape and the meta it returns

Definition

ListTicketsQueryDto declares page, limit, an optional status enum, and an optional free-text q, each validated and coerced from query-string input, and TicketsService.list returns both the requested page of data and a meta object describing page, limit, total, and totalPages.

In simpler words

Clients ask for a page and a size; the server answers with that page plus enough numbers to build pager UI without a second request.

limit is capped at 100 with @Max — an unbounded limit would let a client request the entire table in one response.

meta.total comes from qb.getManyAndCount(), which runs the count and the row fetch together.

Paginated meta contract

{
  "data": [ /* up to 20 tickets */ ],
  "meta": { "page": 1, "limit": 20, "total": 57, "totalPages": 3 }
}

totalPages = Math.ceil(total / limit), so the frontend can render pager controls without recomputing it.

Building the filtered query safely

Definition

Filtering should apply constraints through parameterized query-builder conditions rather than concatenated strings, and each filter should be optional so its absence does not change the query’s meaning.

In simpler words

Every filter is an andWhere clause added only when the client actually sent that field.

status filters with an exact match; q performs a case-insensitive ILIKE search across title and description — both use bound parameters (:status, :q), never string interpolation.

Filters and pagination combine: skip/take is applied after the where clauses, so total and totalPages reflect the filtered set, not the whole table.

Optional filters

if (query.status) {
  qb.andWhere('ticket.status = :status', { status: query.status });
}
if (query.q) {
  qb.andWhere('(ticket.title ILIKE :q OR ticket.description ILIKE :q)', {
    q: `%${query.q}%`,
  });
}

Bound parameters avoid SQL injection even though q is arbitrary client text.

Keep in mind

Always cap limit — an unbounded list endpoint is a performance and abuse risk.
Make every filter optional and additive; absence should mean “no constraint”, not an error.
Compute total/totalPages from the same filtered query, not the unfiltered table.

Test

Check your understanding

At least 10 questions — mix of concept, syntax, practical, and logic. Score ≥80% (enforced by the API) to save progress.

Checking your session…

15 questions · concept 5 · syntax 3 · practical 4 · logic 3

1. How should list endpoints paginate?

Concept

Return every row and let React slice pagesUnbounded limit so clients can download the tableConcatenate filters into raw SQL stringsServer-side page/limit with meta including total and totalPages

2. What does meta.total reflect?

Syntax

Always the full table size, ignoring any filtersThe filtered result count, not necessarily the whole unfiltered tableThe number of rows on the current pageThe configured limit value for the request

3. Why cap limit?

Practical

So the total count is always accurateSo offset pagination can compute skip correctlyProtect the database from huge scans and payloadsSo the JSON response is always sorted

4. How should filters be applied?

Logic

By concatenating the query values into the WHERE clause stringBy fetching all rows and filtering them in JavaScriptParameterized query builder conditions from a validated query DTOBy passing the raw query object straight to repo.query()

5. What belongs in ListTicketsQueryDto?

Concept

The data and meta response envelope fieldsThe full ticket entity including its relationsThe offset and cursor plus the SQL ORDER BY clauseValidated page, limit, and filter fields like status/q

6. Why not paginate only on the client?

Practical

Client pagination cannot compute totalPagesThe browser cannot slice arrays without a libraryShipping entire tables is slow, heavy, and unsafe at scaleClient pagination breaks the data and meta contract

7. What is totalPages derived from?

Syntax

page and limit (page multiplied by limit)total and page (total divided by page)The length of the returned data arraytotal and limit (ceiling division)

8. Should soft-deleted rows affect totals?

Logic

Count them but hide them from data, so totals look largerInclude them only in the count on the last pageExclude them from the count but keep them in the dataIf hidden from data, they must also be excluded from count queries

9. What does page mean?

Concept

Which slice of the filtered result to returnHow many rows to return per requestThe total number of pages availableThe number of rows to skip before returning

10. Which response shape matches the course list craft?

Practical

{ data: [...] } with no meta object{ items, page, total } as flat top-level fields{ data, meta: { offset, cursor, hasMore } }{ data, meta: { page, limit, total, totalPages } }

11. What does this compute for page=3, limit=20?

Conceptadvanced

repo.find({
  take: limit,
  skip: (page - 1) * limit,
});

Skip 60 rows and take 20Skip 3 rows and take 20Take all rows then slice in JSSkip 40 rows and take 20 — the third page

12. What is the risk in this list query?

Syntaxintermediate

const limit = Number(req.query.limit);
return repo.find({ take: limit });

An unbounded limit lets a client request the whole table; clamp it to a maxNumber() cannot parse query stringstake must always be 10find ignores take without skip

13. What does total represent for building meta?

Practicalintermediate

const [data, total] = await repo.findAndCount({ take, skip });

The number of rows on the current pageThe count of rows matching the same filter, used for total and totalPagesThe number of columns returnedThe max_connections of the pool

14. Why is meta.total wrong here?

Logicadvanced

const data = await repo.find({ where: { status }, take, skip });
const total = await repo.count();

count() must be awaited twicecount() ignores the status filter, so total does not match the filtered datafind already returns the totaltotal should use take, not a count

15. How should this status filter be written safely?

Conceptadvanced

qb.where('t.status = ' + status);

qb.where("t.status = " + Number(status))qb.filter(status) after getManyqb.where("t.status = :status", { status }) using a bound parameterLeave it; QueryBuilder escapes concatenation

Checking your session…