GORM createCriteria and list do not return the same results : what can I do?

Question

I am using Nimble and Shiro for my security frameworks and I've just come accross a GORM bug. Indeed :

User.createCriteria().list { 
   maxResults 10 
} 

returns 10 users whereas User.list(max: 10) returns 9 users !

After further investigations, I found out that createCriteria returns twice the same user (admin) because admin has 2 roles!!! (I am not joking).

It appears that any user with more than 1 role will be returned twice in the createCriteria call and User.list will return max-1 instances (i.e 9 users instead of 10 users)

What workaround can I use in order to have 10 unique users returned ?

This is a very annoying because I have no way to use pagination correctly.

---

My domain classes are:

class UserBase { 
   String username 
   static belongsTo = [Role, Group] 
   static hasMany = [roles: Role, groups: Group] 
   static fetchMode = [roles: 'eager', groups: 'eager'] 
   static mapping = { 
     roles cache: true, 
     cascade: 'none', 
     cache usage: 'read-write', include: 'all' 
   } 
}

class User extends UserBase { 
  static mapping = {cache: 'read-write'} 
} 

class Role { 
  static hasMany = [users: UserBase, groups: Group] 
  static belongsTo = [Group] 
  static mapping = { cache usage: 'read-write', include: 'all' 
    users cache: true 
    groups cache: true 
  } 
} 

Answer 1

Less concise and clear, but using an HQL query seems a way to solve this problem. As described in the Grails documentation (executeQuery section) the paginate parameters can be added as extra parameters to executeQuery.

User.executeQuery("select distinct user from User user", [max: 2, offset: 2])

Answer 2

this way you can still use criteria and pass in list/pagination paramaters

User.createCriteria().listDistinct {
    maxResults(params.max as int)
    firstResult(params.offset as int)
    order(params.order, "asc")
}