CODEWITHSUNDEEP
javaspringhibernatespring-mvcjpa
supercobra
supercobra

Reputation: 16292

Spring / Hibernate: JPA exception :: path expected for JOIN

Getting QuerySyntaxException trying to run this query:

public interface SignalRepository extends PagingAndSortingRepository<Signal, Long> {

...
@Query("select p1 from Signal p1 LEFT JOIN Signal p2 " 
        + " ON (p1.zoneId = p2.zoneId AND p1.createdAt < p2.createdAt) "
        + " where p2.id is null AND p1.userId=?#{[0]} AND p1.pid=?#{[1]}")

Produces this error:

caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Path expected for join! [select p1 from com.newco.models.Signal p1 LEFT JOIN Signal p2  ON (p1.zoneId = p2.zoneId AND p1.createdAt < p2.createdAt)  where p2.id is null AND p1.userId=?1 AND p1.pid=?2]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:268) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]

This query works well when typed SQL command line (and Ruby on Rails) but fails with Spring/JAP.

Upvotes: 0

Views: 776

Answers (1)

davidxxx
davidxxx

Reputation: 131486

Path expected for join!

With JPQL, you cannot write JOIN as you do in SQL. You must precise the path of the join relation


Either you do a native SQL query : 

Query q = em.createNativeQuery("YourQuery", Signal.class);

Either you adapt your SQL query in JPQL query.
To do it, you should change several things.

1) I am not sure JPA recognizes this syntax : p1.userId=?#{[0]} AND p1.pid=?#{[1]} to set parameter values. You should rather use : p1.userId=:?1 and p1.pid=:?2.

2)Admitting you declare in Signal Entity, a reflexive relation (p2), the LEFT JOIN condition should be something like that (JOIN is made on entity relation and ON becomes WITH):

"select p1 FROM Signal p1 LEFT JOIN p1.p2 p2 " 
        + " WITH p1.createdAt < p2.createdAt "
        + " where p2.id is null AND p1.userId=:?1 AND p1.pid=:?2

3)p1.zoneId = p2.zoneId condition should not be needed in the WITH if Signal entity has its reflexive relation based on this condition.

Upvotes: 1

Related Questions