Jpa namedquery with left join fetch

Question

this is my namedquery:

@NamedQuery( name = "User.findOneWithLists", query = "SELECT u FROM User u " + "LEFT JOIN FETCH u.aTemplates " + "LEFT JOIN FETCH u.bTemplates " + "LEFT JOIN FETCH u.bp " + "LEFT JOIN FETCH u.aCredentials " + "LEFT JOIN FETCH u.st WHERE (st.deleted = false) " + "LEFT JOIN FETCH u.bCredentials " + "LEFT JOIN FETCH u.cl " + "WHERE u.id= :id")

My problem is that I get an error when the application starting:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: LEFT ....

On the st side there is an annotation

@ManyToOne
 @JoinColumn(name = "st_user")
 private User user;

Any idea how can I handle this where clause?

Roman C · Accepted Answer · 2014-07-21T10:28:16.270

8

Check a SQL syntax, you can't use left join after where clause. If you are looking at the SQL generated form that named query you will see that joined tables in the query the where clause comes after joins and should specify equal condition that links those tables by the keys. The primary key of the main table on the left and the foreign key of the joined table on the right. The joined table is specified by the property of your many-to-one association.

@NamedQuery(
    name = "findOneWithLists",
    query = "from Table t left join User u where u.id= :id"
)

edited Jul 21 '14 at 10:28

answered Jul 21 '14 at 09:48

Roman C

49,761
33
66
176

ok but how can i do a left join with a where or something similar – user3296520 Jul 21 '14 at 09:51
It's not SQL. It's JPQL. JPQL doesn't use tables. It uses entities. The first sentence of the answer is correct, but the rest isn't. – JB Nizet Jul 21 '14 at 09:52
@JBNizet that's right, JPQL, or HQL don't require the condition, but SQL generated uses them. – Roman C Jul 21 '14 at 09:56
@user3296520 if you're using JPA 2.1, you can use the ON clause, as you would in SQL: `left join u.st st on st.deleted = false`. But you can't use an ON clause on a left join **fetch**. The "sts" of a user always contain *all* the sts of the user. You can't load a user and pretent it only has non-deleted sts. – JB Nizet Jul 21 '14 at 10:00

score 0 · Answer 2 · answered Jul 24 '14 at 17:26

For join conditions Hibernate provides the with keyword, even before JPA 2.1.

The relevant part of your query thus would look like this:

SELECT u FROM User u  ... LEFT JOIN u.st WITH st.deleted = false

I'm not sure about LEFT JOIN FETCH u.cl with u.id= :id but if I remember correctly, that's not as easy and might have to be resolved with an adapted join and u.ui = :id in the where condition.

LEFT JOIN FETCH u.st WITH st.deleted = false`

This is not supported, since you can't do a partial fetch.

Jpa namedquery with left join fetch

2 Answers2

Linked

Related