Hi.

I use hibernate 3.2 and I try find out how is possible to compare two sets in HQL. I have two classes:

class A {
Set<User> users;
...
}

class B {
Set<User> users;
...
}

with appropriate mapping in .hbm. My goal is to select all objects of class 'A'
which all of users are contained in collection of users of class 'B'.

Something like this (pseudo query):
select a from A as a, B as b where b.id = 'xy' and b.users.containsAll(a.users)

How it can be done with hibernate?

Thank for any help
Filip

well, you can either do it in memory, or do it on the database side. for the database side just use regular hql or sql. That whole b.users.containsAll(a.users) thing is most likely not be supported.



to do it in memory, just load up both the sets and run a little loop.

Thank, I can do it, but it is not good solution for me because the database will
contain thousands of instances of both classes. Can you explain how to do it on the database side, please ?

My goal is not to select users but instances of class 'A' .

b.users.containsAll(a.users) means that a.users is a SUBSET of b.users - not that the sets are equal. Is this your requirement?

If so, try this (xlate to formal syntax):

select A.id from A where
exists (
(select from b_users where bid='xy') minus (select from a_users where aid = a.id)
)

note: I assumed that each user has a parent_id (aid or bid)