Outer joins and domain classes

When working with GORM domain classes and using DetachedCriteria (for example the ‘where’ method on domain classes), you will notice at some point that all joins will be inner joins!

If the database setup requires an outer (left) join, then the default GORM api has no way to specify this, so you will need a workaround.

This workaround works by looking into the GORM system at the moment just before the detached criteria become actual hibernate criteria. At that moment it is possible to use the Hibernate api itself that does allow specifying the relation join type.

The reason of the lack of support for outer joins seems to be that the detached criteria system is an abstraction not only over hibernate, but also over other NoSQL solutions that do not support this at all.

Here’s the actual workaround (groovy code obviously):

List listWithOuterJoin(DetachedCriteria criteriaBuilder, Map resultParameters, Map outerJoinAssociations) {
    return criteriaBuilder.withPopulatedQuery(resultParameters, null) { Query query ->
        Criteria criteria = query.@criteria
        outerJoinAssociations.each { key, value ->
            criteria.createAlias(key, value, org.hibernate.sql.JoinType.LEFT_OUTER_JOIN)
        }
        query.list()
    }
}

Usage:

1. Create a detached criteria instance using any of the GORM api methods
2. Use aliasses for the relations that you need an outer join for (example: for Person.children, their name can be referred to with ‘c’ as an alias meaning a reference would become c.name in your criteria query)
3. Feed the detached criteria to the helper method show earlier, together with a map of outer join aliasses (in the form of [“children”:”c”]).
4. The result (in this setup) is an ordinary list of results from the criteria

More info on detached criteria can be found here.

Original source is here.