Case-insensitive criteria ordering on child properties

A workaround for a couple of Grails 2.x bugs and changes that have made it difficult to sort criteria case-insensitively when using child/nested properties..

Igor Shults

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:

class Parent {
   Long id

   static hasMany =[ children: 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:

class CriteriaIntegrationSpec extends IntegrationSpec {
    void "Nested criteria property ordering should ignoreCase properly"() {
        given: 'A parent class and its child classes'
            Parent parent = new Parent().save()
            Child child1 = new Child(age: 100, name: 'Apollo', parent: parent).save()
            Child child2 = new Child(age: 200, name: 'Zeus', parent: parent).save()
            Child child3 = new Child(age: 300, name: 'athena', parent: parent).save()

        when: 'a criteria call is ordered by a String property'
            List names = Parent.createCriteria().list() {
                children {
                    eq('parent', parent)
                    gt('age', 50)

                    projections {
                        property('name')
                    }

                    order('name')
                }
            }

        then: 'it does not sort case-insensitively by default'
            assert names == [ 'Apollo', 'Zeus', 'athena' ]
    }
}

Now we want to sort the names regardless of capitalization. Apparently in older versions of Grails it was possible to do:

order('name', 'asc').ignoreCase()

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:

order(Order.asc('name').ignoreCase())

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:

class CriteriaIntegrationSpec extends IntegrationSpec {
    void "Nested criteria property ordering should ignoreCase properly"() {
        given: 'A parent class and its child classes'
            Parent parent = new Parent().save()
            Child child1 = new Child(age: 10, name: 'Apollo', parent: parent).save()//1
            Child child3 = new Child(age: 20, name: 'Zeus', parent: parent).save()//3
            Child child2 = new Child(age: 30, name: 'athena', parent: parent).save()//2

        when: 'a criteria call is ordered by a String property'
            List names = Parent.createCriteria().list() {
                createAlias('children', 'child')
                eq('child.parent', parent)
                gt('child.age', 5)

                projections {
                    property('child.name')
                }

                order(Order.asc('child.name').ignoreCase())
            }

        then: 'it sorts case-insensitively'
            assert names == [ 'Apollo', 'athena', 'Zeus' ]
    }
}

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:

List names = Child.createCriteria().list() { // Instead of Parent.createCriteria()
    eq('parent', parent)
    gt('age', 5)

    projections {
        property('name')
    }

    order(Order.asc('name').ignoreCase())
}

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

Share this Post

Related Blog Posts

JVM

Vert.x Fat Jar Deployments

November 25th, 2014

Fat jar deployments in Vert.x can simplify the delivery of your software. One drawback is limited options for module dependencies, which this post will address.

Object Partners
JVM

UDP Server with Spring Boot and Reactor

November 18th, 2014

A tutorial and accompanying project for creating a simple UDP server with Spring Boot and Reactor.

Object Partners
JVM

StringTemplates in Groovy

November 11th, 2014

Using the StringTemplateEngine in Groovy -- troubleshooting, gotchas, and everything else.

Mike Hostetler

About the author

Igor Shults

Sr. Consultant

Igor is a self-driven developer with a desire to apply and expand his current skill set.  He has experience working with companies of all sizes on the whole application stack, from the database to the front-end.  He enjoys working in collaborative environments involving discussion and planning around data modeling and product development.