Grails“max”子查询与关联，只获取hasMany的最新内容 [英] Grails "max" subquery with an association, to get only the latest of a hasMany

问题描述

简化的域模型：
'Txn'（如交易中）有许多'TxnStatus'。 TxnStatus有一个dateTime

这是一个传统映射，所以我不能更改数据库，即Txn上的映射：

<$
txnStatus列：'MessageID'，ignoreNotFound：true，fetch：'join'
}

我需要根据一些动态构建的标准获取Txns，目前使用GORM的'where'查询，它运行良好;但我还需要获得最新 txnStatus。

试过：

  def query = Txn.where {
 txnStatus {dateTime == max（dateTime）} 
} 
  

给出： java.lang.ClassCastException：org.hibernate.criterion.DetachedCriteria不能转换为java.util.Date

也尝试过：

  def query = Txn.where { 
 txnStatus.dateTime == max（txnStatus.dateTime）
} 
  

其中给出：

 编译错误：... 
不能在表达式txnStatus.dateTime$ b上使用聚合函数max $ b  

在这个阶段，我正在考虑改用HQL ......任何帮助表示赞赏！

解决方案

几天前有个问题与此非常相似。看起来，使用'max'子查询的查询不能很好地处理==

OP能够使它与<并以这种方式解决它。看看有关查询没有帮助我的文档，我想到了这一点。

这是一个非常疯狂的猜测 -

  Txn.where {
 txnStatus {
 dateTime == property（dateTime）.of {max（dateTime）} 
} 
 } 
  

The simplified domain model: 'Txn' (as in Transaction) hasMany 'TxnStatus'. TxnStatus has a dateTime

This is a legacy mapping so I cant change the DB, the mapping on Txn:

  static mapping = { 
    txnStatus column: 'MessageID', ignoreNotFound: true, fetch: 'join'
  }

I need to get Txns based on a number of dynamically built criteria, currently using GORM's 'where' query, it works well; BUT I need to also get only the latest txnStatus.

Tried:

def query = Txn.where {
   txnStatus { dateTime == max(dateTime) }    
}

gives: java.lang.ClassCastException: org.hibernate.criterion.DetachedCriteria cannot be cast to java.util.Date

also tried:

 def query = Txn.where {
       txnStatus.dateTime == max(txnStatus.dateTime)    
    }

which gives:

Compilation Error: ... 
            Cannot use aggregate function max on expressions "txnStatus.dateTime"

At this stage I am thinking of changing to HQL...any help appreciated!

解决方案

There was a question a couple of days ago very similar to this. It appears that using where queries with a 'max' subquery doesn't work well with ==

The OP was able to get it to work with < and worked around it that way. Looking at the docs on where queries has not helped me figure this one out.

Here is a really wild guess -

    Txn.where {
        txnStatus  {
          dateTime == property(dateTime).of { max(dateTime) }
        }
    }

这篇关于Grails“max”子查询与关联，只获取hasMany的最新内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！