Hello there, I am again with you to share a solution of a problem that I have faced in a project and I solve that problem using hibernate MEMBER OF operator. Requirement is simple, I have a Post domain which has many users:

class Post {
    String content

    static hasMany = [users: User]
}

and I was required to get all the posts which do not contain a given user in their users hasMany collection property.

Suppose we have 3 Post instances

  1. post1 has user1 and user4 in users.
  2. post2 has user2 and user3 in users.
  3. post3 has user3 and user4 in users.

If I want all the posts which do not have user2 then the correct result is post1 and post3.

To get the desired result my first attempt was criteria query

User user = user2
List<Post> posts = Post.createCriteria().listDistinct {
    users {
        ne 'id', user.id
    }
}

and the result is [post1, post2, post3] 🙁 .

Then I use hql

List<Post> posts = Post.executeQuery("select distinct p from Post p " +
                "inner join p.users users where :user not in users", [user: user])

again the result is [post1, post2, post3] 🙁 .

After lot of digging I found MEMBER OF operator of hql which gives me the desired result.

  • The MEMBER OF operator returns all instances of a class that have a particular instance in their collection property.
List<Post> posts = Post.executeQuery("select distinct p from Post p " +
                "where :user not member of p.users", [user: user])

output: [post1, post3] 🙂 .


I have enabled my sql logs in DataSource.groovy file. Following are sql queries fired with different options:

with createCriteria:-

select this_.id as id3_1_, this_.version as version3_1_, this_.content as content3_1_, users3_.post_users_id as
post1_3_, users_alia1_.id as user2_, users_alia1_.id as id2_0_, users_alia1_.version as version2_0_,
users_alia1_.account_expired as account3_2_0_, users_alia1_.account_locked as account4_2_0_, users_alia1_.enabled
as enabled2_0_, users_alia1_.&quot;password&quot; as password6_2_0_, users_alia1_.password_expired as
password7_2_0_, users_alia1_.username as username2_0_ from post this_ inner join post_user users3_ on
this_.id=users3_.post_users_id inner join user users_alia1_ on users3_.user_id=users_alia1_.id where
(users_alia1_.id<>?)

with IN operator:-

select distinct post0_.id as id3_, post0_.version as version3_, post0_.content as content3_ from post post0_
inner join post_user users1_ on post0_.id=users1_.post_users_id inner join user user2_ on
users1_.user_id=user2_.id where ? not in  (user2_.id)

with MEMBER OF operator:-

select distinct post0_.id as id3_, post0_.version as version3_, post0_.content as content3_ from post post0_
where ? not in  (select user2_.id from post_user users1_, user user2_ where post0_.id=users1_.post_users_id and
users1_.user_id=user2_.id)

Hope this helps. 🙂