Grails createCriteria: finding objects via a field that is a null domain class instance

12
votes

I'm seeing some unexpected behavior in Grails' createCriteria. I have a domain class that looks like this:

MyDomainClass {
    AnotherDomainClass anotherDomainClass
    static constraints = {
        anotherDomainClass(nullable:true)
    }
}

I want to find all instances of MyDomainClass where anotherDomainClass is null. So I do this:

return MyDomainClass.createCriteria().list {
    eq('anotherDomainClass', null)
}

However, I get nothing back.

What am I doing wrong? I can see there are database entries where the ANOTHERDOMAINCLASS_ID column is indeed null (or blank, I can't tell).

I'd be fine creating a query that references the ANOTHERDOMAINCLASS_ID column directly, but I haven't found a way yet.

Thanks!

3
grailsgrails-orm

3 Answers

16
votes

Instead of using eq you can use the isNull

def results = MyDomainClass.withCriteria {
    isNull('anotherDomainClass')
}

Here's a good reference HibernateCriteriaBuilder Javadoc too.

2
votes

What happens if you try isNull instead of eq?

EDIT: Could actually be isEmpty instead of isNull.

1
votes

It's not a real answer, but my workaround for the time being is to retrieve all the objects from the database and filter in the app tier, like this:

MyDomainClass.list({it.anotherDomainClass == null})