Как ORDER перед GROUP с DBIx::Class

У меня есть простая временная таблица, которая выглядит так:

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. Мой результат "Item" определяется с помощью:

__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

Мой вопрос: как мне получить набор самых последних статусов одобрения Фреда и Джека? То есть я хочу получить этот набор результатов:

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

Помощь?


sql group-by sql-order-by perl dbix-class
person Sir Robert    schedule 15.12.2010    source источник
comment
Я не знаю perl или dbix, но способ SQL сделать это состоит в том, чтобы сделать ваше поле modified полем MAX(modified) as 'Modified', которое будет возвращать самую высокую дату для каждого пользователя.   -  person JNK    schedule 15.12.2010
comment
Я немного поиграл с этим, используя такие вещи, как: SELECT item, user, status, MAX(modified) as most_recent FROM item_approval WHERE id=4; но он не возвращает самую высокую дату на пользователя; он просто возвращает одну строку с самой высокой датой.   -  person Sir Robert    schedule 15.12.2010
comment
вам нужно сделать как MAX(Modified), так и GROUP BY User. MAX без GROUP дает вам максимум для запроса. GROUP BY дает вам максимум (или другие агрегатные функции, такие как SUM, AVG и т. д.) для каждой группы в GROUP BY.   -  person JNK    schedule 15.12.2010
comment
Я изменил его на [ВЫБЕРИТЕ элемент, пользователя, статус, МАКС(модифицировано) как изменено ОТ item_approval me WHERE ( me.item = 4 ) СГРУППИРОВАТЬ ПО пользователю], но это имело странный эффект, заключающийся в предоставлении только максимального модифицированного, а не строка с максимальным изменением. То есть он дал мне первую строку, но с неправильным измененным значением (измененное значение строки с максимальным изменением для этого пользователя).   -  person Sir Robert    schedule 15.12.2010

Ответы (1)


arrow_upward
1
arrow_downward

Судя по поведению, описанному в ваших комментариях, я предполагаю, что ваша база данных - MySQL. Я также предполагаю, что ваша таблица item_approval имеет первичный ключ, который я назову PK.

Один из вариантов — использовать дополнительный выбор, чтобы выбрать строку с наибольшим (самым последним) значением modified:

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

Это довольно медленный вариант, потому что он повторно запускает вложенный выбор для каждой строки, а затем отклоняет все строки, кроме одной, для каждой комбинации элемента/пользователя. Другие базы данных имеют несколько иные способы получения аналогичных результатов.

person Ven'Tatsu    schedule 15.12.2010