CODEWITHSUNDEEP
oracle-databasehibernategrailsgrails-orm
D. Walker
D. Walker

Reputation: 117

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?

Upvotes: 4

Views: 1692

Answers (2)

Aaron Scherbing
Aaron Scherbing

Reputation: 747

Adding onto what Swapnil was saying. If you're using the .list() call on a Criteria object, you can format it like this, using the nulls key:

User.where {
  // filters here
}.list([
  max: 10, 
  offset: 0, 
  sort: 'email', 
  order: 'asc', 
  nulls: NullPrecedence.FIRST,
])

Upvotes: 0

Swapnil Sawant
Swapnil Sawant

Reputation: 620

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.

Upvotes: 4

Related Questions