Filtering and Sorting Data
Filtering and Sorting Data
Welcome to the final lesson of the course! You already have a PostgreSQL-backed ticket collection, a static statistics route, and a dynamic ticket-detail route. Now you’ll make the collection endpoint much more useful by allowing clients to filter, search, and sort the returned tickets.
You’ll define a strongly typed contract between the route and repository, validate query parameters before trusting them, construct a flexible parameterized WHERE clause, and safely add sorting rules to SQL. You’ll also return the number of matched tickets through the X-Total-Count response header.
Previously, you learned how Express distinguishes /stats from /:ticketId, how route parameters flow into parameterized PostgreSQL queries, and how missing tickets become focused 404 responses. In this lesson, the same route-to-repository flow expands from one ticket ID to several optional collection controls.
Understanding Query Parameters
Query parameters add optional information to a URL after a ?. Multiple parameters are separated with &.
These requests all target the same GET /api/tickets route. The query string changes how the collection is selected or ordered without creating a separate endpoint for every possible combination.
This endpoint supports five parameters:
-
statusfilters byopen,in_progress, orclosed. It must match one of the values already defined byticketStatuses. -
priorityfilters bylow,medium, orhigh. It shares the same source of truth as the ticket domain model. -
qperforms a case-insensitive search across ticket titles and descriptions. The route trims surrounding whitespace before passing the term to PostgreSQL. -
sortByselects eithercreatedAtorpriority. When omitted, it defaults tocreatedAt. -
orderselectsascordesc. When omitted, it defaults todesc.
All values from request.query begin at the application boundary as untrusted input. TypeScript types describe what the repository accepts, but the route must first verify that the actual runtime strings are supported.
Defining the Collection Contract
The collection-query types live in src/domain/ticket.ts. They describe the exact options accepted by the repository and the result it returns.
-
TicketSortFieldallows only the two sorting modes implemented by the repository. A genericstringwould incorrectly suggest that fields such as"title"or"status"are supported. -
SortOrderrestricts direction to"asc"or"desc". This makes every repository call explicit and prevents unsupported values from reaching SQL construction.
The same file combines filters and sorting into one options object.
-
q,status, andpriorityare optional because clients may use any combination of filters or omit them entirely. The repository therefore needs to build only the conditions that are currently present. -
sortByandorderare required because the route always supplies defaults. The repository never has to guess how results should be ordered. -
TicketListResultreturns both the mapped tickets and the number of matching rows. The route sendsitemsin the JSON body and exposestotalthrough a response header.
These types form a boundary between HTTP concerns and database concerns. The route turns untrusted query strings into validated options, while the repository receives only supported values.

