Hibernate version: hibernate-3.1

I want to write a query by Criteria which ORs according to values in associated tables. The problem is that building the query results in ANDs when using Criteria.createCriteria().

final Criteria class1Crit = session.createCriteria(Class1.class);
class1Crit.add(Restrictions.eq("classState", state));

final Criteria other1Crit = class1Crit.createCriteria("other1");
other1Crit.add(Restrictions.eq("otherState", state));

Results in SQL:

...
where
this_.class_state = ?
and other1.3_2_.other_state = ?
...

How can a query by Criteria be constructed to result in:

...
where
this_.class_state = ?
or other1.3_2_.other_state = ?
...

Is it possible to "OR" on associated table values or is the Criteria API missing some functionality?

Refer also to my previous unanswered post "criteria: polymorphism and association OR-ing questions" from Fri Aug 25, 2006 2:35 pm for more detail.

Try wrapping both of the restrictions in a Restrictions.or (restriction 1, restriction 2), and it should work.

Thanks for your reply.

I have tried this approach but the problem is that 'restriction 1' (Restrictions.eq("classState", state)) and 'restriction 2' (Restrictions.eq("otherState", state)) are on separate classes/tables (Class1.class and the class associated via association other1).

This means that 'restriction 1' has to be added to the Criteria "class1Crit" and 'restriction 2' has to be added to the Criteria "other1crit" such that they cannot be combined using the 'Restrictions.or' as far as I can tell.

There is another posting which gives a bit more detail of this problem at http://forum.hibernate.org/viewtopic.ph ... tion+oring in scenario 1 which may illustrate the problem more clearly.

If you think I am wrong, which is more than likely, or I have misinterpreted what you mean, could you post some code to demonstrate your solution and I'll give it a go.

Thanks again.

Here's a query I have that works. It uses aliases, which is the trick I found to make restrictions easier to apply.

final Criteria criteria = hsession
.createCriteria(PhenoAttributeHistory.class);

criteria.createAlias("phenoAttributes", "pa")
.createAlias("pa.phenoProjAttributeType", "ppat")
.createAlias("ppat.phenoAttributeType", "pat")
.add(Restrictions.or(
(Restrictions.and(Restrictions.eq("pat.name", "BMI"), Restrictions.gt ("pa.valueDouble", 20D))),
(Restrictions.and(Restrictions.eq("pat.name", "Weight"), Restrictions.gt("pa.valueDouble", 20D)))
));

You'll see a nesting of restrictions here.

The first tier of restriction is an AND: name = X and value = Y.
The second tier takes two tier one restrictions and OR's them.

-Jim

I have tried using the aliasing feature and it does what you say, it allows me to avoid using the Criteria.add() method with its default AND behaviour and enables me to structure a disjunction built up from the aliased Criteria.

Thanks for your help with this.