How can I retrieve the newest record in each group in a DBIx::Class resultset search?

Question

I'm using group_by in a DBIx::Class resultset search. The result returned for each group is always the row in the group with the lowest id (i.e the oldest row in the group). I'm looking for a way to get the row with the highest id (i.e. the newest row in the group) instead.

The problem is fundamentally the same as this: Retrieving the last record in each group ...except that I'm using DBIx::Class not raw SQL.

To put the question in context:

I have a table of music reviews

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

There can be multiple reviews for any given artist_id/album_id. I want the most recent reviews, in descending date order, with no more than one review per artist_id/album_id.

I tried to do this using:

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

This nearly works, but returns the oldest review in each group instead of the newest. How can I get the newest?

J0HN - the order_by is applied after the grouping has been done so it won't affect which result is returned for each group. — nick, Aug 22 '11 at 13:20

score 2 · Accepted Answer · answered Aug 22 '11 at 14:15

For this to work you are relying on broken database behaviour. You should not be able to select columns from a table when you use group by unless they use an aggregate function (min, max etc.) or are specified in the group by clause.

In MySQL, even the manual admits this is wrong - though it supports it.

What I think you need to do is get the latest dates of the reviews, with 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) ],
  }
);

Then loop through to get the review:

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;
}

Yep - it's more queries but it makes sense - what if two reviews are on the same date - how should the DB know which one to return in the select statement?

How can I retrieve the newest record in each group in a DBIx::Class resultset search?

1 Answers1