I have 2 tables:
orders: id
items: id, orderId, total, flag
I would like to make following query using Hibernate Criteria (DetachedCriteria):
SELECT
o.id,
SUM(i1.total),
SUM(i2.total)
FROM
orders o
LEFT JOIN
(
SELECT
i.orderId as orderId,
SUM(i.total) as total
FROM
items i
WHERE
i.flag = 0
GROUP BY
orderId
) AS i1
ON i1.orderId = o.id
LEFT JOIN
(
SELECT
i.orderId as orderId,
SUM(i.total) as total
FROM
items i
WHERE
i.flag = 1
GROUP BY
orderId
) AS i2
ON i2.orderId = o.id
GROUP BY
o.id
I know how to use DetachedCriteri
a to create subquery in WHERE
clause, but as you can see, I need to do a subquery in FROM
clause. If it is not possible, maybe there is a way to write it in SELECT
clause (inside SUM()
), because this query could be rewritten to such form.
I really need to use Criteria API even if I have to pass native SQL to the query.
I didn't show you classes or mapping, but as you can see, this is a very simple example.
Best Answer
I found solution for my problem. I had to make a POJO and mapping:
And of course for flag 1 it will be similar. This could be also done by declaring views in database and creating a mappings to that views. In this example I was using the same POJO class, but different entity-name.
Then I made a property in class Order and mapping:
Of course laziness could be set.
I'm pretty sure now, that this could be also done in different way - by making subquery in
SELECT
clause - using theformula
attribute/element in mapping for properties, but this would work slower than subquery inFROM
clause.And of course all of that was in documentation to Hibernate ;)