select entity where collection contains at least one of

azpublic · **Joined:** Fri Apr 11, 2008 11:45 pm **Posts:** 7

Hi,

This should trivial but I'm banging my head to figure it out ...

I have a Restaurant Entity which has a mapped collection of FoodTypes named foodTypes.

I want to find all the restaurants of which the foodTypes collection contains at least one of the FoodType that are in a Set<FoodType> foodTypesParam that I would pass as a Parameter.

In pseudo HQL that would be something like.

Code:

Set<FoodType> foodTypesParam = new HashSet();
foodTypesParam.add(foodType1);
foodTypesParam.add(foodType2);
foodTypesParam.add(foodType3);


org.hibernate.Query restQuery = getSession().createQuery("from Restaurant r" +
         " where r.foodTypes contains some (:givenItems)")
         .setParameterList("givenItems", foodTypesParam);

I can either do this in HQL or criteria ... I don't mind ....

Any help appreciated.
Many thanks in advance !

rhasselbaum · **Joined:** Thu Oct 07, 2004 4:45 pm **Posts:** 92

Try replacing "contains some" with "in".

stue · **Joined:** Tue Aug 26, 2008 12:18 pm **Posts:** 9

Hi.

you can use the feature of criterias, which allows you to navigate associations using createAlias() or createCriteria(). Check
https://hibernate.bluemars.net/hib_docs ... teria.html , the second example.

In your case the criteria would look something like this:

Code:

DetachedCriteria restaurantsCriteria= DetachedCriteria.forClass(Restaurant.class); // you want to get the list of restaurants
DetachedCriteria foodTypesCriteria = restaurantsCriteria.createCriteria("foodTypes"); //  restrict them according to their food types
Junction junc = Restrictions.disjunction(); // foodTypes should contain 'some' members, not 'all' members of set 
for (FoodType ft : foodTypeParams) {
   junc.add(Restrictions.idEq(ft.getFoodItemId()));
}
foodTypesCriteria.add(junc); // restricion to id's belongs to food types       
restaurantsCriteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);

where restaurantsCriteria is the criteria, which returns you desired restaurants.

Hope it helps.

jimic79 · **Joined:** Mon Oct 19, 2009 2:24 pm **Posts:** 4

I have almost the same problem, but I want to find entities that contain at least the list passed in:

My relationship is Car has many Parts but Part has no reference to Car
Car 1:
- Moonroof
- Spoiler
- Leather Seats

Car 2:
- Leather Seats

Car 3:
- Moonroof
- Leather Seats

If I pass in {Moonroof, Leather Seats} I want the list to contain Car 1 and Car 3 because they both satisfy the minimal requirements.

Car Entity

Code:

@Entity
@Inheritance(strategy=InheritanceType.JOINED)
public class Car extends AbstractEntity 
{
      @OneToMany(fetch=FetchType.EAGER)
      private List<Part> parts = new ArrayList<Part> ();
}

The Part entity is just a basic entity with model num, etc. AbstractEntity has the id and uid values.

Currently, if I add criteria like this: (searchCriteria is the current search criteria built on Car.class, and it already has DISTINCT_ROOT_ENTITY on car):

Code:

DetachedCriteria sc = searchCriteria.createCriteria("parts");
Junction junc = Restrictions.conjunction();
for (Part p : selectedParts)
{
   junc.add(Restrictions.idEq(p.getId()));
}
sc.add(junc);

When i run that as a conjuntion() if i select Moonroof, I get all Cars that have a Moonroof, but if I select Moonroof and Leather Seats, I get no entries. When I set it up as a disjunction() and I select Moonroof and Leather Seats, I get all Cars that have a Moonroof, Leather Seats, or both.

When i run this code, the Executable Criteria looks like this:

Code:

Executable Criteria: CriteriaImpl(org.groupid.site.entities.car.Car:this[Subcriteria(parts:)][model=org.groupid.site.entities.car.Model@3c0e12d9, (id = 4 and id = 54)])

But that Criteria is inherently flawed because no one row in the Car_Part table would have both an id of 4 and 54.

stue · **Joined:** Tue Aug 26, 2008 12:18 pm **Posts:** 9

Try using createAlias method

Code:

searchCriteria.createAlias("parts", "partsAlias");
Junction junc = Restrictions.conjunction();
for (Part p : selectedParts)
{
   junc.add(Restrictions.eq("partsAlias.id", p.getId()));
}
searchCriteria.add(junc);

jimic79 · **Joined:** Mon Oct 19, 2009 2:24 pm **Posts:** 4

I get the same output:

Code:

[STDOUT] Executable Criteria: CriteriaImpl(org.groupid.site.entities.car.Car:this[Subcriteria(parts:p)][model=org.groupid.site.entities.car.Model@3c0e12d9, (p.id = 4 and p.id = 54)])

I think there's a collision between looking for slides that have multiple tags and having distinct root entity on. If you say this works, i'm going to do a little more research into our current setup and I'll post my findings.

Thanks, Be back soon...

[Edit: Spelling]

jimic79 · **Joined:** Mon Oct 19, 2009 2:24 pm **Posts:** 4

Still having the same problem. For a Criteria to work like that, do you need to have a bidirectional mapping? I really don't want to map Part back to Car because it doesn't necessarily need to be associated with a car, whereas a car is made up of parts. Also, parts will most likely down the line point at other parts for upsell, so the queries will only get more complex.

Thoughts?