EQL How To

1. How to translate WHERE X IN (SELECT ...) into EQL

SELECT * FROM ROTMAST WHERE ROTABLE_NO IN (SELECT ROTABLE_NO FROM STI_ROTABLE_STAT)

First, convert the SQL to use the EXISTS clause:

SELECT * FROM ROTMAST R WHERE EXISTS (SELECT * FROM STI_ROTABLE_STAT S WHERE S.ROTABLE_NO = R.ROTABLE_NO)

Then create an EQL sub-query:

EntityResultQueryModel<RotableEo> subQuery = 
      select(RotableEo.class).where().prop("rotable").eq().extProp("id").model();

Then create the main query:

EntityResultQueryModel<Rotable> rotableQuery = 
      select(Rotable.class).where().exists(subQuery).model();

Alternatively, but less efficiently:

EntityResultQueryModel<Rotable> subQuery = 
      select(RotableEo.class).yield().prop("rotable").modelAsEntity(Rotable.class);

EntityResultQueryModel<Rotable> mainQuery = 
      select(Rotable.class).where().prop("id").in(subQuery);

But, of course, our query rewritten with EXISTS should be more performant!

2. How to define calculated boolean properties that check the existence of something in the database

Consider an EQL model for a calculated boolean property in entity Person that determines whether some person is a "delegated certification manager" based on the presence of current delegations. Delegations are considered "current" if the current date/time (now) satisfies fromDate <= now <= toDate.

The most natural approach to building the required EQL model is to use a caseWhen expression. The main question here is what condition should the caseWhen be based on.

One possible answers is to check if the number of current delegations for a person is greater than zero:

static final ExpressionModel delegatedCertificationManager_ = expr()
            .caseWhen()
            .model(select(Delegation.class)
                   .where().prop("toPerson").eq().extProp("id")
                     .and().prop("fromDate").le().now()
                     .and().begin().prop("toDate").isNull().or().prop("toDate").ge().now().end().
                   yield().countAll().modelAsPrimitive()
             ).gt().val(0)
            .then().val(true)
            .otherwise().val(false).endAsBool().model();

Unfortunately, this is a very inefficient approach -- countAll is computationally intensive, and we don't really need the actual number of delegations. Instead, we're simply interested in the fact whether there is at least one delegation.

To avoid heavy computations in this case, countAll should be replaced with a test for existence of relevant delegations. Interestingly enough, the use of exists is a lot more precise and direct answer to the question of "existence". The model below utilises exists for the caseWhen condition -- it is both more concise (there is no comparison) and much faster to execute.

static final ExpressionModel delegatedCertificationManager_ = expr()
             .caseWhen()
             .exists(select(Delegation.class)
                     .where().prop("toPerson").eq().extProp("id")
                       .and().prop("fromDate").le().now()
                       .and().begin().prop("toDate").isNull().or().prop("toDate").ge().now().end().model())
             .then().val(true)
             .otherwise().val(false).endAsBool().model();