Case-insensitive criteria ordering on child properties
I recently ran into a couple of Grails 2.2.x bugs/changes that made a simple requirement a bit of a pain. Given a Grails Criteria, sort the returned entries by a child property, case-insensitive-ly. Sounds easy enough, right?
Let’s say we have a pair of classes, Parent and Child:
Then if we try to order by the names of the child classes through a criteria query, we will find that it does not sort case-insensitively by default (as expected). Also important to note that within the “children” closure, we can reference the child properties without a problem:
Now we want to sort the names regardless of capitalization. Apparently in older versions of Grails it was possible to do:
but according to GRAILS-8182 that is no longer possible in Grails 2.x, and will not be making a comeback. The order method now expects a org.hibernate.criterion.Order object, instead:
Unfortunately, this approach does not seem to play well with properties nested within a child/parent closure, as we have above. Explicitly calling child.name in the method does not appear to work either. Instead, we will get the exception:
org.hibernate.QueryException: could not resolve property: name of: Parent
This is one of the points made in the comments of GRAILS-3911.
One potential workaround for this is to leverage Hibernate’s createAlias call, and replace our child closure with dot-notated properties:
Better yet, if you are not querying the parent object inside of the criteria (like in our case), you can instead invoke createCriteria on the Child class:
Obviously if you DO have references to the parent object (and order on its properties), you will still need to apply the first fix to it.
These workarounds may take a little refactoring, but hopefully they shouldn’t be too bad compared to other alternatives. In the meantime, it does look like GRAILS-9171 has been created with the purpose of simplifying the approach (and hopefully resolving this issue in the process).
Igor Shults
Thanks, this was useful!
Glad to help!
Hi Igor Shults!
Thank yoy very mucho for your explanation and this workaround. Have you tried this on Grails 3.X? I’ve tried and the error:
org.hibernate.QueryException: could not resolve property: name of: Parent
is shown 🙁
I dont’t if it could works on Grails 3.X or another approach could be done.
Thank you!
I haven’t tried in Grails 3, but looking at the docs ( http://docs.grails.org/3.1.1/ref/Domain%20Classes/createCriteria.html ) it seems like if you alias the property and pass in a map as the third argument, you should be able to tell Hibernate to ignore case that way? e.g. eq(“branch”, “london”, [ignoreCase: true])
Yes, it works with the criterion “eq”, but unfortunately the criterion “order” does not have this possiblity yet 🙁 as is described in this issue: https://github.com/grails/grails-core/issues/2836
So the solution you provided I think that works in Grails 2.X, but not in Grails 3.X. I have created an issue (with an example app) if you want to have a look: https://github.com/grails/grails-core/issues/10590
Thanks!
P.S.: Probably I’m making a mistake that I’m not seeing