CODEWITHSUNDEEP
sqldatabasehibernateleft-joinhql
Eslam Hamdy
Eslam Hamdy

Reputation: 7386

Left join fetch with condition fails

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!!

Upvotes: 0

Views: 1894

Answers (1)

Predrag Maric
Predrag Maric

Reputation: 24423

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.

Upvotes: 2

Related Questions