Sorting on nullable fields in GORM

Question

I'm trying to figure out how to sort on multiple fields in Grails 3, one of which may or may not be null. I have this Book domain:

class Book {

    String title
    String sortTitle

    static constraints = {
        title blank: false
        sortTitle nullable: true
    }
}

Books with titles like "The Peripheral" have a sortTitle of "Peripheral, The", otherwise sortTitle will be null. I want books sorted by sortTitle if one exists, otherwise by title.

I found other similar SO questions, but none with a nullable field. Does anyone happen to have some pointers in the right direction?

Answer 1

I couldn't quite figure out how to do it purely with GORM, but some raw HQL worked:

def books = Book.findAll("from Book as b order by coalesce(b.sortTitle, b.title)")

Answer 2

I'm not 100% sure on this but it seems to me that sorting by coalesce would lead to the database doing a file sort which could be rather expensive rather than being able to make use of an index.

My suggestion would be to always populate sortTitle with the name you want to sort by and then just use that.

Answer 3

You can use:

coalesce(book.sortTitle, book.title)

Here you have the official Hibernate documentation.