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

One thought on “Case-insensitive criteria ordering on child properties

  1. Ben says:

    Thanks, this was useful!

    1. Igor Shults says:

      Glad to help!

  2. Miguel Ángel García says:

    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!

    1. Igor Shults says:

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

      1. Miguel Ángel García says:

        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

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*