HQL: Is an element of one collection in another collection?

I want to check whether at least one element of a collection (u.organisations) is contained in another collection (? = excludedOrganisations):

select distinct u from SystemUser u
join u.userGroups g 
join u.organisations o
where 3 in elements(g.permissions) and
EACH_ELEMENT_OF(o) not in (?)

How can I express the EACH_ELEMENT_OF with HQL?

My last trial is:

select distinct u from SystemUser u 
join u.userGroups g 
where 3 in elements(g.permissions) and 
not exists (
    select org from Organisation org 
    where org in elements(u.organisations)
    and org not in (?)
)

Bu开发者_如何学编程t I get the exception:

IllegalArgumentException occurred calling getter of Organisation.id

I guess you need a subquery to express it in SQL, therefore subquery is also needed in HQL:

select u from SystemUser u 
where not exists (
    select 1 from UserGroup g where g.user = u and g not in (?)
)

This is the classical example from Hibernate documentation:

from Cat as cat
left join cat.kittens as kitten
    with kitten.bodyWeight > 10.0

In your case, this would be:

select distinct u from SystemUser u 
join u.userGroups g
join u.organisations o 
where 3 in elements(g.permissions) and 
 o.id not in (?)

I'm assuming the Organisation entity has an id field and you can pass in the list of ids.