Criteria for associated collection property

lasse_stromberg · **Joined:** Wed Apr 05, 2006 5:01 am **Posts:** 20

Hibernate version: 3.1

I have two domain classes Physician and Speciality with a many-to-many relationship.
A Physician contains a Set of Speciality. I want my user to select one or more Speciality and with these i want to construct
a query with the Criteria API to search for all Physician objects that contain the specified Speciality(s). I think i need to somehow join
the Speciality with createCriteria("specialities") but i dont know how i can specify the whole query. I'm really lost at the moment and would appreciate any help! Thx in advance!

Code:

class Physician {

private Set<Speciality> specialities;

// getters & setters...

}

Here the mapping files:

Code:

<hibernate-mapping package="domain">

     <class name="Physician" table="physician">
        
        <id name="physician_id" type="long">
            <column name="physician_id"/>
            <generator class="sequence">
               <param name="sequence">physician_sequence</param>
            </generator>
        </id>
      
   
       <property name="title"   column="title"   type="string"/>
       <property name="firstname"   column="firstname"   type="string"/>
       <property name="lastname"   column="lastname"   type="string"/>
       <property name="gender"   column="gender"   type="character"/>        
       <set name="specialities" table="physician_specialities" cascade="save-update" lazy="true">
        <key column="physician_id"/>
        <many-to-many column="speciality_id"
            class="Speciality"/>
        </set>
       
    </class>
    
</hibernate-mapping>

Code:

<hibernate-mapping package="domain">

    <class name="Speciality" table="speciality">        
        <id name="speciality_id" type="long">
            <column name="speciality_id"/>
            <generator class="sequence">
               <param name="sequence">speciality_sequence</param>
            </generator>
        </id>
              
      <property name="speciality_code"  column="speciality_code"  type="int" not-null="true"/>
       <property name="name"   column="name"   type="string"/>  
    </class>
</hibernate-mapping>

jbosseur · **Posted:** Tue May 23, 2006 11:22 am

How about somthing like this:

Code:

   // create a collection of the selected specialities
  Set specialties = new HashSet();
  for (selecteditems....) {
     specialties.add(new Speciality());
  }
  
  List l = session.createCriteria(Physician.class, "ph")
                     .createCriteria("specialities")
                     .add(Restrictions.in(specialties ))
                     .list();

There are probebly other ways.... but this is one I thought of....

Good luck...!!

bkmr_77 · **Joined:** Tue Apr 25, 2006 12:04 pm **Posts:** 260

try this code also.

Code:

DetachedCriteria specialityCrit = DetachedCriteria.forClass( Speciality.class , "spcl");
specialityCrit.setProjection( Property.forName("spcl.specialityId"));
specialityCrit.add( Restrictions.eq( "spcl.specialityId", new Long( 1 ) ) );

Criteria physicianCrit = session.createCriteria( Physician.class, "phsn" );
physicianCrit.add( Property.forName( "phsn.specialityId" ).in( specialityCrit ) );

lasse_stromberg · **Joined:** Wed Apr 05, 2006 5:01 am **Posts:** 20

Hallo JBosseur,

i tried your code but it is not working.

The first problem is, that Restrictions.in() has only a constructor
for two parameters, one is a collection and the other the property name.
So i changed your code to:

Code:

List l = session.createCriteria(Physician.class, "ph")
                     .createCriteria("specialities")
                     .add(Restrictions.in("ph.specialities",specialities ))
                     .list(); 

Running this code throws a SQLException: ORA-01008 Not all variables bound.

The generated sql looks really strange (physician_specialities is my association-table):

Code:

select * from ( select this_.physician_id as physician1_0_1_, specialiti3_.physician_id as physician1_, speciality1_.speciality_id as speciality2_, speciality1_.speciality_id as speciality1_5_0_, speciality1_.speciality_code as speciality2_5_0_, speciality1_.name as name5_0_ from physician this_ inner join physician_specialities specialiti3_ on this_.physician_id=specialiti3_.physician_id inner join speciality speciality1_ on specialiti3_.speciality_id=speciality1_.speciality_id where 1=1 and this_.physician_id in (?) ) where rownum <= ?

