Answer a paged query
Read one Page of a sorted, filtered list, with the sort keys and filters a caller may use declared beside the query.
What you build
A query that answers one Page of a list: one bounded slice of the result, with its position and the total count. The query carries a PageRequest and answers Page<T>; a PageDeclaration beside it says what a caller may sort and filter by. This guide follows the Proving Sample's ListProducts Feature.
Nothing a caller sends becomes SQL
Sort keys and filter fields are declared by name, and the read maps each name to your own column or expression. A sort key or filter the declaration does not name is a validation Error, and a filter's value only ever reaches the database as a parameter.
When to use it
For any read that returns a list. A read of one item is an ordinary query that answers it or the Business Area's not-found Error.
Steps
Declare the query and what it may be sorted and filtered by
The declaration names the sort keys, each filter field with its value type and the operators it accepts, and the largest page a caller may ask for. Paging says what it accepts and what it defaults to.
internal sealed record ListProductsQuery(PageRequest Page) : Query<Page<ProductListItemDto>>
{
/// <summary>What a caller may sort and filter the product list by.</summary>
public static readonly PageDeclaration Paging = new(
sorts: ["name", "created"],
filters:
[
FilterField.Of<string>("name", FilterOperator.Equal, FilterOperator.Contains),
FilterField.Of<Guid>("category", FilterOperator.Equal),
FilterField.Of<DateTimeOffset>("created", FilterOperator.GreaterThanOrEqual),
],
maxSize: 50
);
}Check the request against the declaration
AllowedBy runs in the pipeline's one validation step, so its Errors come back with any others. Keep the NotNull: a null request adds nothing.
internal sealed class ListProductsQueryValidator : AbstractValidator<ListProductsQuery>
{
public ListProductsQueryValidator() =>
RuleFor(query => query.Page)
.Cascade(CascadeMode.Stop)
.NotNull()
.AllowedBy(ListProductsQuery.Paging);
}Read the Page
Write the read's SQL once, in a static PageSql: the select of the items and the count of the same selection. Map each sort key to a column, name a unique tie-breaker with ThenBy, and test each declared filter's parameter for null, as Reading a Page describes.
internal sealed class ListProductsQueryHandler(IReadConnectionFactory connections)
: QueryHandler<ListProductsQuery, Page<ProductListItemDto>>
{
// The Feature writes its WHERE, the tenant the lease bound included, since plain SQL reads no query filter; each
// declared filter arrives as @{field}_{operator}, null when not asked for.
private const string From = """
from catalog.products p
where p.tenant = current_setting('pouryakit.tenant')
and not p.is_deleted
and (@name_eq is null or p.name = @name_eq)
and (@name_contains is null or strpos(p.name, @name_contains) > 0)
and (@category_eq is null or p.category_id = @category_eq)
and (@created_gte is null or p.created_at >= @created_gte)
""";
private static readonly PageSql Sql = new PageSql(
ListProductsQuery.Paging,
select: $"select p.id, p.category_id as categoryid, p.name {From}",
count: $"select count(*) {From}"
)
.OrderBy("name", "p.name")
.OrderBy("created", "p.created_at")
.ThenBy("p.id");
public override async Task<Result<Page<ProductListItemDto>>> Handle(
ListProductsQuery query,
CancellationToken cancellationToken
)
{
await using var connection = await connections.OpenAsync(cancellationToken);
return await connection.QueryPageAsync<ProductListItemDto>(
Sql,
query.Page,
cancellationToken: cancellationToken
);
}
}Catalog is tenant-scoped and its products soft-deletable, so the read holds its rows to the tenant and leaves removed products out itself: plain SQL reads no query filter. Reading gives the predicates such a read states. To read the Page through EF Core instead, see Reading a Page and Dapper or an EF Core Page.
Bind it from the query string
The endpoint takes an HttpPageRequest parameter and passes its Request to the query, so the page, its size, its sort and its filters arrive from the caller's query string. Never read paging parameters by hand. A Page over HTTP shows ListProducts's endpoint, the query string it reads and the body it answers.
What the Kit guarantees
| Guarantee | Stated in |
|---|---|
| What a declaration accepts, and the Errors a request outside it gets | Paging |
| How the Dapper read binds filters and counts the total | Reading a Page |
| The tenant predicate a tenant-scoped read must state | Reading |
| How the query string binds, and what a malformed one answers | A Page over HTTP |