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

Question

I use Nimble and Shiro for my security systems and I just encountered a GORM error. Really:

User.createCriteria().list { maxResults 10 } 

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

After further research, I found that createCriteria returns twice the same user (admin) , because the administrator has two roles !!! (I am not kidding).

It seems that any user with more than one role will be returned twice to the createCriteria call, and User.list will return max-1 instances (for example, 9 users instead of 10 users).

What workaround can I use to return 10 unique users?

This is very annoying because I cannot use pagination correctly.

---

My domain classes:

 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 understandable, but using an HQL query seems like a way to solve this problem. As described in the Grails documentation (executeQuery section), paginate parameters can be added as additional parameters for 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 parameters

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

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 necessary information in a good PagedResultList format!

/ EDIT

Unfortunately, I don't know how to get a combination of the full results and a subset of the max / offset subset in the same call. (Anyone who can enlighten this?)

I can, however, talk to one of the ways in which I used success to get pagination, in general, in the grail.

 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 } 

This is an example of what I use to expand pages. As KoK said above, I'm still at a loss for one atomic statement that gives both results. I understand that my answer is about the same as KoK, now, I'm sorry, but I think it's worth noting that rowCount () in the forecasts reads a little more clearly, and I have no comments yet: /

Finally: this is the holy grail (not intended for puns) of letters referring to the use of hibernation; tag it;) http://www.grails.org/doc/1.3.x/ref/Domain%20Classes/createCriteria.html

Answer 4

Both solutions proposed here by Ruben and Aaron still do not "fully" work for pagination because the returned object (from executeQuery () and listDistinct) is an ArrayList (with maximum objects in it), and not a PagedResultList with the totalCount property since I would expect "full" pagination support.

Let's say the example is a bit more complicated in this: a. Assume Role has an additional attribute, rolename AND b. we want to return only individual User objects with the name Role.rolename containing the string "a" (bearing in mind that the User can have several roles with the role name containing the string "a")

To do this with two queries, I needed 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 extremely inefficient.

Question: is there a way to perform both of the above actions with a single GORM / HQL statement?

Answer 5

you can use

 User.createCriteria().listDistinct { maxResults 10 } 

Answer 6

Thank you for sharing your problem and Kok for the answer. I did not have the opportunity to rewrite it in HQL. Here is my solution (workaround): http://ondrej-kvasnovsky.blogspot.com/2012/01/grails-listdistinct-and-pagination.html

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