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
}
}
EDIT: Found a way to get both! Totally going to use it now
http://www.intelligrape.com/blog/tag/pagedresultlist/
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.
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
You can use
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).
this way you can still use criteria and pass in list/pagination paramaters
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 :
This seems grossly inefficient.
Question : is there a way to accomplish both of the above with one GORM/HQL statement ?
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.