CODEWITHSUNDEEP
sqlperlgroup-bysql-order-bydbix-class
Sir Robert
Sir Robert

Reputation: 4976

How to ORDER before GROUP with DBIx::Class

I've got a simple temporal table that looks like this:

Table: item_approval

item  user  status         modified
2     fred  approved       2010-12-01 00:00:00
3     fred  approved       2010-12-02 00:00:00
4     fred  disapproved    2010-12-03 00:00:00
7     jack  unapproved     2010-12-05 00:00:00
4     fred  approved       2010-12-06 00:00:00
4     jack  unapproved     2010-12-07 00:00:00
4     fred  disapproved    2010-12-04 00:00:00

I'm using DBIx::Class. My "Item" result is defined with:

__PACKAGE__->has_many(
  "item_approvals",
  "Schema::Result::ItemApproval",
  { "foreign.item" => "self.id" },
  { cascade_copy => 0, cascade_delete => 0 },
);

Which means I can do:

my $item = $schema->resultset('Item')->find({id=>4});

Which is fine. Then, I can do:

my @approvals = $item->item_approvals;

to get a resultset like this:

item  user  status         modified
4     fred  disapproved    2010-12-03 00:00:00
4     fred  approved       2010-12-06 00:00:00
4     jack  unapproved     2010-12-07 00:00:00
4     fred  disapproved    2010-12-04 00:00:00

My question: How do I get the set of Fred and Jack's single most recent approval status? That is, I want to get this resultset:

item  user  status         modified
4     fred  approved       2010-12-06 00:00:00
4     jack  unapproved     2010-12-07 00:00:00

I tried things like this:

my @approvals = $item->search({}, {
    group_by => 'user',
    order_by => {-desc => 'modified'}
});

but the "ORDER BY" is executed after the "GROUP BY", so I get things like this instead:

item  user  status         modified
4     fred  disapproved    2010-12-03 00:00:00
4     jack  unapproved     2010-12-07 00:00:00

Help?

Upvotes: 0

Views: 722

Answers (1)

Ven'Tatsu
Ven'Tatsu

Reputation: 3635

From the behavior described in your comments I'm guessing your database is MySQL. I'm also assuming your item_approval table has a primary key which I will call PK.

One option is to use a sub select to pick the row that has the largest (most recent) modified value:

select item, user, status, modified 
from item_approval me 
where PK = (select s.PK from item_approval s where me.item = s.item and me.user = s.user order by s.modified desc, s.PK desc limit 1) 
and me.item = 4

This is a fairly slow option because it will re-run the sub select for each row and then reject all but one row for each item/user combination. Other databases have slightly different ways to get similar results.

Upvotes: 1

Related Questions