oracle-databasehibernategrailsgrails-orm

how to apply NULLS LAST sorting in Grails/GORM


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?


Solution

  • 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.