How can I create a Join on a subquery with an aggregation (without correlation)

Hibernate ORM
1

Hi
I need your help with the following query. I don’t know how to express it with the hibernate criteria api.
All the examples i found concerning join of subqueries were simple (the subqueries return only one colums, no aggregation,…).
I dont know if it’s at all possible to get this sql statement formulated with the hibernate query criteria.
Note: The generated code by hibernate should not contain any correlated subquery because we are dealing with huge amount of data. A such correlation is (proven) a performance killer.

I would be highly grateful for your information.

select msg.id, msgWorkItem_1.id
from message msg
left outer join MessageWorkItem msgWorkItem_1 on msg.id=msgWorkItem_1.message_id
and (msgWorkItem_1.id, msgWorkItem_1.message_id) in (
select max(msgWorkItem_2.id), msgWorkItem_2.message_id
from MessageWorkItem msgWorkItem_2
where msgWorkItem_2.xxx=‘something’
group by msgWorkItem_2.message_id
)

2

This query cannot be expressed with Criteria API. You need to use a native SQL query.

More, you might try to use a CTE like this:

with max_mwi(max_id, max_message_id) as
	(select 
		max(msgWorkItem_2.id) as max_id, 
		msgWorkItem_2.message_id as max_message_id
	from 
		MessageWorkItem msgWorkItem_2
	where 
		msgWorkItem_2.xxx=‘something’
	group by msgWorkItem_2.message_id)
select msg.id, msgWorkItem_1.id
from message msg
left outer max_mwi on msg.id=max_mwi.max_id and msg.message_id = max_mwi.message_id
3

Thanks a lot for the quick answer