-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: Querying a collection with AND criteria
PostPosted: Tue Oct 07, 2008 7:17 pm 
Beginner
Beginner

Joined: Tue Oct 07, 2008 7:05 pm
Posts: 27
I have a question that seems simple enough but I'm having trouble figuring it out. I'm trying to run a query that returns results from a collection where the criteria is "AND'd".

Example - if I had an 'Item' that has a 'categories' collection, I want to return the items that have all categories in my criteria. So the query would be something like: "return all items whose category is 'apples' AND 'oranges'.

This is easy enough to do when OR'ing the categories, but I'm struggling with the AND. Anyone have a simple solution?

Thanks,

JF


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 08, 2008 6:59 am 
Newbie

Joined: Fri Oct 03, 2008 2:30 am
Posts: 16
Location: Neuchâtel, Switzerland
I don't know if it works, but maybe you give it a try:

Code:
select i from Item i, Category c1, Category c2
  where c1.kind = 'apple' and c2.kind = 'orange'
  and c1 member of i.categories and c2 member of i.categories

Without the second Category c2 this is an example out of "Java Persistence with Hibernate"... buy it , read it


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 08, 2008 7:07 am 
Beginner
Beginner

Joined: Tue Sep 09, 2008 5:42 am
Posts: 22
Location: Romania
Ciao,

I will give you an example of how I do this using NHibernate:

Code:
//the list that will contain all the components from your AND statement
List<ICriterion> ANDExpresionComponents=new List<ICriterion>();

//add all your components here
ANDExpresionComponents.Add(Expression.Eq("Categories", "apples"));
ANDExpresionComponents.Add(Expression.Eq("Categories", "oranges"));

........................................................................................................

//get the NHibernate Session
ISession session=NHibernate.Instance;

//this criteria will return the records that match all the added criterias
ICriteria criteria=session.CreateCriteria(typeof(ITEM));

if(ANDExpresionComponents.Count>0)
            {
                ICriterion crit = ANDExpresionComponents[0];

                for (int i = 1; i < ANDExpresionComponents.Count; i++)
                {
                    crit = Expression.And(crit, ANDExpresionComponents[i]);
                }

                criteria.Add(crit);
            }

IList<ITEM> result=criteria.List<ITEM>();


I hope it helps solving your problem


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 08, 2008 1:56 pm 
Beginner
Beginner

Joined: Tue Oct 07, 2008 7:05 pm
Posts: 27
Thanks for the replies. Unfortunately, I won't be able to use the NHIbernate solution as I'm required to keep the solution JPA compatible.

lonny27, I like your solution, but the problem is for every new category I add, another self-join must be made - and there can be many categories. I had actually worked out a similar solution with pure SQL.

But, I think I found a solution that I'll settle with. It goes like this:

Code:
select c.itemId from Category c where c.kind = 'apples' or c.kind = 'oranges' group by c.itemId having count(c.itemId) >= 2


This works fine, except in the case of duplicate categories. If an item had, say, 'apples' twice but no 'oranges' it would show up in the result. While this is possible, it is unlikely.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Oct 08, 2008 2:00 pm 
Beginner
Beginner

Joined: Tue Oct 07, 2008 7:05 pm
Posts: 27
(sorry for the double post, but the message board was blowing up on me.)


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.