This blog for Hibernate Generic DAO Project has been moved to WordPress Bye bye Blogger!

Friday, December 19, 2008

Filtering by collection properties

The Search capability needs to be able to handle collection properties. Here is a proposal for how we might do it.

Add four new filter types:

* SOME: Takes another filter as the value. SOME evaluates to true if the specified filter evaluates to true for at least one element in the collection specified by the property.

* ALL: Same as SOME except that the filter must evaluate to true for all elements in the collection.

* EMPTY: Takes no value. True if
(a) the property is NULL,
(b) the property is a collection and it is empty, or
(c) the property is a string and it is the empty string ("")

* NOT_EMPTY: Inverse of EMPTY

Here are some examples:

Use case: projects that I am a member of

- if projects.members is a list of people

search.addFilterSome('members', Filter.equal('', me));
search.addFilterSome('members', Filter.equal('id',;
These filter options would produce HQL something like this:

from Project p
where exists (from p.members m where m = :me)
[or optimized using inverse relationship]
where exists (from p.members m where m = :me)
- if projects.members is a list of ProjectMember many-to-many relationship class which in turn has an id component with 'project' and 'person' properties

search.addFilterSome('members', Filter.equal('id.person', me));
where exists (from p.members pm where = :me)

Use case: project has members

where size(members) > 0

Use case: project has at least one of the given members

search.addFilterSome('members','', us));
where exists (from p.members where m in (:us))

Use case: project has all the given members

search.addFilterSome('members', Filter.equal('', member1));
search.addFilterSome('members', Filter.equal('', member2));
search.addFilterSome('members', Filter.equal('', member3));
// I ran this and the subX_'s don't seem to be needed, but I might include them just in case.
where exists (from p.members sub1_m where sub1_m = :member1)
and exists (from p.members sub2_m where sub2_m = :member2)
and exists (from p.members sub3_m where sub3_m = :member3)

Use case: project has only the given members

search.addFilterAll('members','', us));
//same as not some (not condition)
where not exists (from p.members m where m not in (:us))

Use case: project has only members with top-secret clearance

search.addFilterAll(Filter.greaterOrEqual('clearance', TOP_SECRET));
where not exists (from p.members m where not (m.clearance >= :TOP_SECRET))
where not exists (from p.members m where (m.clearance is null or m.clearance < :TOP_SECRET))

Use case: project has fewer than 4 members

search.addFilterLessThan('members.size', 4);
where members.size <>

Use case: project has me as primary member

search.addFilterSome('members', Filter.and( Filter.equal('id.person', me), Filter.equal('primary', true) ) );

where exists (from p.members m where = :me and m.primary = 1 )

1 comment:

  1. Amazing & Great informative blog,it gives very useful practical information to developer like me. Besides that Wisen has established as Best Hibernate Training in Chennai .