ManyToMany, SoftDelete Filter

mali_x · **Joined:** Thu Feb 25, 2010 7:20 am **Posts:** 4

Querying data from ManyToMany mapping using conditions does not seem to work.

The scenario:
1. every table has a boolean "deleted" column enabling soft deletes.
2. Persons and Groups are mapped via ManyToMany --> Resulting in a mapping table also having this deleted column.

The code extract from the Person class:

Code:

   @ManyToMany(cascade = CascadeType.ALL, fetch=FetchType.EAGER)
   @JoinTable(name = "PERSON_PERSONGROUP", 
      joinColumns = { @JoinColumn(name = "PERSON_ID") },
      inverseJoinColumns = { @JoinColumn(name = "PERSONGROUP_ID") })
   @FilterJoinTable(name = "deletedFilter", condition = "deleted = :deleted")
   @Filter(name="deletedFilter", condition="deleted = :deleted")
   public List<PersonGroup> getGroups() {
      return groups;
   }

The code extract from ther PersonGroup class:

Code:

   @ManyToMany(mappedBy="groups", cascade=CascadeType.ALL)
   public List<Person> getPersons() {
      return persons;
   }

The dao code to retrieve the Persons including their Groups:

Code:

   Session session = getSessionFactory().getCurrentSession();
   session.enableFilter("deletedFilter").setParameter("deleted", false);
   Criteria criteria = session.createCriteria(type);
   criteria.add(Restrictions.eq("deleted", false));
   result = criteria.list();

The resulting SQL:

Code:

    select
   ...(code omitted)... 
    from
        APP.PERSON this_ 
    left outer join
        PERSON_PERSONGROUP groups2_ 
            on this_.ID=groups2_.PERSON_ID 
            and groups2_.deleted = ? 
    left outer join
        APP.PERSONGROUP persongrou3_ 
            on groups2_.PERSONGROUP_ID=persongrou3_.ID 
            and persongrou3_.deleted = ? 
    where
        this_.DELETED=?

I executed the generated statement directly in the database. The result is as expected but the objects hibernate creates are not.
The @Filter Annotation works fine but the @FilterJoinTable seems to mess up things. Example:

NOT using the @FilterJoinTable annotation results in distinct Person objects with all their groups (incl. deleted mappings, which I don't want) in the groups

attribute.
e.g.

Code:

PersonA.group1,group2,group3
PersonB.group3,group4

Using the @FilterJoinTable annotation results in douplicate Person objects with each having just one group in the groups attribute. The groups in the

different person objects are all the same across the same person. e.g.

Code:

PersonA.group1
PersonA.group1
PersonA.group1
PersonB.group3
PersonB.group3

Thinking about the solution:
setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) is not what I want, since I would not get the groups...

I have tried with the groups attribute being type List and Set, both did not change the behaviour.
I have queried by criteria and detached criteria, also both resulting the same.
I have tried with @Filter and @FilterJoinTable, as well as @Where and @WhereJoinTable, same results. @WhereJoinTable messes things up as well.

Am I missing something or is there an issue with these annotations? I did not find a whole lot of documentation on this.

mali_x · **Joined:** Thu Feb 25, 2010 7:20 am **Posts:** 4

I forgot,

Code:

@FilterDef(name="deletedFilter", parameters = {@ParamDef(name = "deleted", type = "boolean")})

has to be added to the domain class.

I have not solved this issue so far. Hints would be great!
Regards, Mathias

mali_x · **Joined:** Thu Feb 25, 2010 7:20 am **Posts:** 4

A colleague of mine had a look at this. The configuration was good. I just had the wrong data in the database. Above you have code that works perfect.

still

Code:

setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)

has to be called to remove duplicates.

Hope the example might help someone else. Regards! Mathias

iravhari · **Joined:** Fri Sep 11, 2009 2:35 am **Posts:** 2

Hi,
Have you tried the same soft delete feature when deleting the manytomany objects?
Please do revert ASAP.