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 
  } 
} 

Unplaced

Which is your implementation finally ? Because I have the same problem. Thanks a lot – Calx

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])

Jungle

Thanks a lot. It works !! Do you know if there is a JIRA issue filled for this bug ? – Unplaced

A detailed explanation on why listDistinct only filters out duplicates in memory can be found here in the Hibernate FAQ: community.jboss.org/wiki/… . – Jungle

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")
}

Recondite

its not only about providing pagination params, its about having a real PagedResultList with totalCount property set ... – Amberly

Answer 3

EDIT: Found a way to get both! Totally going to use it now

http://www.intelligrape.com/blog/tag/pagedresultlist/

If you call createCriteria().list() like this
def result=SampleDomain.createCriteria().list(max:params.max, offset:params.offset){
// multiple/complex restrictions
   maxResults(params.max)
   firstResult(params.offset)
} // Return type is PagedResultList
println result
println result.totalCount

You will have all the information you need in a nice PagedResultList format!

/EDIT

Unfortunately I do not know how to get a combination of full results AND max/offset pagination subset in the same call. (Anyone who can enlighten on that?)

I can, however, speak to one way I've used with success to get pagination working in general in grails.

def numResults = YourDomain.withCriteria() {
    like(searchField, searchValue)
    order(sort, order)
    projections {
      rowCount()
    }
}

def resultList = YourDomain.withCriteria() {
    like(searchField, searchValue)
    order(sort, order)
    maxResults max as int
    firstResult offset as int
}

That's an example of something I'm using to get pagination up and running. As KoK said above, I'm still at a loss for a single atomic statement that gives both results. I realize that my answer is more or less the same as KoK now, sorry, but I think it's worth pointing out that rowCount() in projections is slightly more clear to read, and I don't have comment privileges yet :/

Lastly: This is the holy grail (no pun intended) of grails hibernate criteria usage references; bookmark it ;) http://www.grails.org/doc/1.3.x/ref/Domain%20Classes/createCriteria.html

Acetylate

Answer 4

Both solutions offered here by Ruben and Aaron still don't "fully" work for pagination because the returned object (from executeQuery() and listDistinct) is an ArrayList (with up to max objects in it), and not PagedResultList with the totalCount property populated as I would expect for "fully" support pagination.

Let's say the example is a little more complicated in that : a. assume Role has an additional rolename attribute AND b. we only want to return distinct User objects with Role.rolename containing a string "a" (keeping in mind that a User might have multiple Roles with rolename containing a string "a")

To get this done with 2 queries I would have to do something like this :

// First get the *unique* ids of Users (as list returns duplicates by
// default) matching the Role.rolename containing a string "a" criteria
def idList = User.createCriteria().list {
  roles {
    ilike( "rolename", "%a%" )
  }
  projections {
    distinct ( "id" )
  }
}

if( idList ){
  // Then get the PagedResultList for all of those unique ids
  PagedResultList resultList =
    User.createCriteria().list( offset:"5", max:"5" ){
      or {
         idList.each {
           idEq( it )
         }
      }     
      order ("username", "asc")
    }
}

This seems grossly inefficient.

Question : is there a way to accomplish both of the above with one GORM/HQL statement ?

Regen

you can use 'in' "id" idList instead of loop and idEq(it) – Calx

Answer 5

You can use

User.createCriteria().listDistinct {
    maxResults 10
}

Jungle

Almost working :)ListDistinct will return 9 users instead of 10 (removing the duplicated user). But how can I use pagination like that with offset params for instance. – Unplaced

This might be helpful: grails.org/doc/latest/api/grails/orm/… – Mattheus

The problem with the distinct projection is that you only keep the 'columns' or properties on which the distinct is specified. – Jungle

Answer 6

Thanks for sharing your issue and Kok for answering it. I didn't have a chance to rewrite it to HQL. Here is my solution (workaround): http://ondrej-kvasnovsky.blogspot.com/2012/01/grails-listdistinct-and-pagination.html

Please tell me if that is useful (at least for someone).

Detriment