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

Question

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

问题与此基本相同:
Retrieving the last record in each group
...除了我使用的是 DBIx::Class 而不是原始 SQL。

把问题放在上下文中:

我有一张音乐评论表

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

任何给定的artist_id/album_id 都可以有多个评论。
我想要最新的评论，按日期降序排列，每个艺术家 ID/专辑 ID 不超过一个评论。

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

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

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

Answer 1

为此，您依赖于损坏的数据库行为。当您使用 时，您应该无法从表中选择列。分组 除非它们使用聚合函数(min、max 等)或在 group by 子句中指定。

在 MySQL 中，even the manual admits this is wrong - 虽然它支持它。

我认为您需要做的是使用 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 语句中返回哪一个？