@Where clause does not work inside hibernate join query

Question

I have 2 entities with @Where annotation. First one is Category;

@Where(clause = "DELETED = '0'")
public class Category extends AbstractEntity

and it has the following relation;

@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, mappedBy = "category")
private Set<SubCategory> subCategories = Sets.newHashSet();

and second entity is SubCategory;

@Where(clause = "DELETED = '0'")
public class SubCategory  extends AbstractEntity

and contains corresponding relation;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "CATEGORY_ID")
private Category category;

Whenever I call the following Dao method;

@Query(value = "select distinct category from Category category join fetch category.subCategories subcategories")
public List<Category> findAllCategories();

I got the following sql query;

    select
        distinct category0_.id as id1_3_0_,
        subcategor1_.id as id1_16_1_,
        category0_.create_time as create2_3_0_,
        category0_.create_user as create3_3_0_,
        category0_.create_userip as create4_3_0_,
        category0_.deleted as deleted5_3_0_,
        category0_.update_time as update6_3_0_,
        category0_.update_user as update7_3_0_,
        category0_.update_userip as update8_3_0_,
        category0_.version as version9_3_0_,
        category0_.name as name10_3_0_,
        subcategor1_.create_time as create2_16_1_,
        subcategor1_.create_user as create3_16_1_,
        subcategor1_.create_userip as create4_16_1_,
        subcategor1_.deleted as deleted5_16_1_,
        subcategor1_.update_time as update6_16_1_,
        subcategor1_.update_user as update7_16_1_,
        subcategor1_.update_userip as update8_16_1_,
        subcategor1_.version as version9_16_1_,
        subcategor1_.category_id as categor11_16_1_,
        subcategor1_.name as name10_16_1_,
        subcategor1_.category_id as categor11_3_0__,
        subcategor1_.id as id1_16_0__ 
    from
        PUBLIC.t_category category0_ 
    inner join
        PUBLIC.t_sub_category subcategor1_ 
            on category0_.id=subcategor1_.category_id 
    where
        (
            category0_.DELETED = '0' 
        )

Could you please tell me why the above query lacks

and subcategor1_.DELETED = '0'

inside its where block?

Answer 1

I have just solved a similar problem in my project.

It is possible to put @Where annotation not only on Entity, but on also on your child collection.

According to the javadoc:

Where clause to add to the element Entity or target entity of a collection

In your case, it would be like :

@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, mappedBy = "category")
@Where(clause = "DELETED = '0'")
private Set<SubCategory> subCategories = Sets.newHashSet();

Please find a similar issues resolved here

I believe thus solution is not as invasive compared to using Hibernate Filters.These filters are disabled by default and operate on Session level, thus enabling them each time new Session opens is extra work especially when your DAO works through abstractions like Spring Data