NestJS + TypeORM — optional query pagination (pattern)

TL;DR

Optional pagination: if both page and limit are absent, return a plain array (legacy / small lists). If either is present, normalize inputs, cap limit, run offset pagination, return { items, pagination } with total from a separate count query. When LEFT JOIN + mapMany inflate rows, paginate IDs first (no joins), then re-fetch full graphs restricted to those IDs so LIMIT does not lie.

Canonical implementation today lives in backend-claim-dev (payment-request.helper.ts, api-response.interface.ts, list controllers).

Why this pattern

• One route supports table UIs (page / limit) and scripts that want the full list (omit both).
• Stable contract: paginated payload always includes items + pagination.page|limit|total|total_pages.
• Correct rows: TypeORM can dedupe after getMany(), but SQL LIMIT applies before that — so paged joins on one-to-many relations need a two-step fetch.

Response shapes

Not paginated (both params omitted after resolution):

• data: T[] (array only).

Paginated (at least one param present):

• data: PaginatedData<T>:

interface PaginationMeta {
  page: number;
  limit: number;
  total: number;
  total_pages: number;
}

interface PaginatedData<T> {
  items: T[];
  pagination: PaginationMeta;
}

Types in the reference repo: src/common/interfaces/api-response.interface.ts.

Query parameter rules

Helpers in reference repo:

• resolvePaymentRequestPagination(page?, limit?) — numeric / undefined after controller parsing.
• resolvePaymentRequestPaginationFromQuery(page?, limit?) — string query pair -> { page, limit } or undefined if both absent.
• resolvePaymentRequestPaginationFromQueryParams — tolerates string | number | null | ''.

All in src/helpers/payment-request.helper.ts.

Controller wiring (sketch)

1. Read page / limit from @Query().
2. listPagination = resolveFromQuery(...) — may be undefined.
3. Pass page / limit into the service only when defined (or pass the whole optional object).
4. Service returns either T[] or PaginatedData<T>; document which endpoints are which.

Example list entry: GET /api/v1/requests and role lists that forward listPagination into getListByPage in payment-request-fetch.service.ts.

Fetch layer: skip / limit / total

skip = (page - 1) * limit

1. Main query with limitClause + offsetClause when paginated (or two-phase path below).
2. Count query with the same whereClause (no joins needed unless count must reflect join semantics — reference counts on root entity only).

Reference: fetchPaymentRequest in src/helpers/payment-request.helper.ts — builds paginationResult with total_pages: ceil(total / limit).

Two-phase ID pagination (join + mapMany)

Problem: leftJoinAndMapMany multiplies rows; LIMIT 20 can return fewer than 20 parents after dedupe.

Detection: any configured subquery with useMap: true and mapOne: false (or equivalent "collection mapping" joins).

Algorithm:

1. Query A — same filters + order, select only primary key, LIMIT/OFFSET, no collection joins.
2. Collect IDs (preserve order from query A if needed; reference uses IN (...) with second query — order may follow orderClause on the second query).
3. Query B — WHERE id IN (:...ids) with full joins / maps, same order clause.

Reference: usePagedIdThenFetch branch inside fetchPaymentRequest when paginationResolved and mapManyJoins.length >= 1.

Porting checklist (another Anabatic Nest repo)

1. Add PaginationMeta + PaginatedData<T> (or align names with existing API standard).
2. Choose MAX_LIMIT and default page/limit when only one param is sent.
3. Implement resolvePagination returning undefined only when both params absent.
4. In the list service / repository:
   • branch paginated vs not;
   • run count for paginated;
   • if TypeORM relation maps multiply rows, implement ID-first pagination.
5. Align interceptor / envelope so clients can tell paginated vs array (query-param convention or meta).
6. Document OpenAPI: optional page / limit, max limit, and paginated response shape.

Reference implementation (repo paths)

Monorepo: backend-claim-dev

Related

• Claim hub: [[Projects/anabatic-claim/anabatic-claim]]
• Area: [[Areas/Anabatic/Anabatic]]
• Tech index: [[Resources/Tech/Tech]]
• NestJS overview (vault): [[Resources/Tech/NestJS/NestJS]]