HSQL Subqueries

Question

Hi there,

I have a class Order that contains a set of OrderSection's that itself contains a set of OrderItem's.

In SQL, one can use a SELECT statement in a JOIN clause as done 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

Would it be possible to express such a query in HQL?

Answer 1

Unless 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 which case it can then be rewritten in HQL as:

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're out of luck with HQL conversion because Subqueries in HQL can only occur in SELECT or WHERE clauses. You can, however, map your query as <sql-query> - should make no difference at the end.