Left join fetch with condition fails

Question

I have the following HQL query:

return entityManager().createQuery(
    "SELECT page FROM ProjectPage page"
  + " left join fetch page.categorySet as category              "
  + " where page.id = :id " 
  + " and  category.parentCategory is null "
  + " and  (category.status != :status_val) " 
  ,ProjectPage.class).setParameter("id", id)
  .setParameter("status_val", 1).getSingleResult();

the problem is that the conditions in the where clause fails, for example, the query returns category objects whose status is 1 and category objects whose parentCategory is not null although i specified this constrains as stated above!!

Answer 1

If you expect this query to return ProjectPage object with categorySet filtered out based on where conditions, then your expectations are wrong. If ProjectPage instance with given id contains any category that pass the where clause conditions, it will be returned as a whole object. This is by design, and needed because of underlying mechanisms, caching, etc. If you need category objects that fulfill some conditions, you'll have to write a separate query for that.