1. Resolution of component-typed properties
2. Yielding component-typed properties
   1. TG version 2.0.0
3. Entity-typed yields
   1. Entity-typed into entity-typed
      1. Top-level query
      2. Source query
   2. Entity-typed into primitive
      1. Top-level query
      2. Source query
   3. Primitive into entity-typed
      1. Top-level query
      2. Source query
   4. Special case: Existence subqueries
   5. Special case: Scalar subqueries
4. Source queries
5. Values and parameters
   1. Interpretation of nulls
      1. Example
6. (Order by | Group by) key
   1. Order by
   2. Group by
7. Id-only queries
   1. Ordering in id-only queries
8. caseWhen
   1. Specify an explicit type for caseWhen with null in all branches
9. Other topics

When a component-typed property is used as an operand (e.g., comparison condition, yield operand), it has to be resolved against the source of the query. Since a property is a single-valued (scalar) operand, and a component type may have multiple components, a component-typed property implicitly represents one of its components.

As of TG version 2.0.0, the following implicit representations are used:

• Money - sub-property amount.
• RichText - sub-property searchText.

For example, the following yields can be used interchangeably:

yield().prop("price") // implicitly resolved to "price.amount"
yield().prop("price.amount")

This section describes how to use component-typed properties in yields, both as yield operands and aliases.

The treatment of component-typed properties as yield operands is the same as in other contexts. Refer to the section about resolution of component-typed properties.

Support for component-typed properties in yield aliases is limited. Such properties can be used standalone as a yield alias only in a source query. In all other kinds of queries, component sub-properties must be yielded explicitly.

Yielding a component-typed property directly is supported for:

• Money - sub-property amount is implicitly yielded instead.

For the sake of demonstration, assume the following entity:

class Invoice extends AbstractEntity {
  @IsProperty
  @MapTo
  RichText comment;

  @IsProperty
  @MapTo
  Money fee;
}

Properties comment and fee are modelled with component types, which means they cannot be directly yielded in a top-level query. Instead, each component sub-property must be yielded explicitly.

// Invalid query
q1 = select(Invoice.class)
  // equivalent to yield().prop("comment.searchText").as("comment")
  .yield().prop("comment").as("comment")
  // equivalent to yield().prop("fee.amount").as("fee")
  .yield().prop("fee").as("fee")
  .model();

// Valid query
q2 = select(Invoice.class)

  .yield().prop("comment.formattedText").as("comment.formattedText")
  .yield().prop("comment.coreText").as("comment.coreText")
  // Yielding searchText is optional
  .yield().prop("comment.searchText").as("comment.searchText")

  .yield().prop("fee.amount").as("fee.amount")

  .model();

Entities retrieved with query q2 will have properties comment and fee correctly initialised.

As described above, some component types have special support for being used as yield aliases directly. An outer query that uses a source query with such yields can refer to them via prop as is. I.e., property resolution works as expected in such cases.

sourceQ = select().
  yield().X.as("fee"). // (1)
  modelAsEntity(Invoice.class);

q = select(sourceQ).
  yield().beginExpr().prop("fee").mult().val(2).endExpr().as("fee.amount"). // (2)
  modelAsEntity(Invoice.class);

Source query sourceQ doesn't need to include .amount in the alias (1), and its enclosing query q can refer to fee (recall that prop("fee") will expand to prop("fee.amount")). The top-level query q, on the other hand, must specify the full component sub-property path (2) in the yield alias.

This limited form of support is useful for working with synthetic entities. Synthetic entity models are always interpreted as source queries, enabling the use of alias shortcuts described in this section.

Some properties are typed as entities — for example, Vehicle.model is typed as VehicleModel. In the object model such a property represents an entity, while in the database it is represented as a column that stores that entity's id. EQL unifies these two views: an entity-typed expression can act either as the entity it represents or as the underlying id, depending on context.

An entity-typed yield is a yield that:

• uses an entity-typed expression (e.g., select(Vehicle.class).yield().prop("model")).
• OR has a target that is an entity-typed property (e.g., .yield() ... .as("model").modelAsEntity(Vehicle.class)).

An entity-typed property can be yielded in two ways, which differ only in type:

yield().prop("model")     // entity-typed — represents a VehicleModel
yield().prop("model.id")  // primitive    — its id, a Long

What EQL does with an entity-typed yield depends on the type of the yield target and the kind of query. The sub-sections below enumerate the combinations that matter, across all query kinds: a top-level query, a source query, a scalar sub-query, and an existence sub-query (exists / notExists). Yielding a primitive expression into a primitive target is a trivial case and is not covered here.

