HQL Howto: All T w/collection T.c having certain elements?

Jonathan Feinberg · **Joined:** Sun Feb 13, 2005 5:00 pm **Posts:** 5

Hibernate version: 2.1.7
MySQL 4.1

I have a class T with a set of Strings T.c. I wish to retrieve all T whose T.c contains all members of a set of Strings s. I hope to express this in HQL, since I want to do paged retireval. Can it be done?

The set is mapped like so:

Code:

    <set name="c" table="c" lazy="false" inverse="false"
       cascade="none" sort="unsorted">
        <key column="t_id"/>
        <element column="the_string" type="string" not-null="false" unique="false" />
    </set>

I can retrieve Ts having any single given String in T.c with

Code:

    select t from T t
        left join t.c c
        where c = ?

which is simple enough. Can I do what I'm hoping to do?

Many thanks for any help.

Jonathan Feinberg · **Joined:** Sun Feb 13, 2005 5:00 pm **Posts:** 5

Based on a clever example from the docs, I have come up with an answer to my own question:

Code:

select t from T t
    join t.c c
    where c in (:required_values)
    group by t.id
    having count(c) = :required_values_count