Grails Criteria - list contents

Question

These are my domain objects

User{
 hasMany = {roles: UserRole}
}

UserRole{
  User user
  Role role
}

Role{
  String authority
}

I need to find users based on their Role. For that I am trying to use the following criteria:

  def role_admin = Role.findByAuthority('ROLE_ADMIN')
  def criteria = new DetachedCriteria(User).build {
    roles{
      role{
        idEq(role_admin.id)
      }
    }
  }

  result.users = criteria.list(params)
  result.total = criteria.count()

The above will always return one result, even though I have verified by looking at the database directly that there should be more results. The params passed to list are correct, but I tried removing them just to be sure. I can't see what is wrong with the above, any suggestions ?

I also tried this

roles{
    role{
        eq("authority","ROLE_ADMIN")
    }
}

But it is throwing exception:

Unknown column 'role_alias2_.authority' in 'where clause'

Answer 1

this works for me:

def criteriaUsers = UserRole.createCriteria()
def users =  criteriaUsers.listDistinct{
                eq("role", role_admin)
             }.user

Side note: from the nomenclature of your classes it looks like you are using spring-security-core plugin. In my humble opinion the hasMany = [roles: UserRole] is redundant as the association User - Role is already being modeled in the UserRole class.