Gorm - Sorting by calculated or derived field

Recently, while trying to work with a legacy database I needed to create a default sort on a String field that sometimes held a number. One cannot simply sort on the String field because it will produce incorrect results, such as [1, 10, 11,..., 2, 20, ...]. A simple method for correcting this is to pad the field with 0's, so that it produces the results [00001, 00002, 00010, ...]). In order to accomplish this by default in the domain class, in the domain's mapping closure simply add a formula to a sort field.

To create a default sort on a calculated or derived field

	class Book {
	String name
	String bookOrder //<-- String data type for the purposes of the example
	String sortField //<-- Calculated/Derived field
	static mapping = {
	// Note the use of the database field name 'book_order', not the object's property name bookOrder
	sortField formula: 'lpad(book_order, 5, \'0\')' // <-- SQL formula used to generate the sortField value in GORM
	sort sortField: 'asc'
	}
	String toString() {
	bookOrder
	}
	}

view raw Book.groovy hosted with ❤ by GitHub

After loading a series of 20 books into GORM (GORM Book loading code below), retrieving them using Book.list() should produce [1, 2, ..., 10, 11, ..., 20]

	(20..1).each {
	new Book(name: "Book $it", bookOrder: it.toString()).save()
	}

view raw bookLoader.groovy hosted with ❤ by GitHub

References

• eyallupu Hibernate blogpost - shows how to use a formula to create a sort on a calculated field.
• GORM documentation on derived properties