Reputation: 3774
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
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
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