lasse_stromberg · **Joined:** Wed Apr 05, 2006 5:01 am **Posts:** 20

Hallo bkmr_77,

i also tried your code with no luck. I modified it with a for-loop
to insert Restrictions for all selected Speciality:

Code:

 
DetachedCriteria specialityCrit = DetachedCriteria.forClass( Speciality.class , "spcl");
            specialityCrit.setProjection( Property.forName("spcl.speciality_id"));
            for (Speciality spec : mySetWithSelectedSpecialities) {
                
                specialityCrit.add( Restrictions.eq( "spcl.speciality_id", spec.getSpeciality_id() ) );
            }         
Criteria physicianCrit = session.createCriteria( Physician.class, "phsn" );             
physicianCrit.add( Property.forName( "phsn.specialities" ).in( specialityCrit ) );

--> The query returns only one object, but it should return several thousand...

The generated sql is:

Code:

select * from ( select this_.physician_id as physician1_0_0_,  
from physician this_ where 1=1 and this_.physician_id in (select this0__.speciality_id as y0_ from speciality this0__ where this0__.speciality_id=?) ) where rownum <= ?

jbosseur · **Posted:** Wed May 24, 2006 6:22 am

Hi lasse_stromberg,

Ok than... I see I made a mistake there

Code:

  /*pass through the specialities selected I guess you have the ID if not 
    choose another property*/
  Set specialties = new HashSet();
  for (selecteditems....) {
     specialties.add(new Long(id_speciality));
  } 

   List l = session.createCriteria(Physician.class, "ph")
                     .createCriteria("specialities")
                     .add(Restrictions.in("speciality_id",specialities ))
                     .list();

I tried this and worked for me... (not with your code but somethint similar)

Good luck!

lasse_stromberg · **Joined:** Wed Apr 05, 2006 5:01 am **Posts:** 20

hello jbosseur,

thanks for your input!
Your latest code works for me too!
But using Restrictions.in() combines the selected specialities
with OR (generating a WHERE IN clause) and i need AND.
I need to find a certain physician that has a certain speciality 1 AND
another speciality 2. Using your code sample i get all physicians that either have Spec1 or Spec2.
Do you have an idea for that too? Thx for your help so far!

jbosseur · **Posted:** Wed May 24, 2006 8:03 am

Ahhhh I didn´s see you wanted a physician with all specialities
selected so than I think somethink like this would work:

Code:

Criteria c = session.createCriteria(Physician.class, "ph")
                   .createCriteria("specialities") ;

for ( specialities selected ) {
     c = c.add(Restrictions.idEq(Long.valueOf(<speciality id>)))
}
 
List l = c.list();

I am not sure if there is an easier / better way but I think this works fine
and serves your purpose...

Good Luck.
   

lasse_stromberg · **Joined:** Wed Apr 05, 2006 5:01 am **Posts:** 20

still struggling ;-) ...

i tried the following:

Code:

Criteria criteria = session.createCriteria(Physician.class, "ph")
Criteria specCrit = criteria.createCriteria("specialities");
            Conjunction conjunction = Restrictions.conjunction();
            for (Speciality spec : setWithSelectedSpecialities) {
                conjunction.add(Restrictions.eq("speciality_id", spec.getSpeciality_id()));
            }
            specCrit.add(conjunction);

But it always returns 0.

The generated sql is:

Code:

