Grails GORM sort by CASE statement

Question

I'm trying to use a CASE statement to order by GORM query. What I have is a view with a column of state the value there can be a state abbreviation or the word General. I will run the query like this

Dropdown.findByStateInList(['General','CA'], [sort: "stateOrderBy", order: "asc"]

but it returns an error of

could not resolve property: stateOrderBy of: workspace.Dropdown

When I create my domain class like below the value of stateOrderBy is the string CASE WHEN state = 'General' THEN 2 ELSE 1 END and not a 1 or 2 which is why I'm getting the error above. Is there a way to evaluate my CASE statement?

@EqualsAndHashCode(includeFields=true)
class Dropdown implements Serializable {
  String state
  String dropDownNames
  String sectionName
  String prefix
  Integer displayOrder
  def stateOrderBy = "CASE WHEN state = 'General' THEN 2 ELSE 1 END"

  static transients = [ "stateOrderBy" ]

  static mapping = {
    datasource 'plDropdown'
    table 'view_AllTables'
    id composite: ['state','sectionName','displayOrder']
    state column:'state'
    dropDownNames column:'DropDownNames'
    sectionName column:'SectionName'
    prefix column:'Prefix'
    displayOrder column:'DisplayOrder'
    stateOrderBy column:'stateOrderBy'
    version false
  }
}

EDIT: There will always be a result in the General column, but there may not be a result for the state specific query. If there is a state specific answer returned I want to use that otherwise the General answer.

Answer 1

GORM/Hibernate sorting is done by domain/entity properties. GORM will not evaluate a property as a SQL statement. That's a good thing because it's dangerous.

What might work is creating a SqlProjection with a Criteria query:

Dropdown.withCriteria {
    'in'('state', ['General','CA'])

    projections {
        property('state')
        property('dropDownNames')
        property('sectionName')        
        property('prefix')
        property('displayOrder')

        sqlProjection "CASE WHEN state = 'General' THEN 2 ELSE 1 END as stateSortBy", 'stateSortBy', org.hibernate.type.IntegerType as org.hibernate.type.Type
    }

    order('stateSortBy', 'asc')
}

That will create a property on the fly that would be sorted by. Although I think it would be better to do the equivalent in the view. Then you'd be able to sort by that property, even with a Dynamic Finder.

In the sqlProjection example above, the stateSortBy property that's created on the fly would contain the value 2 or 1. That's probably not want you want, so you'll need to replace those with the table column names those ordinals refer to. Then, you'll need to change the hibernate type to one that's appropriate. Which means the two columns must either be of the same type or be casted as such. In SQL terms, what you want is a new calculated field with the value to sort by.

Note that because projections are being used, the result will be a List containing the projected properties (table columns). It will not be a List.

About Domain.executeQuery() and Domain.findAll(sql), those are for HQL rather than SQL.