Hi
In my model I have Product entity with an optional one to one relationship to an Agent entity.
@Lookup(type = LookupType.DROPDOWN)
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "AGENT_ID")
protected Agent agent;
With a simple query like select e.id, e.agent.internalCode from busy$Product e
, there will be an implicit inner join, which hides products with no agent.
SELECT t0.ID, t1.INTERNAL_CODE FROM BUSY_PRODUCT t0, BUSY_AGENT t1 WHERE ((t0.DELETE_TS IS NULL) AND ((t1.ID = t0.AGENT_ID) AND (t1.DELETE_TS IS NULL)))
As far as I know it is not possible in JPA for force an implicit left outer join for relationships. So I tried another query : select e.id, case when (a.id is null) then null else a.internalCode end from busy$Product e left join e.agent a
Which by using JPA yields expected SQL.
@Test
public void leftJoinCaseWhenJPA() {
String query = "select e.id, case when (a.id is null) then null else a.internalCode end from busy$Product e left join e.agent a"
persistence.createTransaction().execute((em)->{
dump(em.getDelegate().createQuery(query).getResultList());
});
}
SELECT t0.ID, CASE WHEN (t1.ID IS NULL) THEN ? ELSE t1.INTERNAL_CODE END FROM BUSY_PRODUCT t0 LEFT OUTER JOIN BUSY_AGENT t1 ON (t1.ID = t0.AGENT_ID) WHERE (t0.DELETE_TS IS NULL)
But does not work with CUBA.
@Test
public void leftJoinCaseWhenCUBA() {
String query = "select e.id, case when (a.id is null) then null else a.internalCode end from busy$Product e left join e.agent a"
persistence.createTransaction().execute((em)->{
dump(em.createQuery(query).getResultList());
});
}
com.haulmont.cuba.core.sys.jpql.JpqlSyntaxException: Errors found for input jpql:[select e.id, case when (a.id is null) then null else a.internalCode end from busy$Product e left join e.agent a]
CommonErrorNode [<unexpected: [@10,19:22=‘when’,<60>,1:19], resync=case when (>]
CommonErrorNode [<mismatched token: [@14,26:26=‘.’,<68>,1:26], resync=.id is null) then null else a.internalCode end from busy$Product e left join e.agent a>]
While documentation claims that case when is ok except in update query : JPQL Functions - CUBA Platform. Developer’s Manual.
It would be handy to have an option on DataManager/LoadContext to bypass CUBA.
Using 6.9.7 but could move to 6.10 to get a patch.
Best Regards
Michael