如何使用 DBIx::Class 在 GROUP 之前进行 ORDER
我有一个简单的时态表,如下所示:
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
我正在使用 DBIx::Class。我的“项目”结果定义为:
__PACKAGE__->has_many(
"item_approvals",
"Schema::Result::ItemApproval",
{ "foreign.item" => "self.id" },
{ cascade_copy => 0, cascade_delete => 0 },
);
这意味着我可以做:
my $item = $schema->resultset('Item')->find({id=>4});
这很好。然后,我可以这样做:
my @approvals = $item->item_approvals;
获得这样的结果集:
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
我的问题:如何获得 Fred 和 Jack 的单个最近批准状态集?也就是说,我想得到这个结果集:
item user status modified
4 fred approved 2010-12-06 00:00:00
4 jack unapproved 2010-12-07 00:00:00
我尝试了这样的事情:
my @approvals = $item->search({}, {
group_by => 'user',
order_by => {-desc => 'modified'}
});
但是“ORDER BY”是在“GROUP BY”之后执行的,所以我得到了这样的结果:
item user status modified
4 fred disapproved 2010-12-03 00:00:00
4 jack unapproved 2010-12-07 00:00:00
帮助?
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
根据您评论中描述的行为,我猜测您的数据库是 MySQL。
我还假设您的
item_approval
表有一个主键,我将其称为PK
。一种选择是使用子选择来选择具有最大(最新)修改值的行:
这是一个相当慢的选项,因为它将为每一行重新运行子选择,然后对于每个项目/用户组合,拒绝除一行之外的所有内容。
其他数据库获得类似结果的方法略有不同。
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 callPK
.One option is to use a sub select to pick the row that has the largest (most recent)
modified
value: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.