select * from ( select this_.physician_id as physician1_0_1_, tspecialiti3_.physician_id as physician1_, speciality1_.speciality_id as speciality2_, speciality1_.speciality_id as speciality1_5_0_, speciality1_.speciality_code as speciality2_5_0_, speciality1_.name as name5_0_ from physician this_ inner join physician_specialities specialiti3_ on this_.physician_id=specialiti3_.physician_id inner join speciality speciality1_ on specialiti3_.speciality_id=speciality1_.speciality_id where 1=1 and (speciality1_.speciality_id=? and speciality1_.speciality_id=?) ) where rownum <= ?

lasse_stromberg · **Joined:** Wed Apr 05, 2006 5:01 am **Posts:** 20

hi jbosseur,
i tried your last suggestion and it returns 0 (if specify multiple specialities, for a single speciality it works) like my last example!
But i cant figure out why...

jbosseur · **Posted:** Wed May 24, 2006 8:24 am

lasse_stromberg wrote:

hi jbosseur,
i tried your last suggestion and it returns 0 (if specify multiple specialities, for a single speciality it works) like my last example!
But i cant figure out why...

Are there any physicians who have more than one speciality?
Check the sql to see if it generates what you expect......

I didn´s have a realy good example to work with so....

OK so what you want is basicly an SQL like this

Select....
FROM physicians
WHERE EXISTS (
SELECT 1 FROM peciality WHERE speciality_id = <>
)
AND
EXISTS (
SELECT 1 FROM peciality WHERE speciality_id = <>
)

I´ll have a look to see how to do that

jbosseur · **Posted:** Wed May 24, 2006 9:35 am

Ok than i played arround a little and I think this way is not possible
because of a bug in hibernate but I am not absolutely sure about that

But I rewrote the query like this

Code:

Select 
FROM physician 
WHERE 
     physician_id IN (
            SELECT physician_id FROM Speciality
         WHERE speciality_id = <selected_speciality1>
)
AND
     physician_id IN (
            SELECT physician_id FROM Speciality
         WHERE speciality_id = <selected_speciality2>
)
and
etc....

This makes the Criteria liek this:

Code:

   Criteria c = session.createCriteria(Physcician.class,"p");

   for (Iterator iter = l1.iterator(); iter.hasNext();) {
      B element = (B) iter.next();
   
      DetachedCriteria dc = DetachedCriteria.forClass(Speciality.class,"s")
      .setProjection( Property.forName("<physcian_id>") )
      .add(Restrictions.idEq(element.getId()))
.add(Property.forName("s.physcian_id").eqProperty("p.physcian_id"));
         
         c = c.add(Property.forName("physcian_id").in(dc));
      }

   List l2 = c.list();

Try this and let me know if it worked.....

jbosseur · **Posted:** Wed May 24, 2006 9:51 am

I got it to work the other way as well with exists it ends up like this
and I think it shows a bit better waht is going on I think

Code:

      Criteria c = session.createCriteria(Physician.class,"p");
      

      for (specialities selected....) {
         Long id = Long.valueOf(iter.next());
      System.out.println(element.getId());
         DetachedCriteria dc = DetachedCriteria.forClass(Speciality.class,"s")
         .setProjection( Property.forName("id_speciality") )
         .add(Restrictions.idEq(id))
         .add(Property.forName("s.physcian_id").eqProperty("p.physcian_id"));
         
         c = c.add(Subqueries.exists(dc));
         }

      List l2 = c.list();

lasse_stromberg · **Joined:** Wed Apr 05, 2006 5:01 am **Posts:** 20

Sorry, this:

Code:

Property.forName("s.physician_id").eqProperty("p.physician_id"));

is not working because physician_id is not a property of Speciality.

Physician and Speciality are linked over a association table because of
their many-to-many relationship.
I'm not sure if it is possible to do what i want with the Criteria API.

jbosseur · **Posted:** Wed May 24, 2006 10:25 am

I saw in your mapping that you don´t have a collection od Physicians at this moment but in that case I think you can are right and you can´t get this done ate leats not very easily.....
I will try to think about it a bit more but the easiest way for sure would be to create a Collection of physcians of the speciality side...