David Lastrucci

Posted on Apr 16

Filtering, sorting, and pagination with the fluent query builder

#delphi #orm #database #opensource

So far in this series we have mapped entities, validated them, and performed basic CRUD. But real applications rarely load all records from a table. You need to search, filter, sort, and paginate.

Trysil provides a fluent API for this: TTFilterBuilder<T>. You chain method calls to build a filter, then pass it to Select<T>. The builder generates parameterized SQL behind the scenes — no string concatenation, no injection risk.

Basic filtering

uses
  Trysil.Filter;

var
  LBuilder: TTFilterBuilder<TTContact>;
  LFilter: TTFilter;
  LContacts: TTList<TTContact>;
begin
  LContacts := TTList<TTContact>.Create;
  try
    LBuilder := LContext.CreateFilterBuilder<TTContact>();
    try
      LFilter := LBuilder
        .Where('Lastname').Equal('Smith')
        .Build;

      LContext.Select<TTContact>(LContacts, LFilter);
    finally
      LBuilder.Free;
    end;

    for LContact in LContacts do
      WriteLn(Format('%s %s', [LContact.Firstname, LContact.Lastname]));
  finally
    LContacts.Free;
  end;
end;

The flow is always the same:

Create a builder with LContext.CreateFilterBuilder<T>
Chain conditions with .Where, .AndWhere, .OrWhere
Apply an operator (.Equal, .Like, .Greater, etc.)
Call .Build to get a TTFilter
Pass the filter to LContext.Select<T>

Available operators

Method	SQL
`.Equal(value)`	`= :param`
`.NotEqual(value)`	`<> :param`
`.Greater(value)`	`> :param`
`.GreaterOrEqual(value)`	`>= :param`
`.Less(value)`	`< :param`
`.LessOrEqual(value)`	`<= :param`
`.Like(pattern)`	`LIKE :param`
`.NotLike(pattern)`	`NOT LIKE :param`
`.IsNull`	`IS NULL`
`.IsNotNull`	`IS NOT NULL`

Combining conditions

Use .AndWhere and .OrWhere to combine multiple conditions:

LFilter := LBuilder
  .Where('Lastname').Equal('Smith')
  .AndWhere('Email').IsNotNull
  .Build;

LFilter := LBuilder
  .Where('City').Equal('Rome')
  .OrWhere('City').Equal('Milan')
  .Build;

Pattern matching with LIKE

LFilter := LBuilder
  .Where('Lastname').Like('Sm%')
  .Build;

Standard SQL wildcards apply: % matches any sequence of characters, _ matches a single character.

Sorting

LFilter := LBuilder
  .Where('Country').Equal('Italy')
  .OrderByAsc('Lastname')
  .Build;

You can chain multiple sort clauses:

LFilter := LBuilder
  .Where('Country').Equal('Italy')
  .OrderByAsc('Lastname')
  .OrderByAsc('Firstname')
  .Build;

For descending order, use .OrderByDesc:

LFilter := LBuilder
  .OrderByDesc('Price')
  .Build;

Note: you can sort without filtering — just skip the .Where call.

Pagination

For large datasets, load data in pages:

const
  PageSize = 20;
var
  LPage: Integer;
begin
  LPage := 3; // zero-based page index

  LFilter := LBuilder
    .OrderByAsc('Lastname')
    .Limit(PageSize)
    .Offset(LPage * PageSize)
    .Build;

  LContext.Select<TTContact>(LContacts, LFilter);
end;

.Limit(n) sets the maximum number of rows to return. .Offset(n) skips the first n rows. Combined with sorting, this gives you clean, predictable pagination.

Counting records

Sometimes you need the total count (for example, to display "Page 3 of 12"):

var
  LCount: Integer;
begin
  LFilter := LBuilder
    .Where('Country').Equal('Italy')
    .Build;

  LCount := LContext.SelectCount<TTContact>(LFilter);
end;

SelectCount<T> returns the number of matching rows without loading the entities into memory.

A complete example

Here is a realistic search function that combines filtering, sorting, pagination, and counting:

procedure TContactService.Search(
  const ASearchText: String;
  const APage: Integer;
  const APageSize: Integer;
  const AContacts: TTList<TTContact>;
  out ATotalCount: Integer);
var
  LBuilder: TTFilterBuilder<TTContact>;
  LFilter: TTFilter;
begin
  LBuilder := FContext.CreateFilterBuilder<TTContact>();
  try
    if not ASearchText.IsEmpty then
      LBuilder
        .Where('Lastname').Like(Format('%s%%', [ASearchText]))
        .OrWhere('Firstname').Like(Format('%s%%', [ASearchText]));

    LBuilder
      .OrderByAsc('Lastname')
      .OrderByAsc('Firstname');

    LFilter := LBuilder.Build;
    ATotalCount := FContext.SelectCount<TTContact>(LFilter);

    LBuilder
      .Limit(APageSize)
      .Offset(APage * APageSize);

    LFilter := LBuilder.Build;
    FContext.Select<TTContact>(AContacts, LFilter);
  finally
    LBuilder.Free;
  end;
end;

What is next

We can now search, sort, and paginate our data with a clean fluent API. In the next article we will tackle relations and lazy loading — how to model parent-child relationships and load related entities on demand.

Trysil is open-source and available on GitHub. Stars and feedback are always appreciated!

DEV Community