cakephp - 如何限制每个记录/组包含的关联？

Question

我有一个模型，文章，其中有许多摘要。我想加载 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 正在获取类别中所有内容的 article.id(很好)。然后，CakePHP 进入 Abstracts 表，使用它刚刚找到的那 10 个articles.id，并要求获得最高票数的 10 个 Abstracts(属于那些 Articles)。

问题是我希望每篇文章有 1 个摘要，而不是属于该类别中任何文章的 10 个摘要。我怎样才能解决这个问题？谢谢!

编辑

ndm 建议这是 Using limit() on contained model 的副本所以我在那里尝试了解决方案。即，我将此添加到我的模型中:

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

然后我尝试使用包含(['TopAbstract'])，按类别查找文章，这只会杀死我的 SQL。它死于可怕的死亡:

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

调试甚至没有显示杀死它的查询，所以我不确定如何调试这个？

编辑

有点自言自语，但错误肯定在 hasOne 的“条件”部分。我把它拿出来，它工作正常。找不到在互联网上应该如何看待的示例..有人知道吗？

Answer 1

您正在寻找的是 greatest-n-per-group 的解决方案问题。您没有提及任何特定的 RDBMS，但也请参阅 http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html
图书馆解决方案
对于那些有点冒险的人，我开发了一些自定义关联，它们透明地集成到 ORM 层中，并允许每组的基本限制为 hasMany和 belongsToMany关系: https://github.com/icings/partitionable .
使用它们，问题的解决方案是建立这样的关联:

$this
    ->partitionableHasMany('TopAbstracts')
    ->setClassName('Abstracts')
    ->setLimit(1)
    ->setSort([
        'Abstracts.points' => 'DESC',
        'Abstracts.id' => 'ASC',
    ]);

TopAbstracts然后可以像任何其他关联一样被包含。
关联级别的定制解决方案
所以让我们试一试，这里有三个可以应用于关联级别的选项(定义条件也可以移动到自定义查找器中)，但是您可能认为它们不是那么“直截了当”。

选择策略 - 在分组、最大值子查询上使用连接

$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 个选项都将查询和注入(inject)记录而无需任何黑客，这不是很“直截了当”。

手动方法
为了完整起见，当然总是可以手动加载关联的记录并适本地格式化结果，例如使用结果格式化程序，参见示例 CakePHP Entity contain without foreign key

选择策略和逆序
仅供引用，我最初偶然发现的奇怪解决方案之一。这个真的不应该用!
这将选择所有相关的摘要，然后 ORM 将对其进行迭代，并为每篇文章选择第一个匹配的 article_id值(value)。所以理论上，当在 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