Saturday, March 26, 2011

Sorting with an outer join using GORM

In a situation where a GORM query is to return a sorted result set, but the sort field belongs to the associated object, which may or may not be null, a regular Criteria query will not cut it, because of the following limitation:

Host.createCriteria().list {
    createAlias("site", "s")
    order("s.name", "asc")
}

Without "createAlias", the query would be an outer join on "site", but because we are trying to sort by site.name, "createAlias" makes the query an inner join, so all results where the site is null are lost. Using regular HQL would help us in this situation:

Host.findAll("from Host as h \
               left outer join h.site as s order by s.name asc")