kofhearts
kofhearts

Reputation: 3774

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

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!

Upvotes: 0

Views: 240

Answers (2)

norganos
norganos

Reputation: 141

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.

Upvotes: 1

Khal
Khal

Reputation: 76

Break the many-many into two one-many relations by also modeling the join table (which is your composite class)

class User{

  String name
  static hasMany = [userRoles:UserRole]
}

class Role{

  String authority
  static hasMany = [userRoles:UserRole]
}

class UserRole{

  static belongsTo = [user:User, role:Role]
}

Now fire your query on UserRole:

def result = UserRole.createCriteria().list(){ 

     eq('role', Role.get(1))  //the Role with id 1 is USER_ROLE    
}

Upvotes: 0

Related Questions