Adding OR Condition Between two Sub Criterias

Ali Abdel-Aziz · **Posted:** Wed Apr 30, 2008 2:15 am

I have Two Entities one called Step and the other Called TransferChoice

TransferChoice has fromStep and ToStep which both point to the Step

Now I wanna retrieve all TransferChoices that has the specified Step either in fromStep or in toStep relation.

How can this be done as I have

Code:

Criteria stepsTransferChoicesCriteria = this.getSession().createCriteria(StepTransferChoice.class)
                           .setFetchMode("fromStep", FetchMode.JOIN)
                           .setFetchMode("toStep", FetchMode.JOIN);

and two sub criterias that I wanna make OR between them

Code:

stepsTransferChoicesCriteria.createCriteria("fromStep").add(Restrictions.idEq(procedureStepId));

OR

Code:

stepsTransferChoicesCriteria.createCriteria("toStep").add(Restrictions.idEq(procedureStepId));

Can any body helps.

stolley · **Posted:** Wed Apr 30, 2008 2:58 am

http://www.hibernate.org/hib_docs/v3/ap ... sjunction()

Does that help?

Ali Abdel-Aziz · **Posted:** Wed Apr 30, 2008 3:22 am

I have two sub Criteria which is not Criterion
so how to get Criterion from Criteria ?

and if this is possible I will use Restriction.or imediatly.

ferozz · **Posted:** Wed Apr 30, 2008 3:30 am

try this:

Restrictions.or(Property.forName("name").in(subquery1), Property.forName("name").in(subquery2));

Ali Abdel-Aziz · **Posted:** Wed Apr 30, 2008 5:03 am

First of All thank you all,

and I guess the solution is very close by using Property.forName

but I still have some questions and problems?

How will I use the Property.forName

my attempt was to use it as follows:

Code:

Criteria stepsTransferChoicesCriteria = this.getSession().createCriteria(StepTransferChoice.class)
                                      .setFetchMode("fromStep", FetchMode.JOIN)
                                      .setFetchMode("toStep", FetchMode.JOIN);

Criteria fromStepCriteria = stepsTransferChoicesCriteria.createCriteria("fromStep");
Criteria toStepCriteria = stepsTransferChoicesCriteria.createCriteria("toStep");

Line 01: // if the follwoing two lines commented still we have problem
Line 02: fromStepCriteria.add(Restrictions.idEq(procedureStepId));
Line 03: toStepCriteria.add(Restrictions.idEq(procedureStepId));            

stepsTransferChoicesCriteria.add(Restrictions.or(Property.forName("id").in(fromStepCriteria.list()), 
                                                             Property.forName("id").in(toStepCriteria.list())) );

I think I still miss-using the Property.forName

as with the existence of the three labeled lines 01, 02, 03

it generate the following query:

Code:

select *
  from STEP_TRANSFER_CHOICE this_ inner join PROCEDURE_STEP procedures2_ on this_.TO_STEP_FK=procedures2_.ID 
                                  inner join PROCEDURE_STEP procedures1_ on this_.FROM_STEP_FK=procedures1_.ID 
 where procedures1_.ID = ? and procedures2_.ID = ? and (this_.ID in () or this_.ID in ())

and by commenting them it generate the following query:

Code:

select *
  from STEP_TRANSFER_CHOICE this_ inner join PROCEDURE_STEP procedures2_ on this_.TO_STEP_FK=procedures2_.ID 
                                  inner join PROCEDURE_STEP procedures1_ on this_.FROM_STEP_FK=procedures1_.ID 
 where (this_.ID in (?, ?, ?, ?, ?, ?) or this_.ID in (?, ?, ?, ?, ?, ?))

which both queries is not correct.

I guess there should be some mean to refer to the id of the first Sub Criteria some alias.

ferozz · **Posted:** Wed Apr 30, 2008 5:45 am

i suppose that forName("id") -> instead of id u should use "fromStep" and to "Step"

Ali Abdel-Aziz · **Posted:** Wed Apr 30, 2008 5:48 am

I have altered my implementation as follows:

Code:

Criteria stepsTransferChoicesCriteria = this.getSession().createCriteria(StepTransferChoice.class, "transferChoice");

Criteria stepCriteria = this.getSession().createCriteria(ProcedureStep.class)
                                            .add(Restrictions.idEq(procedureStepId));

stepsTransferChoicesCriteria.add(Restrictions.or(Property.forName("transferChoice.fromStep").in(stepCriteria.list()) ,                                 Property.forName("transferChoice.toStep").in(stepCriteria.list()) ) );

and this yields the following query:

Code:

select * 
  from STEP_TRANSFER_CHOICE this_ 
 where (this_.FROM_STEP_FK in (?) or this_.TO_STEP_FK in (?))

and it works fine.