Hi,

I'd like to write a query that only returns objects where
the elements of a set property meet certain conditions.

This is what I tried but it doesn't seem to be correct

String query = "select profile from Profile as profile " +
"where exists c in elements(profile.criteria) and " +
"c.attribute = 'blue'";

Thanks for any tips.
--
Nick


Hibernate version: 3.1

Mapping documents:

<class name="Profile" table="PROFILES">
<id name="id" column="PROFILE_ID">
<generator class="native"/>
</id>

<property name="name"/>

<set name="scores" table="ProfileScores">
<key column="PROFILE_ID"/>
<many-to-many column="SCORE_ID"
unique="true"
class="Profile$Score"/>
</set>

<set name="criteria" table="ProfileCriteria">
<key column="PROFILE_ID"/>
<many-to-many column="CRITERIA_ID"
unique="true"
class="Profile$Criteria"/>
</set>

</class>


Code between sessionFactory.openSession() and session.close():

String query = "select profile from Profile as profile, Profile$Criteria as criteria " +
"where exists c in elements(profile.criteria) and " +
"c.attribute = 'political'";

//"criteria.attribute = 'diet'";

i = hSession.createQuery(query).list().iterator();

Name and version of the database you are using:

hsqldb

Try

Thanks!

Now what if I'd like to impose a condition on two or more elements of the Set. For instance, the condition that there exists a 'blue' and 'red' attribute in the Set.
--
Nick

Actually, what I'm really after is a query that imposes several conditions on an element of the Set. Something like

select profile
from Profile profile
where (profile.criteria.attribute='blue' and
profile.criteria.value = 10)
and
(profile.criteria.attribute='green' and
profile.criteria.value = 5)

In words, selecting Profiles having one criteria element
with attribute='blue', value = 10 and another element with
attribute = 'green' and value = 5. Thanks for any tips.
--
Nick

I am facing the same problem now, but unfortunately there is no answer to this posting yet.
Does anyone know a solution to this or might point me to the right direction?

Thanks
Thomas

element is tricky.
maybe this can help you ;-)



*g
Helmut

I'm trying to achieve the same thing, but the example given by Helmut doesn't work. "Unexpected token: elements"... As far as I know elements can only be used in the where clause. Is there any other way to get this done?