I'm using a Grails DetachedCriteria query, and need to get null values ordered last when sorting descending. This is against an Oracle database.
Based on the research I've done so far, there is no direct support for this in Hibernate, and hence not in Grails:
Grails/Hibernate: how to order by isnull(property) to get NULLs last?
and
https://hibernate.atlassian.net/browse/HHH-2381
Based on these, it seems like my best option is to extend the Order class from Hibernate and add NULLS LAST support myself. Following that path, can anyone give me an example of how that would be exposed through Grails? I have very little experience with straight Hibernate, so the examples given are rather difficult to follow.
Alternatively: is there any way in Oracle to specify NULLS LAST sorting within the table definition, via some property on a column or the like?
Following is an example in grails criteria to sort Nulls at last by overriding hibernate addOrder method
def userCriteria = User.createCriteria()
List results = userCriteria.list(max:limit, offset:offset) {
eq("isActive", true)
ilike("firstName",text+"%")
userCriteria.addOrder(Order.asc("firstName").nulls(NullPrecedence.LAST));
}
You can even have this in an or
block. eg.
or{
userCriteria.addOrder(Order.asc(USERNAME).nulls(NullPrecedence.LAST));
userCriteria.addOrder(Order.asc(EMAIL).nulls(NullPrecedence.LAST));
userCriteria.addOrder(Order.asc(FIRST_NAME).nulls(NullPrecedence.LAST));
userCriteria.addOrder(Order.asc(LAST_NAME).nulls(NullPrecedence.LAST));
}
Hope this helps someone searching like me.