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

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.

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:

1. getPostsList() returns empty list
2. getPostsList() returns list with at least one element with queried status
3. 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.