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

About the Author

Object Partners profile.

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 *

Related Blog Posts
Why we started using JSON Schema in our internal Helm charts
Helm 3 supports validating the provided chart values against a JSON Schema. While it may be quicker to get started in your chart development without a schema, we found it valuable for a number of […]
Rewriting files in Google Cloud Storage
Rewriting Files in GCP Note: even though this code is in Python, this should be the same idea in JavaScript, Go, etc. I wrote the following to copy a file from one Google Cloud Storage […]
Interpreting Spatial Data in the Age of COVID-19
As 2020 has come to an end, many are eager to leave the mess of COVID-19 behind with the new year and gain a fresh start. Unfortunately, new cases are still soaring across the United […]
Building a Better Mousetrap
Recently, my daughter (age 6) was into building “mousetraps” out of shoe boxes. These were more or less comfortable cardboard mouse houses complete with beds, rooms, everything a mouse could want or need and not […]