Flex Pasta » Filtering Hibernate Child Collections
Filtering Hibernate Child Collections
Ever run into the problem with Hibernate where a child collection needs to be filtered? With Flex, most child collections are going to be fetched eagerly. These child collections may need to filtered based on some filter to the parent. I found this problem to be a little tricky. If anyone has a better solution, commentary is welcome.
Take the following object model for example. A Cat class contains a collection of “kittens”. Let’s say that the Cat class has a “color” property on it and I want to pull all Cats whose color is white. In HQL, I would write something like “from Cats where color = ‘white’”. This would get me a collection of all white Cats, which would contain a collection of each of the Cat’s kittens. Now consider that I want to filter the kittens to only get kittens whose color is also white. The entire kittens collection is always filled in each Cat class, with no HQL join query that can filter the kittens collection.
Here is how you can filter the Cat’s kittens by using the Hibernate Criteria api.
Criteria c = this.getSession().createCriteria(Cat.class)
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.add(Restrictions.eq(”color”, “white”))
.createCriteria(”kittens”, Criteria.LEFT_JOIN)
.add(Restrictions.eq(”color”, “white”))
;
List<Cat> cats = c.list();
This is not what I would say is ideal for getting what I want, but it worked. Hope it helps you.
13 Comments
1. Scrimmers replies at 21st May 2009, 1:14 am :
Brian..
I had this exact problem earlier this week I solved it using Hibernate Filters..
In my case Users have a collection of events,I only want the events for a certain time frame but for ALL users..
First in my hbm file I set up a filter definition.
The on my “events” set I add a filter to the one to many relationship.
2. Scrimmers replies at 21st May 2009, 1:18 am :
The filter definition looks like this..
The Child Set definition looks like this.
then in the java code I simply do the following..
Filter eventfilter = getSession().enableFilter("eventsByDateFilter");
eventfilter.setParameter("eventDateStart",startDate);
eventfilter.setParameter("eventDateEnd", endDate);
List userList = getHibernateTemplate().loadAll(User.class);
getSession().disableFilter("eventsByDateFilter");
Hope this helps.. apologies for the multiple comments, too early for being clever and my finger slipped and smashed the enter key..
3. Scrimmers replies at 21st May 2009, 1:21 am :
Ok here are the hbm bits again as they didn’t come out in the code blocks, imagine it wrapped in angle brackets..
filter-def name=”eventsByDateFilter”
filter-param name=”eventDateStart” type=”date”
filter-param name=”eventDateEnd” type=”date”
filter-def
set name=”appointments” table=”EVENT” lazy=”false”
key column=”USER_ID”
one-to-many class=”com.stuff.Event”
filter name=”eventsByDateFilter” condition=”start_time between :eventDateStart and :eventDateEnd”
filter
set
4. Madhava ram replies at 26th May 2009, 6:58 am :
I am having the same kind of issue while saving the object. Please find the below link where I explained the problem in detail.
http://www.flexdeveloper.eu/forums/flex-builder-flash-builder-eclipse/flex-java-hibernate/
Thanks in advance
5. i-Thom replies at 28th May 2009, 3:13 pm :
Can’t you solve this problem with this HQL query :
FROM Cat c
LEFT JOIN FETCH c.kittens k
WHERE c.color = "white"
AND k.color = "white"
?
6. Ryan replies at 8th June 2009, 5:38 pm :
I filter child collections frequently using HQL like the solution in comment #5 - is there something different you are talking about here?
7. Shih-gian Lee replies at 20th July 2009, 8:35 pm :
Not sure how I missed this post. The criteria api is alternative to hql. whatever is done in criteria api can be done in hql. Comment #5 basically solved the problem.
8. chanter replies at 12th August 2009, 3:05 am :
I’m looking for a criteria query that will return an object of Cat.class with it’s child collection kitten filtered out by some sort of restriction. Example:
We have a cat named Tom and it has three kittens Jerry , John and Paul. Now I want a criteria that’ll get me Tom and all his kittens who’s names start with “J” in such a manner that i’ll get an object of class Cat which field kittens has a collection containing Jerry and John. I tried :
Criteria c = this.getSession().createCriteria(Cat.class)
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.add(Restrictions.eq(”name”, “Tom”))
.createCriteria(”kittens”, Criteria.LEFT_JOIN)
.add(Restrictions.like(”name”, “J%”));
but it returns Tom but with all his kittens. Any idea how could I do this using the criteria api ?
9. chanter replies at 12th August 2009, 3:19 am :
Sorry for double posting. It seems that my previous query returns a collection {null, Jerry, John}, so every item from the collection that doesn’t match the criteria is set to null.
10. KK replies at 10th November 2009, 4:24 pm :
After 2 days of R&D (trying to restrict the child collection while getting the parent object in tact) I found #5 comments working like a charm, only that was missing the “fetch” keyword in the join and wasted time. Thanks a lot Bro, I usually don’t comment or reply on forums, but you made me
11. ulath replies at 25th November 2009, 8:01 am :
Java Persistence with Hibernate says; “You never assign an alias to any fetch-joined association or collection for further restriction or projection. So left join fetch i.bids b where b = … is invalid, whereas left join fetch i.bids b join fetch b.bidder is valid.” i tried your solution and the solution in #5 comment without success. any ideas?
12. krikor herlopian replies at 25th February 2010, 5:24 am :
thanks for help
13. ar replies at 21st June 2010, 2:32 pm :
Solution in #5 worked for me.
Leave a comment