Creating a criteria restriction that operates on a property of a subproperty, from a list of sub-properties on a domain object in Grails

Question

I have a Groovy class called Person, with has a one-to-many association called identities, where each element is an instance of the Identity class. The Identity class has a property called channel, which has an attribute called channelName. Here's a stripped-down example:

class Person {
    static hasMany = [identities: Identity]

    String username
    String firstName
    String lastName
    ...
}

class Identity {
    Channel channel

    ...
}

class Channel {
    String channelName

    ...
}

There is a method that dynamically builds up criteria based on the arguments that are passed in, to search for people. A stripped down example:

Person[] findPeople(String username, String channelName, int limit) {
    return buildCriteria(username, channelName).list(max: limit) {
        order('lastName')
        order('firstName')
    }
}

The buildCriteria method looks like this:

private DetachedCriteria<Person> buildCriteria(String username, String channelName) {
    def criteria = Person.where {  }

    if(username) {            
        criteria = criteria.where {
            eq('username', username)
        }
    }

    if(channelName) {
        criteria = criteria.where {
            identities {
                eq('channel.channelName', channelName)
            }
        }
    }

    return criteria
}

If channelName is passed in, I want to get a Person record back if any of the identities on that person has a channel whose channelName property matches the one that is passed in. I wrote a unit test for this and everything seems to work as expected, and I get the expected results. The test is able to successfully resolve 'channel.channelName'. But when I actually end up using the method at runtime (it is called via a controller endpoint), I see the following error:

could not resolve property: channel.channelName of: com.example.domain.Identity

The only difference I can see is that in the unit-test environment, Channel, Person, and Identity are mocked. But I don't see why that should cause such a difference in behavior. I have two questions:

• How is Groovy able to resolve the property in the test environment, but not during actual runtime?
• Is this the right way to query against a property of a sub-property, from a list of sub-properties (on a parent object)? If not, what is the right way?

Answer 1

AFAIK the right way is channel { eq('channelName', channelName) } but maybe in dettached criterias (I don't usually use them) or newer versions of Grails the dot syntax works as well.

In any case, I was trying to reproduce you problem in a similar domain class hierarchy I have, and found very odd results using the where method. Checking the MySQL query log I could see that the most inner query (in your case it would be eq('channel.channelName', channelName)) was completely ignored, even when changed to channel { eq('channelName', channelName) }.

Changing where { ... }.list([max:10]) to createCriteria().list([max:10]) solved it. It doesn't use detached criterias, though.

In any case, the following works for me and avoids the need of dettached criterias.

List<People> findPeople(String username, String channelName, int limit) {
    People.createCriteria().list(max: limit) {
        with(buildCriteria(username, channelName))
        order('lastName')
        order('firstName')
    }
}

private Closure buildCriteria(String username, String channelName) {
    return {
        if(username) {            
            eq('username', username)
        }
        if(channelName) {
            identities {
                channel {
                    eq('channelName', channelName)
                }
            }
        }
    }
}

What I like about this way of creating criterias is that is agnostic to the way they are later executed. You get a closure you hook into the criteria, regardless of that being a Hibernate Criteria or the Grails dettached criteria. It also allows you to compose criterias very easily, since you can apply as many closures as you want.

I know it doesn't really answer your question, but maybe you can give it a go and compare the results with your current ones. You might get some clues out of it.