Enthuware

Posted: **Wed Jul 05, 2017 9:40 am**

Hi, I used the application program, ToyAppForJPA
to try this:

"select co.id , li.quantity from CustOrder co join co.lineItems li where li.quantity =
(select max(li.quantity) from li)"

It returns the same result as:

select co.id, li.quantity from CustOrder co join co.lineItems li where li.quanity=
(Select max(line.quantity) from OrderLineItem line)

So, in this case, the last option can be correct as well.

Posted: **Wed Sep 19, 2018 11:07 am**

himaiMinh wrote: ↑
Wed Jul 05, 2017 9:40 am
Hi, I used the application program, ToyAppForJPA
to try this:

"select co.id , li.quantity from CustOrder co join co.lineItems li where li.quantity =
(select max(li.quantity) from li)"

It returns the same result as:

select co.id, li.quantity from CustOrder co join co.lineItems li where li.quanity=
(Select max(line.quantity) from OrderLineItem line)

So, in this case, the last option can be correct as well.

Code: Select all

CriteriaQuery<CustOrder> cq = cb.createQuery(CustOrder.class); 
Root<CustOrder> custOrderRoot = cq.from(CustOrder.class); 
Join<CustOrder, OrderLineItem> oliJoin = custOrderRoot.join("lineItems");             
cq.select(custOrderRoot).distinct(true); 
Subquery<Integer> sq = cq.subquery(Integer.class); 
Join<CustOrder, OrderLineItem> sqRoot = sq.correlate(oliJoin); 
sq.select(cb.max(sqRoot.get(OrderLineItem_.quantity))); 
cq.where( cb.equal(oliJoin.get(OrderLineItem_.quantity),  cb.all(sq)) );

It looks to me like this code finds the max quantity per CustOrder in the main query rather than the max quantity for all OrderLineItems.

Enthuware

About Question enthuware.oce-jpad.v6.2.481 :

About Question enthuware.oce-jpad.v6.2.481 :

Re: About Question enthuware.oce-jpad.v6.2.481 :