Hibernate HQL inner join subselect

Question

I got the following SQL which I wanted to transform into valid HQL. The issue with this is, that its not permitted to join on a sub-query per the docs. Even though these are old docs (v3.3) this section still seems to still hold in hibernate 5.3.

SELECT f.date,
    f.name,
    SUM(f.seats) 
FROM Foo f 
        INNER JOIN (SELECT fl.date,
                        fl.start + fl.end AS code 
                    FROM Foo fl 
                    WHERE fl.date >= (SELECT MAX(earliestDate) 
                                            FROM Bar) 
                        AND fl.name = :name) fl 
        ON f.start + f.end = code 
        AND f.date = fl.date 
WHERE f.date >= (   SELECT MAX(earliestDate) 
                        FROM Bar)   
GROUP BY f.date,
    f.name 
ORDER BY f.date ASC,
    SUM(f.seats) DESC

I came up with this HQL:

SELECT NEW com.company.project.model.FooRepresentation( f.fooId.date,
    f.fooId.name,
    SUM(f.seats)) 
FROM Foo f 
        INNER JOIN (SELECT fl.fooId.date,
                        fl.fooId.start + fl.fooId.end AS code 
                    FROM Foo fl 
                    WHERE fl.fooId.date >= (SELECT MAX(earliestDate) 
                                            FROM FooConfig) 
                        AND fl.fooId.name = :name) fl 
        ON f.fooId.start + f.fooId.end = code 
        AND f.fooId.date = fl.fooId.date 
WHERE f.fooId.date >= ( SELECT MAX(earliestDate) 
                        FROM FooConfig) 
GROUP BY f.fooId.date,
    f.fooId.name 
ORDER BY f.fooId.date ASC,
    SUM(f.seats) DESC

Trying to execute this HQL query, results in this exception

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 169

which hints to the ( at INNER JOIN (SELECT.

Is there a way to join on sub-queries within HQL? If not, what would be the best way to achieve the same results as the SQL with HQL?

I am not that good with converting the inner join on <sub-query> to an actual where clause where the sub-query is permitted

What's the reason why you want to do this rather than sticking to SQL? — Lukas Eder
Mostly the convenience of having the result translated to a non @Entity class (NEW com.company.project.model.FooRepresentation()) — XtremeBaumer
Oh, the NEW com.company.project.model.FooRepresentation syntax? But I mean, that's one line of code to map from the Object[] to a constructor call, compared to rewriting it all :) Anyway - I'm not sure this will be an easy rewrite... — Lukas Eder
Without the inconvenience that joins on subqueries are not allowed, this wouldn't actually be that hard to convert. Its basically only replacing the column names. So you would suggest that I use a native query and map it on my own? — XtremeBaumer

Lukas Eder Lukas Eder · Accepted Answer · 2019-02-21T09:32:18

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.

Hibernate HQL inner join subselect

2 Answers