The yielded expression and the target are both entity-typed.

Modelled as an entity, the target property receives the yielded entity:

select(Vehicle.class)
  .yield().prop("model").as("model")
  .yield().prop("replacedBy").as("replacedBy")
  .modelAsEntity(Vehicle.class)

Modelled as an aggregate, the yielded entity appears under its alias as an entity instance:

// Each result row exposes `x` as a VehicleModel:
select(Vehicle.class).yield().prop("model").as("x").modelAsAggregate()

A query whose single yield is an entity returns those referenced entities — see Id-only queries:

// The models of all vehicles, as VehicleModel entities:
select(Vehicle.class).yield().prop("model").modelAsEntity(VehicleModel.class)

In an aggregate-modelled source query, the yield target inherits the type of the yielded expression. Enclosing queries can then reference entity-typed properties of the source query.

// Vehicle.model : VehicleModel => srcQry.m : VehicleModel
var srcQry = select(Vehicle.class).yield().prop("model").as("m").modelAsAggregate();

select(srcQry)
  .where().prop("m.make").isNotNull()   // navigate through the yielded entity
  .yield().prop("m").as("m")            // or yield it as a whole
  .modelAsAggregate()

For a source query whose result type is an entity type, each entity-typed yielded expression must match the declared type of the yield's target.

var srcQry = select(Vehicle.class)
  .yield().prop("model").as("model")        // Valid: VehicleModel -> VehicleModel
  // Vehicle.replacedBy : Vehicle
  .yield().prop("model").as("replacedBy")   // Invalid: VehicleModel -> Vehicle
  .modelAsEntity(Vehicle.class);

select(srcQry) ...

The yielded expression is entity-typed but the target has a primitive type (only Long makes sense). Validity of this form of yields depends on the query kind and shape.

In top-level queries, yielding an entity-typed expression into a primitive property is valid only in id-only queries.

If an entity ID is truly desired, it must be yielded explicitly:

yield.prop("model.id").as("x")

When the yield target has a primitve type, the yielded expression is interpreted as an entity id:

class Container extends AbstractEntity<...> {
  @IsProperty
  Long modelId;
}

var srcQry = select(...)
            .yield().prop("model").as("modelId")
            .modelAsEntity(Container.class)

select(srcQry) ...

In an aggregate-modelled source query, yield prop("model.id") to expose the id as a number.

.yield().prop("model.id").as("x")
.modelAsAggregate()

The yielded expression is a primitive and the target is entity-typed. Only an id (a Long) is meaningful — it is interpreted as an entity whose type is that of the yield target.

In top-level queries, yielding a primitive-typed expression into an entity-typed property is invalid.

If you only have an id but know the desired entity type, use a subquery that selects by that id.

select(Vehicle.class)
  // Invalid: primitive -> entity-typed
  .yield().prop("id").as("replacedBy")
  // Valid: entity-typed -> entity-typed
  .yield().model(select(TgVehicle.class).where().prop("id").eq().extProp("id").model()).as("replacedBy")
  .modelAsEntity(Vehicle.class)

In contrast to top-level queries, it is permitted to yield a primitive id into an entity-typed property.

The following all yield a VehicleModel reference into the entity-typed model:

var srcQry = select(...)
            .yield().prop("model").as("model")     // Type: VehicleModel
            .yield().prop("model.id").as("model")  // Type: VehicleModel
            .yield().val(someModelId).as("model")  // Type: VehicleModel
            .modelAsEntity(Vehicle.class)

select(srcQry) ...

An aggregate-modelled source query has no declared entity-typed target, so a yielded number stays a number.

.yield().prop("model.id").as("model") // Type: Long
.modelAsAggregate()

The yield is ignored — only the presence of matching rows matters, so no yield is needed:

.where().exists(select(Vehicle.class) ... .model())

A scalar subquery is an expression, and its type is determined as follows:

• If the subquery has an explicit result type, specified by modelAsEntity(T), then its type is T.
• Otherwise modelAsPrimitive() is used, and its type is equal to that of the first yield.

// Type: VehicleModel
var subQry = select(Vehicle.class).where() ... .yield().prop("model").modelAsEntity(VehicleModel.class);

...where().prop("model").eq().model(subQry) // Can be compared with entity-typed expressions
...where().prop("id").eq().model(subQry)    // Can be compared with primitive-typed expressions
...yield().model(subQry).as("model")        // Can be yielded into an entity-typed property

