HQL Subqueries in Joins
I have an Order class that contains an OrderSection set, which itself contains an OrderItem set.
In SQL, you can use a SELECT statement in a JOIN clause, as in the following query:
SELECT
o.id,
o.amount,
sum(s.quantity*s.price),
sum(s.quantity*i.price)
FROM
orders AS o
JOIN ordersections AS s ON s.order_id=o.id
JOIN (SELECT section_id, sum(quantity*price) AS price FROM orderitems GROUP BY section_id) AS i ON i.section_id=s.id
GROUP BY o.id, o.amount
Is it possible to express such a query in HQL?
0
source to share
1 answer
If I'm missing something, your query can be rewritten in SQL as:
SELECT
o.id,
o.amount,
sum(s.quantity*s.price),
sum(s.quantity*i.quantity*i.price)
FROM orders AS o
JOIN ordersections AS s ON s.order_id=o.id
JOIN orderitems AS i ON i.section_id=s.id
GROUP BY o.id, o.amount
In this case, it can be rewritten in HQL as follows:
SELECT
o.id,
o.amount,
sum(s.quantity*s.price),
sum(s.quantity*i.quantity*i.price)
FROM orders AS o
JOIN o.sections AS s
JOIN s.items AS i
GROUP BY o.id, o.amount
If I am missing something and the above query does not return what you want, you are out of luck with the HQL conversion because Subqueries in HQL can occur in SELECT or WHERE clauses . You can, however, match your query as <sql-query>
- shouldn't make any difference at the end.
+2
source to share