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)")