Hibernate version: 3.1.3

Mapping documents:
<class name="Request" table="REQUESTS">
...
<set name="values" lazy="true" cascade="all-delete-orphan">
<key column="REQUEST_ID"/>
<one-to-many class="AttributeValue"/>
</set>
</class>

<class name="AttributeValue" table="ATTRIBUTE_VALUES">
<property name="displayValue" type="string" column="DISPLAY_VALUE"/>
<set name="valueOptions" lazy="true" cascade="all-delete-orphan">
<key column="ATTRIBUTE_VALUE_ID"/>
<one-to-many class="ValueOption"/>
</set>
</class>

<class name="ValueOption" table="VALUEOPTIONS">
<id name="id" type="long" column="VALUEOPTION_ID" unsaved-value="0">
<generator class="identity"/>
</id>

<property name="value" column="VALUE" type="string" not-null="true"/>
</class>

Name and version of the database you are using:MYSQL 5.x

The generated SQL (show_sql=true):


Hi,
Having a trouble with the criteria API:

Criteria criteria = session.createCriteria(Request.class)
.add(Restrictions.eq("isDeleted", Boolean.FALSE))
.add( Restrictions.eq ("application.id", query.getApplication().getId()))
.createAlias ("submitter", "submitter")
.createAlias ("values", "attributeValue")
.createAlias ("attributeValue.attribute", "attribute")
.createAlias ("attributeValue.valueOptions", "valueOption");

criteria.add(Restrictions.and (Restrictions.eq("attribute.id", queryCriteria.getAttribute().getId()), Restrictions.in("attributeValue.displayValue", String [] {.......})));

the problem is that I do not get any matched items although there are items matching the criteria.

However, it works if I compare valueOption like this:
criteria.add(Restrictions.and (Restrictions.eq("attribute.id", queryCriteria.getAttribute().getId()), Restrictions.in("valueOption.value", String [] {.......})));

If I remove .createAlias ("attributeValue.valueOptions", "valueOption"), the first one works.

So does this mean I can not have both :
.createAlias ("attributeValue.attribute", "attribute")
.createAlias ("attributeValue.valueOptions", "valueOption");

or are there anything wrong in my query ?

thanks
lixin

captured SQL (simplified a bit):

select this_.REQUEST_ID as REQUEST1_25_20_

from REQUESTS this_
inner join USERS submitter1_ on this_.USER_ID=submitter1_.USER_ID
inner join ATTRIBUTE_VALUES attributev2_ on this_.REQUEST_ID=attributev2_.REQUEST_ID
inner join ATTRIBUTE attribute3_ on attributev2_.attribute=attribute3_.ATTRIBUTE_ID
left outer join REQUESTS request24_ on attributev2_.REQUEST_ID=request24_.REQUEST_ID
inner join VALUEOPTIONS valueoptio4_ on attributev2_.ATTRIBUTE_VALUE_ID=valueoptio4_.ATTRIBUTE_VALUE_ID

where this_.ISDELETED=? and this_.APPLICATION_ID=? and (attribute3_.ATTRIBUTE_ID=? and attributev2_.DISPLAY_VALUE like ?) order by this_.CREATE_TIME asc

I suspect that this problem is caused by the last createAlias :
--------------
Criteria criteria = session.createCriteria(Request.class)
.add(Restrictions.eq("isDeleted", Boolean.FALSE))
.add( Restrictions.eq ("application.id", query.getApplication().getId()))
.createAlias ("submitter", "submitter")
.createAlias ("values", "attributeValue")
.createAlias ("attributeValue.attribute", "attribute")
.createAlias ("attributeValue.valueOptions", "valueOption");
--------------

After this, I added restriction like .add (Restrictions.eq ("attributeValue.value", "some value"); this does not return any items;
but it returns found items if I do not have the last .createAlias.

So does this mean and restrictions are for the last alias ?