Skip to content

JOIN Queries

Trysil supports declarative multi-table SELECT queries using [TJoin] attributes. Join entities are read-only -- Insert, Update, and Delete raise ETException.

TJoinKind

TJoinKind = (Inner, Left, Right);

A scoped enum defined in Trysil.Attributes.pas that specifies the type of SQL JOIN.

TJoinAttribute

Apply one or more [TJoin] attributes to the entity class. Three overloads are available.

Simple JOIN

Join another table using columns from the FROM table:

[TTable('Orders')]
[TSequence('OrdersID')]
[TJoin(TJoinKind.Inner, 'Customers', 'CustomerID', 'ID')]
TOrderReport = class

Parameters:

  1. JoinKind -- Inner, Left, or Right
  2. TableName -- the table to join (also used as the alias)
  3. SourceColumnName -- column from the FROM table
  4. TargetColumnName -- column from the joined table

Generated SQL:

SELECT ... FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.ID

Self-JOIN with Alias

When joining the same table twice, provide an explicit alias:

[TTable('Movimenti')]
[TSequence('MovimentiID')]
[TJoin(TJoinKind.Inner, 'PianoDeiConti', 'ContoDare', 'ContoDareID', 'ID')]
[TJoin(TJoinKind.Inner, 'PianoDeiConti', 'ContoAvere', 'ContoAvereID', 'ID')]
TMovimentoContabile = class

Parameters:

  1. JoinKind -- join type
  2. TableName -- the table to join
  3. Alias -- unique alias for this join instance
  4. SourceColumnName -- column from the FROM table
  5. TargetColumnName -- column from the joined table

Generated SQL:

SELECT ... FROM Movimenti
INNER JOIN PianoDeiConti ContoDare ON Movimenti.ContoDareID = ContoDare.ID
INNER JOIN PianoDeiConti ContoAvere ON Movimenti.ContoAvereID = ContoAvere.ID

Chained JOIN

Join a table using a column from a previous join instead of the FROM table:

[TTable('Orders')]
[TSequence('OrdersID')]
[TJoin(TJoinKind.Inner, 'Customers', 'Customers', 'CustomerID', 'ID')]
[TJoin(TJoinKind.Left, 'Countries', 'Countries', 'Customers', 'CountryID', 'ID')]
TOrderWithCountry = class

Parameters:

  1. JoinKind -- join type
  2. TableName -- the table to join
  3. Alias -- unique alias
  4. SourceTableOrAlias -- table name or alias of a previous join
  5. SourceColumnName -- column from the source table/alias
  6. TargetColumnName -- column from the joined table

Generated SQL:

SELECT ... FROM Orders
INNER JOIN Customers Customers ON Orders.CustomerID = Customers.ID
LEFT JOIN Countries Countries ON Customers.CountryID = Countries.ID

Mapping Joined Columns

Use the two-parameter overload of [TColumn] to map a field to a column from a joined table:

[TColumn('Customers', 'CompanyName')]
FCustomerName: String;
  • First parameter: the alias of the joined table (must match the alias from [TJoin])
  • Second parameter: the column name in that table

Fields without a table parameter are mapped to the FROM table as usual:

[TColumn('OrderDate')]
FOrderDate: TDateTime;

Complete Example

unit Model.OrderReport;

interface

uses
  Trysil.Types,
  Trysil.Attributes;

{$WARN UNKNOWN_CUSTOM_ATTRIBUTE ERROR}

type

  [TTable('Orders')]
  [TSequence('OrdersID')]
  [TJoin(TJoinKind.Inner, 'Customers', 'CustomerID', 'ID')]
  TOrderReport = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TColumn('OrderDate')]
    FOrderDate: TDateTime;

    [TColumn('Amount')]
    FAmount: Currency;

    [TColumn('Customers', 'CompanyName')]
    FCustomerName: String;

    [TVersionColumn]
    [TColumn('VersionID')]
    FVersionID: TTVersion;
  public
    property ID: TTPrimaryKey read FID;
    property OrderDate: TDateTime read FOrderDate;
    property Amount: Currency read FAmount;
    property CustomerName: String read FCustomerName;
  end;

implementation

end.

Generated SQL:

SELECT Orders.ID AS Orders_ID,
       Orders.OrderDate AS Orders_OrderDate,
       Orders.Amount AS Orders_Amount,
       Customers.CompanyName AS Customers_CompanyName,
       Orders.VersionID AS Orders_VersionID
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.ID
ORDER BY Orders.ID

Querying Join Entities

Join entities are queried exactly like single-table entities:

LOrders := LContext.CreateEntityList<TOrderReport>();
try
  // Load all
  LContext.SelectAll<TOrderReport>(LOrders);

  // With filter
  LContext.Select<TOrderReport>(LOrders, LFilter);

  // Count
  LCount := LContext.SelectCount<TOrderReport>(LFilter);
finally
  LOrders.Free;
end;

Behavior

Read-Only

Join entities cannot be written. Calling Insert<T>, Update<T>, or Delete<T> on a join entity raises ETException with the message "Join entities are read-only: Insert, Update, and Delete are not supported."

Column Alias Length

A joined column is selected as Alias_Column, and that name is also the key of the metadata and the columnName a client filters by. Identifier length limits differ: Oracle up to 12.1 takes 30 bytes - bytes, not characters, so an accented letter counts two on an AL32UTF8 database - Firebird up to 3.0 and InterBase 31 characters, the others 63 or more. DocumentiRighe_PrezzoUnitarioNetto is 34, so the same mapping worked on SQL Server and was refused on Oracle, with nothing to say so until it ran there.

An alias longer than 30 bytes in UTF-8 is therefore shortened to its first 23 bytes, cut between two UTF-16 code units, plus a hash of the whole name: DocumentiRighe_PrezzoUn_A3F19C. The head keeps the alias recognisable in a query, and the hash keeps two columns apart that share their first 23 bytes - PrezzoUnitarioNetto and PrezzoUnitarioLordo would otherwise become the same column twice in one select list.

An alias that fits in 30 bytes is left exactly as it was. One that fits in 30 characters but not in 30 bytes is shortened too: DocumentiRighe_QuantitàResidua is 30 characters and 31 bytes.

A client filtering over HTTP can also name a joined column by the JSON name of its member, the one it sees in every response and in MetadataToJSon<T>. That name does not depend on the alias, so it does not change when the alias is shortened. And a mapping with names long enough to be shortened now works on every engine, where before it worked on some.

Identity Map

The identity map is bypassed for join entities. The same primary key can appear in multiple result rows with different joined data, so caching by PK would produce incorrect results.

Soft Delete

When the FROM-table entity has a [TDeletedAt] column, the automatic DeletedAt IS NULL filter is qualified with the FROM table name to avoid ambiguity:

-- Without joins
WHERE DeletedAt IS NULL

-- With joins
WHERE Orders.DeletedAt IS NULL

Backward Compatibility

All join-related changes are behind HasJoins checks. Existing single-table entities are completely unaffected.

Limitations (v1)

  • TWhereClause on join entities requires manually qualified column names.

TTFilterBuilder\<T> is no longer one of them

Until 2.0.0 the builder did not resolve join aliases, and a filtered query on a join entity had to be written by hand with TTFilter.Create. It resolves them now, in the expression form and in the fluent string form alike - see Filtering join entities.

See Also