Как ограничить содержащиеся ассоциации на запись/группу?

У меня есть модель, статьи, в которой есть много рефератов. Я хочу загрузить 10 последних статей, а для каждой статьи - тезисы с наибольшим количеством очков. Моя функция выглядит так:

public function getArticles($category, $viewName) {
            $subArticles = $this->Articles->findByCategory($category)->contain([
                    'Abstracts' => function ($q) {
                            return $q
                                    ->select(['body', 'points', 'article_id'])
                                    ->where(['Abstracts.approved' => true])
                                    ->limit(10)
                                    ->order(['Abstracts.points' => 'DESC']);
                    }
            ])
            ->limit(10)
            ->order(['Articles.created' => 'DESC']) ;
            $this->set( $viewName . 'Articles', $subArticles );
    }

Результат, который я получаю, не то, что я предполагаю. Просматривая SQL, сначала CakePHP получает articles.id всего в категории (отлично). Затем CakePHP переходит в таблицу Abstracts, используя те 10 articles.id, которые он только что нашел, и просит 10 тезисов с самыми высокими голосами (которые принадлежат этим статьям).

Проблема в том, что я хочу 1 Аннотация для каждой статьи, а не 10 тезисов, принадлежащих к любой статье этой категории. Как я могу это исправить? Спасибо!

ИЗМЕНИТЬ

ndm предположил, что это был дубликат Использование limit() на содержащейся модели, поэтому я попытался найти решение там. А именно, я добавил это к моей модели:

 $this->hasOne('TopAbstract', [
            'className' => 'Abstracts',
            'foreignKey' => 'abstract_id',
            'strategy' => 'select',
            'sort' => ['TopAbstract.points' => 'DESC'],
            'conditions' => function ($e, $query) {
            $query->limit(1);
            return $e;
    } ]);

И затем я пытаюсь найти статьи byCategory, содержащие contains (['TopAbstract']), только это убивает мой SQL. Он умирает от ужасной смерти:

Error: SQLSTATE[HY000]: General error: 1 near ")": syntax error

Отладка даже не показывает запрос, который его убил, поэтому я не уверен, как отлаживать этот файл?

ИЗМЕНИТЬ

Разговор с самим собой немного, но ошибка определенно находится в "условиях" части hasOne. Я вынимаю это, и он отлично работает. Невозможно найти пример того, как это должно выглядеть на interwebs. У кого-нибудь есть идея?

Ответы

Ответ 1

То, что вы ищете, является решением проблемы greatest-n-per-group. Вы не упомянули какую-либо конкретную СУБД, но, тем не менее, смотрите также http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html

Итак, давайте попробуем, вот три варианта, которые могут быть применены на уровне ассоциации (определение условий также может быть перенесено в пользовательские искатели), однако вы можете считать их не такими "простыми".

Для чего-то конкретного HasMany и BelongsToMany прокрутите до конца!

Выбор стратегии - использование объединения в групповом подзапросе с максимальным значением

$this->hasOne('TopAbstracts', [
    'className' => 'Abstracts',
    'strategy' => 'select',
    'conditions' => function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $query) {
        $query->innerJoin(
            [
                'AbstractsFilter' => $query
                    ->connection()
                    ->newQuery()
                    ->select(['article_id', 'points' => $query->func()->max('points')])
                    ->from('abstracts')
                    ->group('article_id')
            ],
            [
                'TopAbstracts.article_id = AbstractsFilter.article_id',
                'TopAbstracts.points = AbstractsFilter.points'
            ]
        );
        return [];
    }
]);

Это выберет лучшие тезисы с помощью запроса на соединение, основанного на максимальных баллах, это будет выглядеть примерно так:

SELECT
    TopAbstracts.id AS 'TopAbstracts__id', ...
FROM
    abstracts TopAbstracts
INNER JOIN (
        SELECT
            article_id, (MAX(points)) AS 'points'
        FROM
            abstracts
        GROUP BY
            article_id
    )
    AbstractsFilter ON (
        TopAbstracts.article_id = AbstractsFilter.article_id
        AND
        TopAbstracts.points = AbstractsFilter.points
    )
WHERE
    TopAbstracts.article_id in (1,2,3,4,5,6,7,8, ...)

Выбор стратегии - использование фильтрации с самообъединением слева

$this->hasOne('TopAbstracts', [
    'className' => 'Abstracts',
    'strategy' => 'select',
    'conditions' => function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $query) {
        $query->leftJoin(
            ['AbstractsFilter' => 'abstracts'],
            [
                'TopAbstracts.article_id = AbstractsFilter.article_id',
                'TopAbstracts.points < AbstractsFilter.points'
            ]);
        return $exp->add(['AbstractsFilter.id IS NULL']);
    }
]);

При этом будет использоваться самообъединение, которое фильтрует по строкам, которые не имеют a.points < b.points, это будет выглядеть примерно так:

