Quering in collections of complex values

fggarcia · **Posted:** Fri Oct 06, 2006 3:57 pm

Hi, I have a some tables called Person, Roles and Person_Roles. I have something similar than this:

Code:

<class name="model.Person" table="PERSON">
    <id name="id">
        <generator class="identity" />
    </id>
      ......
    <set table="person_roles" name="roles">
      <key column="person_id"/>
      <many-to-many column="role_id" class="model.Role"/>
    </set>
</class> 

I want query something like this:

SELECT p.id, p.name FROM person p , person_roles pr WHERE p.role_id = pr.role_id and pr.role_id in (1,2,3);

I want the person who have 1, 2 or 3 role type.

Its possible do something like the following?:

Code:

from Person p where p.roles.id in (1,2,3) ?

Thanks for any help

jaikiran · **Joined:** Wed Jul 27, 2005 2:33 am **Posts:** 118

Try this out:

Code:

from Person p join p.roles r where r.id in (1,2,3)

syfsyf · **Joined:** Mon Dec 31, 2007 1:35 pm **Posts:** 1

jaikiran wrote:

Try this out:

Code:

from Person p join p.roles r where r.id in (1,2,3)

Hi,
Is this possible to rewrite this query(or similar to this) using DetachedCriteria? I was trying but i always fail :(