如何在 DBIx::Class 结果集搜索中检索每个组中的最新记录？

Question

我在 DBIx::Class 结果集搜索。为每个组返回的结果始终是组中具有最低 id 的行（即组中最旧的行）。我正在寻找一种方法来获取具有最高 id 的行（即组中的最新行）。

问题本质上是一样的： 检索每个组中的最后一条记录 ...除了我使用的是 DBIx::Class 而不是原始 SQL。

将问题放在上下文中：

我有一个音乐评论表，

review
------
id
artist_id
album_id
pub_date
...other_columns...

对于任何给定的 Artist_id/album_id 都可以有多个评论。 我想要最新的评论，按日期降序排列，每个 Artist_id/album_id 的评论不超过一条。

我尝试使用以下方法来执行此操作：

$schema->resultset('Review')->search(
  undef,
  {
    group_by => [ qw/ artist_id album_id / ],
    order_by => { -desc => 'pub_date' },
  }
);

这几乎有效，但返回每个组中最旧的评论而不是最新的评论。 我怎样才能得到最新的？

Answer 1

为此，您依赖于损坏的数据库行为。当您使用 group by 时，您不应该能够从表中选择列，除非它们使用聚合函数（最小值、最大值等）或在 group by 子句中指定。

在 MySQL 中，甚至手册也承认这是错误的< /a> - 尽管它支持它。

我认为你需要做的是获取评论的最新日期，使用 max(pub_date):

my $dates = $schema->resultset('Review')->search({},
  {
    select   => ['artist_id', 'album_id', {max => 'pub_date'}],
    as       => [ qw(artist_id album_id recent_pub_date) ],
    group_by => [ qw(artist_id album_id) ],
  }
);

然后循环获取评论：

while (my $review_date = $dates->next) {
    my $review = $schema->resultset('Review')->search({
        artist_id => $review_date->artist_id,
        album_id  => $review_date->album_id,
        pub_date  => $review_date->get_column('recent_pub_date'),
    })->first;
}

是的 - 这是更多查询，但这是有道理的 - 如果两个评论在同一日期怎么办 -数据库应该如何知道在 select 语句中返回哪一个？