Filtering¶

Trysil provides two ways to filter queries: the TTFilter record for manual construction and TTFilterBuilder<T> for a fluent, type-safe API. Both are defined in Trysil.Filter.pas.

TTFilter Record¶

Simple WHERE Clause¶

LFilter := TTFilter.Create('Lastname = :Lastname');
LFilter.AddParameter('Lastname', ftWideString, 'Smith');
LContext.Select<TPerson>(LPersons, LFilter);

With Max Records and Ordering¶

LFilter := TTFilter.Create('Active = 1', 20, 'Lastname ASC');
LContext.Select<TPerson>(LPersons, LFilter);

This limits the result to 20 records ordered by Lastname.

With Pagination¶

LFilter := TTFilter.Create('Active = 1', 0, 20, 'Lastname ASC');
LContext.Select<TPerson>(LPersons, LFilter);

Parameters: WHERE clause, start offset, limit, ORDER BY.

Adding Parameters¶

LFilter := TTFilter.Create('Age >= :MinAge AND Age <= :MaxAge');
LFilter.AddParameter('MinAge', ftInteger, 18);
LFilter.AddParameter('MaxAge', ftInteger, 65);

Always use named parameters (:ParamName) instead of concatenating values into the WHERE string. This prevents SQL injection and ensures correct type handling.

TTFilter.Empty¶

Use TTFilter.Empty when no filter is needed. This is equivalent to selecting all records:

LContext.Select<TPerson>(LPersons, TTFilter.Empty);

SelectAll<T> internally uses TTFilter.Empty.

TTFilterBuilder\<T> (Fluent API)¶

The preferred way to build filters. The builder resolves column metadata from the entity type at construction time, ensuring column names and data types are valid.

Obtain a builder via TTContext.CreateFilterBuilder<T>:

var LBuilder := LContext.CreateFilterBuilder<TPerson>();
try
  var LFilter := LBuilder
    .Where('Lastname').Equal('Smith')
    .AndWhere('Firstname').Like('J%')
    .OrderByAsc('Lastname')
    .Limit(20)
    .Offset(0)
    .Build;

  LContext.Select<TPerson>(LPersons, LFilter);
finally
  LBuilder.Free;
end;

Warning

The builder is an object that must be freed after use. Call Free once you have obtained the TTFilter via Build.

Available Operators¶

Method	SQL Operator	Example
`Equal(value)`	`=`	`.Where('Status').Equal(1)`
`NotEqual(value)`	`<>`	`.Where('Status').NotEqual(0)`
`Greater(value)`	`>`	`.Where('Age').Greater(18)`
`GreaterOrEqual(value)`	`>=`	`.Where('Age').GreaterOrEqual(18)`
`Less(value)`	`<`	`.Where('Age').Less(65)`
`LessOrEqual(value)`	`<=`	`.Where('Age').LessOrEqual(65)`
`Like(pattern)`	`LIKE`	`.Where('Name').Like('J%')`
`NotLike(pattern)`	`NOT LIKE`	`.Where('Name').NotLike('Test%')`
`IsNull`	`IS NULL`	`.Where('Email').IsNull`
`IsNotNull`	`IS NOT NULL`	`.Where('Email').IsNotNull`

Combining Conditions¶

LBuilder
  .Where('Lastname').Equal('Smith')         // first condition
  .AndWhere('Active').Equal(True)           // AND
  .OrWhere('Role').Equal('Admin')           // OR

Where starts the first condition.
AndWhere adds a condition with AND.
OrWhere adds a condition with OR.

Conditions are combined in declaration order. The fluent chain does not place parentheses — for grouped conditions like (A or B) and C, use the expression API below.

Expression Filters (Grouping)¶

Because SQL binds AND tighter than OR, a flat Where/OrWhere/AndWhere chain produces the wrong grouping for "(Admin or Manager) and Active":

// WRONG GROUPING — flat chain
LBuilder
  .Where('Role').Equal('Admin')
  .OrWhere('Role').Equal('Manager')
  .AndWhere('Active').Equal(True);
// SQL: Role = :p0 OR Role = :p1 AND Active = :p2
// reads as: Admin OR (Manager AND Active)  -- not what you meant

The expression API in Trysil.Filter.Expression.pas fixes this. It overloads operators on a TTProperty value to build a TTExpression that carries its own parentheses, and feeds it to Where / AndWhere / OrWhere.

Declaring properties¶

A TTProperty wraps a column name:

uses Trysil.Filter.Expression;

var
  LRole: TTProperty;
  LActive: TTProperty;
begin
  LRole := TTProperty.Create('Role');
  LActive := TTProperty.Create('Active');

Building grouped expressions¶

Combine comparisons with and, or, not — each combinator wraps its operands in parentheses, so the grouping is explicit and correct:

var LFilter := LContext.CreateFilterBuilder<TUser>()
  .Where((LRole = 'Admin') or (LRole = 'Manager'))
  .AndWhere(LActive = True)
  .Build;
// SQL: (Role = :p0 OR Role = :p1) AND Active = :p2

Parenthesize every comparison

Delphi binds and / or tighter than the comparison operators, so each comparison must be wrapped in parentheses: write (LRole = 'Admin') or (LRole = 'Manager'), never LRole = 'Admin' or LRole = 'Manager'.