// Type: VehicleModel
select(Vehicle.class).where() ... .yield().prop("model").modelAsPrimitive();

// Type: String
select(Vehicle.class).where() ... .yield().prop("key").modelAsPrimitive();

A source query is a query that is used as a source of another query.

Example:

select(
  // source query 1
  select(InventoryItem.class).yield().prop("price").as("cost").modelAsEntity(ReTransaction.class),
  // source query 2
  select(ReturnReceipt.class).yield().prop("refund").as("cost").modelAsEntity(ReTransaction.class)
)
.where().prop("cost").ge().val(100)
.modelAsEntity(ReTransaction.class)

If two or more source queries are used, they form a union.

A query that contains source queries is subject to the following constraints:

• If two or more source queries are used, they must have the same number of yields and use the same set of yield aliases.

  The following examples demonstrate invalid queries:

  1. Different numbers of yields.

     select(
       // 2 yields
       select(InventoryItem.class)
         .yield().prop("price").as("cost")
         .yield().prop("id").as("id")
         .modelAsEntity(ReTransaction.class),
       // 1 yield
       select(ReturnReceipt.class)
         .yield().prop("refund").as("cost")
         .modelAsEntity(ReTransaction.class)
     )

  2. Different sets of yield aliases.

     select(
       // { "cost", "id" }
       select(InventoryItem.class)
         .yield().prop("price").as("cost")
         .yield().prop("id").as("id")
         .modelAsEntity(ReTransaction.class),
       // { "expense", "id" }
       select(ReturnReceipt.class)
         .yield().prop("price").as("expense")
         .yield().prop("id").as("id")
         .modelAsEntity(ReTransaction.class)
     )

EQL queries can contain literal values and parameters, expressed through the Fluent API methods val and param.

The set of types that can be used for values and parameters is constrained.

The following rules to apply to component types:

• Money -- expanded to amount.
• RichText -- cannot be used. One of its components should be used instead.

There are two ways of specying a literal value in a query: val and iVal. They differ in their interpretation of null values.

• When val(null) is used, the result is determined by the rules of the enclosing predicate.
• When iVal(null) is used, it denotes that the value is to be ignored, and that the enclosing predicate is to be ignored as well.

For example, consider the following comparison predicate:

prop("id").eq().val(null)

It will produce the following SQL:

id <> NULL

<> is a standard SQL equality operator, which evaluates to null if either of the operands is null (or both are null). This is also true when both operands are expressions other than NULL (e.g., a <> b, where both or one of the columns contains null). On the other hand, if iVal is used, the comparison predicate will be ignored alltogether as if the original expression never contained it.

This kind of transformation applies to most types of predicates:

• ComparisonPredicate = ComparisonOperand ComparisonOperator ComparisonOperand

  If at least one of the operands is null, the predicate is ignored.

• MembershipPredicate = ComparisonOperand MembershipOperator MembershipOperand

  If the scalar operand is null, the predicate is ignored.

• QuantifiedComparisonPredicate = ComparisonOperand ComparisonOperator QuantifiedOperand

  If the scalar operand is null, the predicate is ignored.

• LikePredicate = ComparisonOperand LikeOperator ComparisonOperand

  If at least one of the operands is null, the predicate is ignored.

• "Exists" predicates in SingleConditionPredicate.

  Does not apply.

• UnaryPredicate = ComparisonOperand UnaryComparisonOperator

  If operand is null, the predicate is ignored.

The same rules apply to parameters, which can be specified with param and iParam.

EQL does not provide an operator with the semantics of PostgreSQL's IS [NOT] DISTINCT FROM.

Here is an example where iVal is useful:

WorkActivity wa = ...

select(WorkActivity.class).where()
  .prop("id").neq().iVal(wa)
  .and().prop("startDate").gt().val(date)
  ...

Assume that wa != null, and recall that entity-typed values are interpreted as their IDs, so in this case iVal(wa) is the same as iVal(wa.getId()).

With this query, we want to select all Work Activities that are distinct from wa. If we were to use val(wa), and wa.getId() == null (new, not yet persisted Work Activity), then the whole where clause would evaluate to false, yielding no results, which is not desired. Therefore, iVal(wa) is used -- if wa.getId() == null, then the query becomes equivalent to:

select(WorkActivity.class).where()
  .prop("startDate").gt().val(date)
  ...

The orderBy and groupBy clauses have special interpretation of composite keys. Recall that EQL interprets a composite key as a string -- a concatenation of its key members. In these clauses, however, a composite key is recursively expanded into its key members.

