Hi,

i have some persistent objects which contains a set of Long values:

<class name="Task" table="tasks" polymorphism="explicit">
<id name="id" column="id" type="long">
<generator class="com.evermind.tools.hibernate.UniqueIdGenerator"/>
</id>
<!--(some properties)-->

<set name="concerned" table="task_concerned">
<key column="task_id"/>
<element column="user_id" type="long"/>
</set>
<!-- ... -->

The mapping between user and task is non-relational (task contains a list of concerned user ids but there's no constraint that forces the user to exist).

Now i want to query all tasks which contains a certain user id. I tried
"from Task t where elements(t.concerned)=42". This fails because it creates a subselect which is not supported by mysql.

When i try "from Task t where t.concerned=42" i gent an exception that the collection is not indexed.

How must i do this query?

Hi, did you get a solution?

Well...MySQL supports subqueries since version 4.1.x.

Yes. One solution is just to use a database with subqueries (most do so).

A different one is to use explicitely a join (left join or cross join).
Something like "from task t left join concerned c where c.user_id=42" or (alternatively usefull in some other cases) "from task t, concerned c where t.id=c.task_id and c.user_id=42" (both are examples and untested but show how it can ce done).

Michael.