Grails GORM按CASE语句排序 [英] Grails GORM sort by CASE statement

问题描述

我试图使用 CASE 语句通过GORM查询进行排序。我所拥有的是一列 state 的视图，其值可以是状态缩写或单词 General 。我会运行这样的查询

pre $ Drop $ get $ ，order：asc]

但它返回一个错误

 无法解析属性：stateOrderBy：workspace.Dropdown 
  

当我创建类似以下的域类时， stateOrderBy 的值是字符串 CASE WHEN state ='General'THEN 2 ELSE 1 END 而不是1或2这就是为什么我得到上述错误。有没有一种方法来评估我的CASE语句？

  @EqualsAndHashCode（includeFields = true）
 class Dropdown实现Serializable {
字符串状态
字符串dropDownNames 
字符串sectionName 
字符串前缀
整数displayOrder 
 def stateOrderBy =CASE WHEN state ='General'THEN 2 ELSE 1 END
 
 static transients = [stateOrderBy] 
 
静态映射= {
数据源'plDropdown'
表'view_AllTables'
 id复合：['state'，'sectionName'，'displayOrder'] 
状态列：'状态'
 dropDownNames列： 'DropDownNames'
 sectionName列：'SectionName'
前缀列：'前缀'
 displayOrder列：'DisplayOrder'
 stateOrderBy列：'stateOrderBy'
版本false 
 
 
  

编辑：总会有一个结果 General 列，但状态特定查询可能没有结果。如果有返回状态的具体答案，我想使用它，否则一般的答案。

解决方案

GORM / Hibernate排序是由域/实体属性。 GORM不会将一个属性评估为SQL语句。这是一件好事，因为它是危险的。

可以使用Criteria查询创建SqlProjection：

  Dropdown.withCriteria {'''''''''''''$'$'$' （'state'）
属性（'dropDownNames'）
属性（'sectionName'）
属性（'前缀'）
属性（'displayOrder '）
 
 sqlProjectionCASE WHEN state ='General'THEN 2 ELSE 1 END as stateSortBy，'stateSortBy'，org.hibernate.type.IntegerType as org.hibernate.type.Type 
} 
 
 order（'stateSortBy'，'asc'）
} 
  

这将创建一个即将被排序的属性。虽然我认为在视图中做同样的事情会更好。然后你可以通过该属性进行排序，即使使用动态搜索。

在上面的sqlProjection示例中， stateSortBy 动态创建的属性将包含值2或1.这可能不是您想要的，因此您需要用那些序号引用的表列名称替换它们。然后，您需要更改休眠类型适当的一个。这意味着两列必须是相同的类型或铸造。用SQL术语来说，你想要的是一个新的计算字段，其中包含要排序的值。

请注意，因为正在使用投影，结果将是一个包含投影属性（表格列）。它不会是一个List。

关于Domain.executeQuery（）和Domain.findAll（sql），这些是用于HQL而不是SQL。

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.

解决方案

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.

这篇关于Grails GORM按CASE语句排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！