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

0
votes

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!

2
grailsgrails-orm
def users = UserRole.findAll{role=='USER_ROLE'}.size() is the count - V H
but this will also give users with other roles including USER_ROLE. - kofhearts
you may need to run two separate commands to find users with and without then compare and parse out what you want/not want. good luck - V H
Also if you then iterate through each user from the first find All then check user's role size. All those that have 1 are what you are looking for. - V H

2 Answers

1
votes

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.

0
votes

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    
}