Grails/Hibernate: how to order by isnull(property) to get NULLs last?

Question

Normally when ordering ascending by a field, you get the NULL values first, and then the more interesting values. Often, you want NULL values last. In MySQL, you can do this with:

SELECT * FROM people ORDER BY ISNULL(name), name;

However, I'm using Grails with Hibernate criteria, and I have absolutely no idea how to do this there. Is this even supported in any way? Is there some way to order by a custom SQL expression? I'd hate to rewrite all my criteria to plain SQL just to get it to sort correctly.

Argive

Looks like the Hibernate 'bug' has been fixed, hibernate.atlassian.net/browse/HHH-465. I would love to see updated answers for this. – Stromboli

Answer 1

In hibernate you can try with this below code :

Criteria c = ...;

c.addOrder(Order.asc("name").nulls(NullPrecedence.LAST));

Naos

same can be done for grails criteria too.. def userCriteria = User.createCriteria() userCriteria.addOrder(Order.asc(FIRST_NAME).nulls(NullPrecedence.LAST)); – Womack

Answer 2

if you want to order and HibernateCriteriaBuilder to set NullPrecedence in GORM the AbstractHibernateCriteriaBuilder provies you with the method order() that you can set a org.hibernate.criterion.Order like regular Hibernate

Example

     People.createCriteria().list (){
          order(org.hibernate.criterion.Order.asc('name')
                . nulls(org.hibernate.NullPrecedence.LAST)
           )
     }

Colby

Answer 3

I'm afraid it's not even in Hibernate yet: there's an open bug for this.

Though, one could use NativeSQLOrder from that bug comments and try to inject a proper function into HibernateCriteriaBuilder. You only need to add a sqlOrder method to HibernateCriteriaBuilder class, doing approximately same as HibernateCriteriaBuilder.order().

Eric