JPQL query - hibernate: with-clause not allowed on fetched associations

Question

I have JPA Repository and JPQL query like this:

@Query("SELECT c from Campaign c" +
        " left join fetch c.postsList p on p.status = :postStatus" +
        " left join fetch p.platform" +
        " left join fetch c.campaignStatistics stat on stat.updateDate = :updateDate" +
        " where c.id =:id")
Campaign findCampaignWithPosts(
        @Param("id") Long id,
        @Param("postStatus") PostStatus postStatus,
        @Param("updateDate") LocalDate updateDate);

But it doesn' work. I get:

org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause not allowed on fetched associations; use filters

I went to study information about the JPA 2.1 specification and that's what I found:

The join condition used for a join comes from the mapping's join columns. This means that the JPQL user is normally free from having to know how every relationship is joined. In some cases it is desirable to append additional conditions to the join condition, normally in the case of outer joins. This can be done through the ON clause. The ON clause is defined in the JPA 2.1 specifiation, and may be supported by some JPA providers. EclipseLink : Hibernate : TopLink - support the ON clause.

It should be noted that this type of query does not help me at all, becouse in this query sometimes i get null, where clause use after join tables.

   @Query("SELECT c from Campaign c" +
            " left join fetch c.postsList p" +
            " left join fetch p.platform" +
            " left join fetch c.campaignStatistics stat" +
            " where c.id =:id" +
            " and p.status = :postStatus" +
            " and stat.updateDate = :updateDate")

What to do in this case? Is there no alternative solution other than how to use the native query? But then the meaning of almost all JPQL queries is lost. I use hibernate version 5.2.12

Have you checked if some of your fields in your classes is called `with`? — Luiggi Mendoza, Nov 29 '17 at 01:38
@Luiggi Mendoza no, all fields in the classes not contains 'with' — Peter Kozlovsky, Nov 29 '17 at 01:43
Perhaps it means that you cannot put an `ON` clause when using `FETCH JOIN`? But then that is what the JPA spec says also, so are you really surprised?. `ON` is only allowed on normal `JOIN` — , Nov 29 '17 at 07:44
Also, you claim to quote the JPA spec, but I've no idea where you got that text from; it is NOT from the JPA spec. https://github.com/javaee/jpa-spec/blob/master/jsr338-MR/JavaPersistence.pdf — , Nov 29 '17 at 08:23
@DN1 i get this info on wikibooks [link](https://en.wikibooks.org/wiki/Java_Persistence/JPQL). Probably not correctly expressed, calling the specification. But how then in this situation? use native queries? — Peter Kozlovsky, Nov 29 '17 at 12:12

score 9 · Accepted Answer · answered Dec 26 '17 at 15:40

9

You are right, when you add and p.status = :postStatus it filters out the results where the right side of the join does not exist (i.e. when the Campaign has no posts).

What worked for me is to add an OR clause to accept the case where the right side of the join is NULL.

So, you should replace and p.status = :postStatus with and (p IS NULL OR p.status = :postStatus).

So this request should work :

@Query("SELECT c from Campaign c" +
            " left join fetch c.postsList p" +
            " left join fetch p.platform" +
            " left join fetch c.campaignStatistics stat" +
            " where c.id =:id" +
            " and (p is NULL OR p.status = :postStatus)" +
            " and stat.updateDate = :updateDate")

As for the error message you received, I think you should not add the ON clause because that is already handled by JPA/Hibernate.

I am using Hibernate 5.0.12.

answered Dec 26 '17 at 15:40

HL'REB

838
11
17

3

It's [not a good idea](https://stackoverflow.com/a/5819631/2387977) use `WHERE` condition on `fetch` alias. – Dherik Mar 06 '18 at 12:21
1

This is not a correct answer. Using a where clause will not provide the same result as joining with a smaller set. See the post by Victor which is the correct answer to this question. – cholewa1992 Mar 30 '21 at 09:12
This answer is not correct only if I want to get the Campaign, even if any of the postLists does not have the :postStatus? Then I would like to get a Campaign with empty postList. For this scenario, this answer is not correct. @victor mentions this. but didn't provide a solution though. – Ramanujan R Oct 02 '21 at 15:21

score 6 · Answer 2 · answered Feb 02 '21 at 09:28

There is no alternative solution, because when you have a predicate like this:

and (p is NULL OR p.status = :postStatus)

then 3 cases appear:

getPostsList() returns empty list
getPostsList() returns list with at least one element with queried status
getPostsList() returns list with no element with queried status

In state from 3 case you will lose all rows with Campaign completely. But fetch ones with 1 state

Because left join will not join nulls, when list of postList is not empty.

score 0 · Answer 3 · answered Mar 06 '18 at 12:52

It's not a good idea use WHERE condition on fetch alias

Try this

@Query("SELECT c from Campaign c" +
        " left join c.postsList p" +
        " left join fetch c.postsList " +
        " left join fetch p.platform " +
        " left join c.campaignStatistics stat" +
        " left join fetch c.campaignStatistics " +
        " where c.id =:id" +
        " and (p is NULL OR p.status = :postStatus)" +
        " and stat.updateDate = :updateDate")

JPQL query - hibernate: with-clause not allowed on fetched associations

3 Answers3

Linked