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

Hey, are your entity mapped with a relationship like one to many or so? — user1211, Dec 02 '16 at 09:10
A simple way of writing it would be @Query("Select * from A a left join B b on a.id=b.id") — user1211, Dec 02 '16 at 09:11
so of course I use relationships, I wanted to simplify it and it is reason why I did not included it. Yup I tried join on a.id=b.id but with JPA this does not work because JPA recognizes the relationship automatically. and the keyword "on"must be omitted — Kamil Nękanowicz, Dec 02 '16 at 09:37
because the ON clause comes from the mapping's join columns and is handled automatically — Kamil Nękanowicz, Dec 02 '16 at 09:48

AxelH · Accepted Answer · 2016-12-02T10:07:48.237

5

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.

edited Dec 02 '16 at 10:07

answered Dec 02 '16 at 09:43

AxelH

14,325
2
25
55

You're right your solution is better but Is it possible to somehow make it work my way? – Kamil Nękanowicz Dec 02 '16 at 10:00
1

@user3871754 Infortunatly, nope. This is in the HQL doc. Let me update my answer – AxelH Dec 02 '16 at 10:09
I tested it, and got : View's SELECT contains a subquery in the FROM clause – Kamil Nękanowicz Dec 02 '16 at 13:18
Wait, Hibernate tells you the view can't be called ? – AxelH Dec 02 '16 at 13:21
No I try create view from phpAdmin I think I have to update mySQL to 5.7: http://stackoverflow.com/questions/8428641/views-select-contains-a-subquery-in-the-from-clause – Kamil Nękanowicz Dec 02 '16 at 13:24
@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 ;) – AxelH Dec 02 '16 at 13:27

score 0 · Answer 2 · answered Dec 02 '16 at 09:22

0

You can write it like this

@Query("SELECT a FROM Carparks a LEFT JOIN Locales b on a.carpark_id = b.carpark_id")

answered Dec 02 '16 at 09:22

BOUALI ALI

230
2
14

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 Dec 02 '16 at 09:42

QuerySyntaxException: unexpected token:

2 Answers2