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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 | |
| } | |
| } |
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]
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| (20..1).each { | |
| new Book(name: "Book $it", bookOrder: it.toString()).save() | |
| } |
References
- eyallupu Hibernate blogpost - shows how to use a formula to create a sort on a calculated field.
- GORM documentation on derived properties
