Grails/GORM/HQL：消除 FROM 部分中的内部查询

Question

我有一个与此类似的表“Table”：

id     user_id      owner_id      due_date
------+------------+-------------+--------------------------
1     | 1          | 1           | 2011-07-26 12:28:50
2     | 1          | 1           | 2011-07-26 15:32:11
3     | 1          | 1           | 2011-07-27 08:11:58
4     | 2          | 1           | 2011-07-26 15:19:44
5     | 2          | 1           | 2011-07-23 12:00:50

如您所见，由 FK user_id 标识的用户可以拥有多个具有不同 due_date 的条目。

我想获取按 user_id 分组的所有最新 Table 实体。

在普通的 MySQL 中，它可能看起来像这样：

SELECT * FROM (
    SELECT * FROM TABLE
      WHERE owner_id = xxx
      ORDER BY due_date DESC
  ) AS sorted
GROUP BY user_id

我首先使用 ORDER BY 进行选择，然后应用 GROUP BY（不幸的是，所有的 SELECT 都不起作用。请参见此处 http://www.cafewebmaster.com/mysql-order-sort-group）。

现在我想将其实现为 HQL（使用 Grails）。问题是 HQL 不支持 FROM 部分中的内部 SELECT，因此以下方法不起作用：

def findMostRecentPerUser( Owner owner ) {
    def result = Table.executeQuery("""
                from Table as t1
                where t1.id in (
                    select sorted.id from (select * from Table as t2 where t2.owner_id = ${owner.id} order by t2.due_date desc) as sorted group by sorted.user_id
            )
        """)
    return result
}

另一种方法是 Criteria，但我不知道如何以这种方式实现它。

有人能指出我正确的方向吗？非常感谢您的帮助。

Answer 1

这种方法应该有效。改进表示赞赏。

def findMostRecentPerUser( Owner owner ) {
    // Inner query sorts maximum due_date per user
    // Outer query fetches all Table entries with this due_date
    def result = Table.executeQuery("""
            from Table as t1
            where t1.due_date in (
                select max(t2.due_date) from Table as t2 where t2.owner = :owner group by t2.user
            )
        """,
        [ owner: owner ] )
    return result
}

Answer 2

为什么不直接将 MySql 排除在外，只获取结果并使用 比较器进行排序。这样，无论数据库如何实现，您都可以保证排序是相同的。

List<Table> results = query();
Collections.sort(results, YourCustomComparator.getInstance());

这样做还会减少数据库的负载，因为它变成了一个没有 order by 的简单查询。

请参阅此处的集合文档。

Answer 3

我不确定您的意图是否可行（我什至不确定您应该能够使用原始 SQL 来完成它）。

您想要使用 Group By 语句，该语句（根据我的理解）仅在与聚合函数结合使用时才有意义，但您没有使用聚合函数。我假设您的目标是获得如下所示的数据结构： [user_id_1: [t1, t5, t2], user_id_2: [t3, t4, t8]...] （其中 tX 是Table 中的实例）是否正确？

如果是这种情况，我认为您只需要获取您关心的一组实例并自行组织它们：

def findMostRecentPerUser( Owner owner ) {
  def someDate = new Date() - 5 // 5 Days ago
  def result = [:].withDefault{[]}
  Table.withCriteria {
    eq('owner.id', owner.id)
    gt('due_date', someDate)
    order('due_date', 'desc')
  }.each{result[it.user.id] << it}
  return result
}

但是，正如您所看到的，这只会为您提供在某个日期之后发生的实例（或者删除 gt 语句，它为您提供所有实例，这可能不是您想要的）。您可以使用 distinct('user.id') 将其限制为每个用户的最近一个实例，

但是如果您想要每个用户的 5 个最近实例，我认为您会为每个用户执行查询（当然，将结果集限制为 5 个）。如果有人有这个案例的解决方案，我非常有兴趣找到解决方案。