Operators and methods on TTProperty¶

Expression	SQL
`LProp = value`	`= :p`
`LProp <> value`	`<> :p`
`LProp > value`	`> :p`
`LProp >= value`	`>= :p`
`LProp < value`	`< :p`
`LProp <= value`	`<= :p`
`LProp.Like(pattern)`	`LIKE :p`
`LProp.NotLike(pattern)`	`NOT LIKE :p`
`LProp.IsNull`	`IS NULL`
`LProp.IsNotNull`	`IS NOT NULL`
`LProp.Between(lo, hi)`	`BETWEEN :p AND :p`
`LProp.InValues([a, b, c])`	`IN (:p, :p, :p)`
`not (expr)`	`NOT (...)`

Ordering with a property¶

OrderByAsc and OrderByDesc also accept a TTProperty:

LBuilder
  .Where(LActive = True)
  .OrderByDesc(LRole);

Mixing with the fluent form¶

Both forms share the same parameter counter, so you can mix them in one builder without :pN collisions:

LBuilder
  .Where('Lastname').Equal('Smith')                     // fluent -> :p0
  .AndWhere((LRole = 'Admin') or (LRole = 'Manager'));  // expression -> :p1, :p2

Generated companion record¶

Instead of declaring TTProperty locals by hand, let the Trysil Expert generate a companion record next to each entity (enabled by default). For TCustomer it emits TCustomerProperties with one TTProperty per column, so column names are checked by the compiler at the call site:

var C := TCustomerProperties.Create;
LBuilder
  .Where((C.City = 'Roma') or (C.City = 'Milano'))
  .AndWhere(C.Age >= 18);

Join entities¶

For join entities, use the two-argument TTProperty.Create(Alias, Column). It qualifies the WHERE reference as Alias.Column and validates against the joined column's output alias Alias_Column:

var LCustomerName := TTProperty.Create('Customers', 'CompanyName');

var LFilter := LContext.CreateFilterBuilder<TOrderReport>()
  .Where(LCustomerName.Like('Acme%'))
  .Build;
// WHERE: Customers.CompanyName LIKE :p0

The alias matches the one in the [TColumn('Alias', 'Column')] attribute. For a column that comes from the FROM table, use the FROM table name as the alias (e.g. TTProperty.Create('Orders', 'ID')). This is the only filtering form that resolves join aliases — the fluent string form does not (see below).

Sorting and Pagination¶

LBuilder
  .OrderByAsc('Lastname')       // ORDER BY Lastname ASC
  .Limit(50)                    // LIMIT 50
  .Offset(100)                  // OFFSET 100

LBuilder
  .OrderByDesc('CreatedAt')     // ORDER BY CreatedAt DESC

Only one OrderBy call is active at a time. Calling OrderByAsc or OrderByDesc replaces any previous ordering.

Including Soft-Deleted Records¶

When an entity has a [TDeletedAt] column, all queries automatically exclude soft-deleted records by adding DeletedAt IS NULL to the WHERE clause. To include them:

Via TTFilterBuilder¶

var LFilter := LContext.CreateFilterBuilder<TArticle>()
  .Where('Title').Like('Draft%')
  .IncludeDeleted
  .Build;

LContext.Select<TArticle>(LArticles, LFilter);

Via TTFilter¶

LFilter := TTFilter.Create('Title LIKE :Title');
LFilter.AddParameter('Title', ftWideString, 'Draft%');
LFilter.IncludeDeleted := True;
LContext.Select<TArticle>(LArticles, LFilter);

See Entity Mapping — Soft Delete for how to set up change tracking attributes.

SelectCount¶

Count records matching a filter without loading entities:

var LBuilder := LContext.CreateFilterBuilder<TPerson>();
try
  var LFilter := LBuilder
    .Where('Active').Equal(True)
    .Build;

  LCount := LContext.SelectCount<TPerson>(LFilter);
finally
  LBuilder.Free;
end;

Filtering Join Entities¶

The expression API resolves join aliases via the two-argument TTProperty.Create(Alias, Column) — see Join entities above. This is the recommended way to filter join entities:

var LCustomerName := TTProperty.Create('Customers', 'CompanyName');
var LFilter := LContext.CreateFilterBuilder<TOrderReport>()
  .Where(LCustomerName.Like('Acme%'))
  .Build;
LContext.Select<TOrderReport>(LOrders, LFilter);

The fluent string form (.Where('Column')) does not qualify join aliases. As an alternative, TTFilter.Create with a manually written WHERE also works:

var LFilter := TTFilter.Create('Customers.CompanyName LIKE :Name');
LFilter.AddParameter('Name', ftWideString, 'Acme%');
LContext.Select<TOrderReport>(LOrders, LFilter);

Static Filters via Attributes¶

For filters that never change at runtime, use TWhereClause and TWhereClauseParameter attributes directly on the entity class:

[TTable('Users')]
[TWhereClause('Active = :Active')]
[TWhereClauseParameter('Active', True)]
TActiveUser = class

Every query on TActiveUser will automatically include WHERE Active = True. These parameters are compile-time constants and cannot carry runtime values.

See Entity Mapping for details.