how to find users with a single role in a many to many relationship?

Question

Lets say i have two classes User and Role and a composite class UserRole.

This is a many to many relationship. With groovy i want to count the total users that have the only role USER_ROLE. How can i do so?

class User{

  String name

}

class Role{

  String authority

}

class UserRole{

  User user
  Role role

}

I have only put relevent information.

I want to form a gorm query such as

def result = UserRole.createCriteria().list(){
    eq('role', Role.get(1)) //the Role with id 1 is USER_ROLE    
}

so that i can get the count of users with the only role USER_ROLE. I appreciate any help! Thanks!

Answer 1

without adding the hasMany to your role and user domains (you could add a hasMany from User to UserRole, but you should not add one from Role to UserRole), this HQL Query should do what you want.

User.executeQuery("\
        SELECT  u \
        FROM    User AS u \
        WHERE   EXISTS ( \
                SELECT  1 \
                FROM    UserRole AS ur_a \
                WHERE   ur_a.user = u \
                    AND ur_a.role = :searchRole \
            ) \
            AND NOT EXISTS ( \
                SELECT  1 \
                FROM    UserRole AS ur_b \
                WHERE   ur_b.user = u \
                    AND ur_b.role != :searchRole \
            ) \
    ", [searchRole: Role.get(1)])

But these kinds of Select usually perform poorly on a database. It's ok for maintenance functions or if it will not be executed often.