QuerySyntaxException: unexpected token:

Question

In MySQL it works:

SELECT * FROM carparks a 
LEFT JOIN  (SELECT * FROM locales_carparks) 
c ON a.carpark_id=c.carpark_id

Hot to translate it to JPA:

@Query("SELECT a FROM Carparks a LEFT JOIN("
            +"SELECT b FROM a.locales b"
            +")")

IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 72 [SELECT a FROM database.model.carpark.Carparks a LEFT JOIN(SELECT b FROM a.locales b)]

I've simplified example to show the essence of the problem. Normally I use justSELECT a FROM Carparks a LEFT JOIN a.locales and it works, but in my case I want to use nested SELECT because my query is much more complex

Solution

You could use a simple alternative

create view v_carparks as
    SELECT * FROM carparks a 
    LEFT JOIN  (SELECT * FROM locales_carparks) 
    c ON a.carpark_id=c.carpark_id

And use it for the query

@Query("SELECT a FROM v_carparks")

Especially if the query is complicated, this would be cleaner to have a huge query in a view to hide that complexity.

EDIT :

You can't used a nested query for join. This is written in the HQL documentation like this :

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

This could be explain for the mapping system. Hard to do the mapping with a subqueries result.

Comments

[Kamil Nękanowicz]

thanks, I normally use SELECT a FROM Carparks a LEFT JOIN a.locales and it works, but in my case I wanted to use nested SELECT because my query is much more complex, I am gonna update question to clarify it

[Kamil Nękanowicz]

You're right your solution is better but Is it possible to somehow make it work my way?

[AxelH]

@user3871754 Infortunatly, nope. This is in the HQL doc. Let me update my answer

[Kamil Nękanowicz]

I tested it, and got : View's SELECT contains a subquery in the FROM clause

[AxelH]

Wait, Hibernate tells you the view can't be called ?

[Kamil Nękanowicz]

No I try create view from phpAdmin I think I have to update mySQL to 5.7: stackoverflow.com/questions/8428641/…

[AxelH]

@user3871754 Yes, I was about to past the same link .. I didn't new that about MySql, sorry. But you could create a view with the nested query and use it to do the join in the first view ;)