For example, consider the following entity type:

class Request
  @CompositeKeyMember(1)
  User user;
  @CompositeKeyMember(2)
  Date date;

class User
  String key;

The special interpretation of composite keys would result in the following transformation:

select(Request.class)
.orderBy().prop("key").asc()
=>
select(Request.class)
.orderBy().prop("user.key").asc().prop("date").asc()

The ascending/descending order is inherited by each key member.

A transformation of similar nature applies to groupBy.

This transformation also applies to property paths that end with a composite key.

This transformation enables preservation of ordering semantics for composite keys. The semantics of ordering by each key member is different from that of ordering by a concatenated representation. For example, consider the following dataset for Request:

Column user.key is a shorthand so that all data can be represented by a single table.

1. The result of ordering by each key member in ascending order:

2. The result of ordering by the concatenated representation in ascending order (for demonstration, column key contains the concatenated representation):

Here, the later date 02/02/2024 comes before the earlier date 12/12/2023, despite the ascending order. This is because the order is based on a string representation (02 < 12).

Note that a specific date format was used, but its choice is arbitrary. What is important is that the date format affects the ordering.

The expanded form of groupBy is equivalent to the original form if and only if the concatenated representation of a composite key preserves its uniqueness property.

For example, when all key members have type String, their concatenation preserves the uniqueness property.

However, if there is a Date key member, and the date format is not precise enough, the uniqueness property may be lost. For example:

An id-only query is a top-level query where:

1. the result is a persistent entity type;
2. there is a single yield which is either unaliased or has alias id.

There are 2 kinds of id-only queries:

1. Local id-only query.

   This is a query whose single yield is the property id.

   The most common form is illustrated by the following example:

   from(select(Vehicle.class)
        .where() ... // Optionally
        .orderBy() ... // Optionally
        .groupBy() ... // Optionally
        .model())
     .with(fetchIdOnly(Vehicle.class))
     .model()

   This query has no explicit yields, hence an implicit yieldAll is assumed, which is then narrowed down to just id due to the fetch model.

2. Foreign id-only query.

   This is a query whose single explicit yield is an entity-typed property.

   The most common form is illustrated by the following example:

   select(Vehicle.class)
        .where() ... // Optionally
        .orderBy() ... // Optionally
        .groupBy() ... // Optionally
        .yield().prop("model")
        .modelAsEntity(VehicleModel.class)

   Here, Vehicle.model : VehicleModel.

Ordering in foreign id-only queries is inherently unreliable due to certain internal transformationis performed by EQL.

Example 1: Unreliability of internal orderBy in foreign id-only queries:

var query = select(Vehicle.class)
           .orderBy().prop("key").desc()
           .yield().prop("model")
           .modelAsEntity(VehicleModel.class);
// Transforms into:
select(VehicleModel.class).where().prop("id").in().model(query).model();

External orderBy is not affected by this, but has its own disadvantages.

Example 2: The subtlety of external orderBy in foreign id-only queries:

var query = select(Vehicle.class)
           .yield().prop("model")
           .modelAsEntity(VehicleModel.class);
var orderBy = orderBy().prop("key").desc().model();

from(query).with(orderBy).model()
// Transforms into:
from(select(VehicleModel.class).where().prop("id").in().model(query).model())
  .with(orderBy)
  .model();

Note that the external orderBy above becomes invalid after transformation, as it attempts to use Vehicle.key, but the source of the transformed query is VehicleModel. This is a limitation of EQL, which can only be overcome by manually transforming the original query.

Example 3: Foreign id-only query transformed into a join with a reliable ordering.

select(TgVehicleModel.class).as("m")
  .join(TgVehicle.class).as("v")
  .on().prop("m.id").eq().prop("v.model")
  .orderBy().prop("v.key").desc()
  .model()

If all branches may return null, one of endAs* constructs should be used to indicate the desired type of the caseWhen expression. Plain end() should not be used, as EQL will then attribute the null type to the caseWhen expression, which may result in an error during SQL execution.

// Always null type
caseWhen().prop("amount").eq().val(54).then().val(null).otherwise().val(null).end();
// Null type if `x` is null
caseWhen().prop("amount").eq().val(54).then().val(x).otherwise().val(null).end();
// Null type if the parameter value is null
caseWhen().prop("amount").eq().val(54).then().param(p1).end();

// Integer type
caseWhen().prop("amount").eq().val(54).then().param(p1).endAsInt();

• EQL crit-condition.
• Auto-escaping of special characters in like clauses.