如何在最大日期和组中同时获取grails中的记录 [英] How to fetch records in grails by max date and group at same time

问题描述

  id名称阴影date_created 
 ----  - --- ------- --------------- 
 1红色光亮10-28-2012 
 2橙色光10-28-2012 
 3红色< null> 10-24-2013 
 4橙灯10-24-2013 
  

预期结果：

  id名称值date_created 
 ---- ----- ------ ---- ----- 
 3红色< null> 10-24-2013 
 4橙灯10-24-2013 
  

我能做什么与GORM得到这个结果？

在纯sql中，这个查询得到了我想要的结果：

  SELECT t.name，t.shade，r.MaxTime 
 FROM（SELECT name，MAX（date_created）as MaxTime 
 FROM colorable 
 GROUP BY名称）r 
 INNER JOIN colortable t ON t.name = r.name AND t.date_created = r.MaxTime 
  

我试过的东西：

  def c = Color.createCriteria（）
 def results = c {
 projection {
 groupProperty（name）
 max（dateCreated）
} 
} 
  

但我无法弄清楚如何从投影中获取更多列？即阴影列

解决方案

您使用的是Grails 2.0或以上版本：

  def colors = Color.withCriteria {
 eqdateCreated，new grails.gorm.DetachedCriteria（Color）.build {
预测{
 mindateCreated
} 
} 
 
预测{
 property 姓名
财产阴影
财产dateCreated
} 
} 
  

显式使用 DetachedCriteria 类有点难看，但也不算太坏。这个查询也应该作为Where查询来做，但似乎有一个错误，这意味着你不能在集合函数中使用'=='。一旦错误得到解决，你应该可以这样做：

  def colors = Color.where {
 dateCreated = = max（dateCreated）
} .property（name）。property（shade）。property（dateCreated）。list（）
  

请注意，用'&'替换'=='可以正常工作。

I have a table that looks like this:

id    name      shade        date_created
----  -----    -------      ---------------
1     Red       bright        10-28-2012
2     Orange    light         10-28-2012
3     Red       <null>        10-24-2013
4     Orange    light         10-24-2013

Desired Result:

id    name   value    date_created
----  -----  ------   ---------
3     Red    <null>   10-24-2013
4     Orange light    10-24-2013

What can I do with GORM to get this result?

In pure sql this is the query that gets me the desired result:

SELECT t.name, t.shade, r.MaxTime
FROM (SELECT name, MAX(date_created) as MaxTime
      FROM colorable
      GROUP BY name) r
INNER JOIN colortable t ON t.name = r.name AND t.date_created = r.MaxTime

What I've Tried:

    def c = Color.createCriteria()
    def results = c {
        projections {
            groupProperty("name")
            max("dateCreated")
        }
    }

But I can't figure out how to fetch more columns from the projection? i.e. the shade column

解决方案

You can do this with detached criteria if you're using Grails 2.0 or above:

def colors = Color.withCriteria {
    eq "dateCreated", new grails.gorm.DetachedCriteria(Color).build {
        projections {
            min "dateCreated"
        }
    }

    projections {
        property "name"
        property "shade"
        property "dateCreated"
    }
}

The explicit use of the DetachedCriteria class is a bit ugly, but it's not too bad. This query should also be doable as a Where query, but there appears to be a bug which means you can't use '==' with aggregate functions. Once the bug is fixed, you should be able to do:

def colors = Color.where {
    dateCreated == max(dateCreated)
}.property("name").property("shade").property("dateCreated").list()

Note that replacing '==' with '<' works fine.

这篇关于如何在最大日期和组中同时获取grails中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！