Hql: How to Perform an Inner Join on a Subquery

HQL: Is it possible to perform an INNER JOIN on a subquery?

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-subqueries

Note that HQL subqueries can occur only in the select or where clauses.

You can rewrite the query so that the subquery is part of the where clause instead. Referencing the l.idItem in the subquery

Hibernate HQL inner join subselect

I think your SQL query can be rewritten to this:

SELECT f.date, f.name, SUM(f.seats) 
FROM Foo f 
WHERE EXISTS (
  SELECT 1
  FROM Foo f1
  WHERE f.start + f.end = f1.start + f1.end
  AND f.date = f1.date
  AND f1.date >= (SELECT MAX(earliestDate) FROM Bar)
  AND f1.name = :name  
)
AND f.date >= (SELECT MAX(earliestDate) FROM Bar)
GROUP BY f.date, f.name
ORDER BY f.date ASC, SUM(f.seats) DESC

This should be simpler to translate to HQL, and it might even be more correct, because your original query seems to create an unwanted cartesian product between the self joined rows.

There's probably an even better way to query this in SQL, without self joining the Foo table, using a single pass through the table. But I'd need to know more about your real world use-case for that, and what RDBMS you're using.

Subselect in LEFT JOIN hql

When you join with that table, you need to use the ON keyword.

   LEFT JOIN ( 
        SELECT d, e, f
        FROM table9 as t9
        ....
    ) as xx ON xx.prop = t2.prop and xx.prop2 = t4.prop

Also you forgot the = in the join of the second property.

EDIT:

A subquery must be surrounded by parentheses (often by an SQL
aggregate function call). Even correlated subqueries (subqueries that
refer to an alias in the outer query) are allowed. Note that HQL
subqueries can occur only in the select or where clauses.

From this, you cannot have a sub-query in the from clause.

HQL Subqueries in joins

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.

---

Related Topics