Hello All,

I am trying to implement a query that tries to do in pseudo code

select from entity where
ref1.subref.name = 'value1'
or
ref1.subref.name = 'value2'
or
ref2.subref.name = 'value1'

i can only add a disjunction to subcritaria or alias that is created on ref1.subref. I cannot add add the same disjunction on ref2.subref. The output of the where in that case becomes
where
(
ref1_.name=?
or ref1_.name=?
or ref1_.name=?
)
and (
ref2_.name=?
or ref2_.name=?
or ref2_.name=?
)



i like to have an output like

where
(
ref1_.name=?
or ref1_.name=?
or ref2_.name=?
)


I cannot make up from the documentation or other posts how i should do this.

version 3.1.2

I try to be more concreet :

After stripping all the not relevant parts my hbm files look like

TableModel
<class name="TableModel" table="tablemodel" lazy="true">
<set name="fieldList">
<key column="parenttableModelId"/>
<one-to-many class="com.myvision.model.Field"/>
</set>
<set name="referenceList">
<key column="parenttableModelId"/>
<one-to-many class="com.myvision.model.Reference"/>
</set>
</class>

Field
<class name="Field" table="field" lazy="true">
<many-to-one
name="fieldType"
class="FieldType"
column="fieldTypeId"/></class>

FieldType
<class name="FieldType" table="fieldtype" lazy="true">
<property name="name"/>
</class>

Reference
<class name="Reference" table="reference" lazy="true">
<many-to-one
name="entity"
class="TableModel"
column="entityId"/> </class>


My application code is :

ICriteria newCriteria =
GenericDelegate.newDistinctCriteria(TableModel.class);
IOr or = newCriteria.or();
or.eq("fieldList.fieldType.name","text");
or.eq("referenceList.entity.name","FieldType");
Collection <TableModel> list = newCriteria.list();
for (TableModel model : list) {
System.err.println(model.getName());
}
GenericDelegate.closeORMSession();


desired sql (compact)

SELECT DISTINCT * FROM
tablemodel T
JOIN field F ON T.id=F.parenttablemodelid
JOIN fieldtype FT ON F.fieldtypeid=FT.id
JOIN reference R ON T.id=R.parenttablemodelid
JOIN tablemodel T2 ON R.entityid=T2.id
WHERE FT.name = ? OR T2.name = ?

try

Session s = HibernateDelegate.currentSession();
Criteria c = s.createCriteria(TableModel.class);
Criteria createAlias -or- Criteria =
c.createAlias("fieldList.fieldType","fieldList_fieldType");
createAlias.add(Restrictions.eq("fieldList_fieldType.name", "yesNo" ));

gives
[ERROR] JDBCExceptionReporter - Unknown table 'fieldlist_1_' in where clause


try

Session s = HibernateDelegate.currentSession();
Criteria c = s.createCriteria(TableModel.class);

Disjunction disjunction = Restrictions.disjunction();
disjunction.add(Restrictions.eq("name", "yesNo" ));
disjunction.add(Restrictions.eq("name", "text" ));

Criteria sub =
c.createCriteria("fieldList")
.createCriteria("fieldType");

Criteria sub2 =
c.createCriteria("referenceList")
.createCriteria("entity");

c.add(disjunction);

i get sql

select *
from
tablemodel this_
inner join
field field1_
on this_.id=field1_.parenttableModelId
inner join
fieldtype fieldtype2_
on field1_.fieldTypeId=fieldtype2_.id
inner join
reference reference3_
on this_.id=reference3_.parenttableModelId
inner join
tablemodel tablemodel4_
on reference3_.entityId=tablemodel4_.id
where
(
this_.name=?
or this_.name=?
)

try

Session s = HibernateDelegate.currentSession();
Criteria c = s.createCriteria(TableModel.class);

Disjunction disjunction = Restrictions.disjunction();
disjunction.add(Restrictions.eq("name", "yesNo" ));
disjunction.add(Restrictions.eq("name", "text" ));

Criteria sub =
c.createCriteria("fieldList")
.createCriteria("fieldType");

Criteria sub2 =
c.createCriteria("referenceList")
.createCriteria("entity");

sub2.add(disjunction);
sub.add(disjunction);

gives sql
select
from
tablemodel this_
inner join
field field1_
on this_.id=field1_.parenttableModelId
inner join
fieldtype fieldtype2_
on field1_.fieldTypeId=fieldtype2_.id
inner join
reference reference3_
on this_.id=reference3_.parenttableModelId
inner join
tablemodel tablemodel4_
on reference3_.entityId=tablemodel4_.id
where
(
tablemodel4_.name=?
or tablemodel4_.name=?
)
and (
fieldtype2_.name=?
or fieldtype2_.name=?
)



Because createAlias or createCriteria goes only one level deep i cannot make a disjunction on the same references with a depth delta greater than 1. I hope I made the explanation not to complicated. Other posts about this are unanswered or do not offer a solution that works for me. Can somebody help out?

I am wondering if i ask a really stupid question or a really complicated one. The only solution i can think of now is implement my criteria api so that it generates sql wich I can feed to the session.