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
.Buildto get aTTFilter - 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!