SELECT
    TopAbstracts.id AS 'TopAbstracts__id', ...
FROM 
    abstracts TopAbstracts
LEFT JOIN
    abstracts AbstractsFilter ON (
        TopAbstracts.article_id = AbstractsFilter.article_id
        AND
        TopAbstracts.points < AbstractsFilter.points
    )
WHERE
    (AbstractsFilter.id IS NULL AND TopAbstracts.article_id in (1,2,3,4,5,6,7,8, ...))

Стратегия соединения - использование подзапроса для условия соединения

$this->hasOne('TopAbstracts', [
    'className' => 'Abstracts',
    'foreignKey' => false,
    'conditions' => function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $query) {
        $subquery = $query
            ->connection()
            ->newQuery()
            ->select(['SubTopAbstracts.id'])
            ->from(['SubTopAbstracts' => 'abstracts'])
            ->where(['Articles.id = SubTopAbstracts.article_id'])
            ->order(['SubTopAbstracts.points' => 'DESC'])
            ->limit(1);

        return $exp->add(['TopAbstracts.id' => $subquery]);
    }
]);

Это будет использовать коррелированный подзапрос, который использует довольно специфический выбор с простым упорядочением и ограничением, чтобы выбрать самый верхний комментарий. Обратите внимание, что для параметра foreignKey установлено значение false во избежание компиляции дополнительного условия Articles.id = TopAbstracts.article_id в условия соединения.

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

SELECT
    Articles.id AS 'Articles__id', ... ,
    TopAbstracts.id AS 'TopAbstracts__id', ...
FROM
    articles Articles
LEFT JOIN
    abstracts TopAbstracts ON (
        TopAbstracts.id = (
            SELECT
                SubTopAbstracts.id
            FROM
                abstracts SubTopAbstracts
            WHERE
                Articles.id = SubTopAbstracts.article_id
            ORDER BY
                SubTopAbstracts.points DESC
            LIMIT
                1
        )
    )

Все эти 3 варианта будут запрашивать и вставлять записи без каких-либо взломов, это просто не очень "просто".

Ручной подход

Ради полноты, конечно, всегда можно вручную загрузить связанные записи и соответствующим образом отформатировать результаты, например, используя средства форматирования результатов, см., Например, Объект CakePHP, содержащий без внешнего ключа

Выберите стратегию и обратный порядок

Просто для справки, одно из странных решений, с которыми я столкнулся изначально. Этот действительно не должен использоваться!

При этом будут выбраны все связанные тезисы, а затем ORM будет выполнять их итерацию, и для каждой статьи выберите первую с соответствующим значением article_id. Таким образом, теоретически, при заказе спуска на points, ORM должен выбрать тот, у которого больше всего очков.

Хотя я ожидал, что это сработает из коробки, кажется, что ORM перебирает результаты в обратном порядке, что приведет к неправильному выбору строк. Чтобы это работало, запрос должен использовать противоположный порядок, который обычно должен использоваться, то есть ASC вместо DESC.

$this->hasOne('TopAbstracts', [
    'className' => 'Abstracts',
    'foreignKey' => 'abstract_id',
    'strategy' => 'select',
    'conditions' => function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $query) {
        $query->order(['TopAbstracts.points' => 'ASC']);
        return [];
    }
]);

Кроме того, функция должна возвращать пустой массив вместо выражения, как показано в связанном ответе, поскольку это приведет к компиляции неверного SQL. Оба этих поведения, итерация в обратном порядке и неправильный SQL, могут быть ошибками.

Хотя это будет работать, он всегда будет выбирать все связанные тезисы, а не только верхние, которые могут показаться довольно неэффективными, и выглядеть примерно так:

SELECT
    Articles.id AS 'Articles__id', ...
FROM
    articles Articles

SELECT
    TopAbstracts.id AS 'TopAbstracts__id', ...
FROM
    abstracts TopAbstracts
WHERE
    TopAbstracts.article_id in (1,2,3,4,5,6,7,8, ...)
ORDER BY
    TopAbstracts.points ASC

Ассоциация HasMany

Я попробовал связать HasMany, но сейчас я слишком занят, чтобы заниматься этим дальше... просто собрал специальную пользовательскую ассоциацию MySQL для целей тестирования, основанную на эмуляции ROW_NUMBER(), аналогичной MySQL select top X записей для каждого человека в таблице.

Если кому-то интересно, проверьте https://gist.github.com/ndm2/039da4009df1c5bf1c262583603f8298

Принадлежит многим ассоциациям

Вот пример для ассоциаций BelongsToMany, которые используют собственные оконные функции, к сожалению, CakePHP пока не поддерживает общие выражения таблиц: https://gist.github.com/ndm2/b417e3fa683a972e295dc0e24ef515e3.