| I want to do a select with a subQuery joind with the main query in the select like this:
SELECT c.ID, c.NAME,
(SELECT MAX(o.CREATED_DATE) FROM ORDER o WHERE o.CUSTOMER_ID=c.ID) as LAST_ORDER
FROM CUSTOMER c
WHERE c.AREA=?;
I use this code to do the select:
public List<CustomerRad> getCustomersAndLastOrder(String area) {
CriteriaBuilder builder = getCriteriaBuilder();
CriteriaQuery<CustomerRad> mainQuery = builder.createQuery(CustomerRad.class);
Root<CustomerEntity> root = mainQuery.from(CustomerEntity.class);
Subquery<LocalDateTime> sqOrder = mainQuery.subquery(LocalDateTime.class);
Root<OrderEntity> orderRoot = sqOrder.from(OrderEntity.class);
mainQuery.where(builder.equal(root.get(CustomerEntity_.area), area));
sqOrder.select(builder.greatest(orderRoot.get(OrderEntity_.createdDate)));
sqOrder.where(builder.equal(orderRoot.get(OrderEntity_.arbeidsflyt), root));
mainQuery.select(
builder.construct(CustomerRad.class,
root.get(CustomerEntity_.id),
root.get(CustomerEntity_.name),
sqOrder.getSelection()
)
);
return getEntityManager().createQuery(mainQuery).getResultList();
}
And get this SQL, that produce the correct result, but got an unnecasery join in the subquery. (formatted to make it more readable
SELECT customeren0_.ID AS col_0_0_,
customeren0_.NAME AS col_1_0_,
(SELECT MAX(orderentit1_.CREATED_DATE)
FROM ORDER orderentit1_,
CUSTOMER customeren2_
WHERE orderentit1_.CUSTOMER_ID=customeren2_.ID
AND orderentit1_.CUSTOMER_ID =customeren0_.ID
) AS col_2_0_
FROM CUSTOMER customeren0_
WHERE customeren0_.AREA=?;
I would expect it to be like this:
SELECT customeren0_.ID AS col_0_0_,
customeren0_.NAME AS col_1_0_,
(SELECT MAX(orderentit1_.CREATED_DATE)
FROM ORDER orderentit1_
WHERE orderentit1_.CUSTOMER_ID=customeren0_.ID
) AS col_2_0_
FROM CUSTOMER customeren0_
WHERE customeren0_.AREA=?;
|