如何限制每个记录/组包括联想？ [英] How to limit contained associations per record/group?

问题描述

我有一个模型，文章，其中的hasMany文摘。我要加载的10最新的文章，并为每篇文章，摘要同分人数最多的。我的功能看起来像这样：

I have a Model, Articles, which hasMany Abstracts. I want to load the 10 latest Articles, and for each Article, the Abstract with the highest number of points. My function looks like this:

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的进入摘要表，使用这些10 articles.id的只是发现的，并要求以最高票10文摘（属于那些文章）。

The result that I get is not what I intend though. Looking through the SQL, first CakePHP is getting the articles.id of everything in the category (fine). Then, CakePHP goes into the Abstracts table, using those 10 articles.id's it just found, and asks for the 10 Abstracts with the highest votes (that belong to those Articles).

问题是，我希望1抽象为每篇文章，而不是10文摘属于该类别中的任何文章。我该如何解决这个问题？谢谢！

The problem is that I want 1 Abstract for each Article, not the 10 Abstracts belonging to any Article in that category. How can I fix this? Thanks!

修改

NDM认为，这是的使用限制（）上包含的模型重复所以我试图解决那里。也就是说，我已将此添加到我的模型：

ndm suggested that this was a duplicate of Using limit() on contained model so I attempted the solution there. Namely, I added this to my Model:

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

然后我试图找到文章byCategory，与包含（['TopAbstract']），仅此杀死了我的SQL。它死一个可怕的死亡：

And then I try to find the Articles byCategory, with contain(['TopAbstract']), only this kills my SQL. It dies a horrible death:

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

调试甚至不显示，把它打死了查询，所以我不知道如何调试这一个？

Debug doesn't even show the query that killed it, so I'm not sure how to debug this one?

修改

在自言自语了一下，但错误绝对是hasOne的'条件'的一部分。我采取了，它工作正常。找不到这应该是怎样看的interwebs例子..任何人有任何想法？

Talking to myself a bit, but the error is definitely in the 'conditions' part of the hasOne. I take that out, and it works fine. Can't find an example of how this is supposed to look on the interwebs.. anyone have any idea?

推荐答案

您正在寻找的，是对的最大正每组问题。你没有提到任何具体的RDBMS，也仍然看到的http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html

What you are looking for, is a solution to the greatest-n-per-group problem. You didn't mention any specific RDBMS, but nonetheless see also http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html

让我们给这一个尝试，这里是一个可以在联想级别应用三个选项，但是你可能会认为他们不会的是的直白。

So let's give this a try, here's three options that can be applied on association level, however you might consider them as not that "straightforward".

$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 [];
    }
]);

这将选择通过一个基于最大点连接查询顶部摘要，它看起来像

This will select the top abstracts via a join query that is based on the max points, it will look something like

SELECT
    TopAbstracts.id AS `TopAbstracts__id`, ...
FROM
    abstracts TopAbstracts
INNER JOIN (
        SELECT
            article_id, (MAX(points)) AS `points`
        FROM
            abstracts
        GROUP BY
            article_id
    )
    FilterAbstracts ON (
        TopAbstracts.article_id = FilterAbstracts.article_id
        AND
        TopAbstracts.points = FilterAbstracts.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＆LT; b.points ，它看起来像

This will use a self-join that filters based on the rows that don't have a.points < b.points, it will look something like

SELECT
    TopAbstracts.id AS `TopAbstracts__id`, ...
FROM 
    abstracts TopAbstracts
LEFT JOIN
    abstracts FilterAbstracts ON (
        TopAbstracts.article_id = FilterAbstracts.article_id
        AND
        TopAbstracts.points < FilterAbstracts.points
    )
WHERE
    (FilterAbstracts.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 选项设置为假，以避免额外的 Articles.id = TopAbstracts.article_id 条件编译到连接条件。

This will use a correlated subquery that uses a rather specific select with simple ordering and limiting to pick the top comment. Note that the foreignKey option is set to false in order to avoid an additional Articles.id = TopAbstracts.article_id condition to be compiled into the join conditions.

查询看起来像

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个选项将查询，没有任何两轮牛车注入的记录，它只是不是很爽快。

---

All these 3 options will query and inject the records without any hackery, it's just not very "straightforward".

为了完整起见，当然总是可以手动加载associcated记录和使用结果格式化结果适当格式，例如，例如参见 <一个href=\"http://stackoverflow.com/questions/30251374/cakephp-entity-contain-without-foreign-key/30275843#30275843\">CakePHP实体不包含外键

For the sake of completeness, it is of course always possible to manually load the associcated records and format the results appropriately, for example using result formatters, see for example CakePHP Entity contain without foreign key

仅供参考，怪异的解决方案，我绊了最初之一。这个人真的不应该使用！

这将选择所有相关的摘要，然后ORM将遍历它们并为每篇文章选择第一个具有匹配的article_id 值。所以理论上，在点有序descing时，ORM应该选择一个与他最高分。

This will select all associated abstracts, and then the ORM will iterate over them and for each article pick the first one with a matching article_id value. So in theory, when ordered descing on points, the ORM should pick the one with he most points.

虽然我本来期望这工作开箱即用，似乎比结果相反的顺序，这将导致错误的行ORM迭代被拾起。为了得到这个工作，查询需要使用通常需要使用，即 ASC 而不是 DESC 。

While I would have expected this to work out of the box, it seems that the ORM iterates over the results in reversed order, which will cause the wrong rows to be picked. In order to get this working, the query needs to use the opposite order that would normally need to be used, ie ASC instead of 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 [];
    }
]);

另外，函数需要返回一个空数组，而不是前pression像在链接的答案显示，因为这将导致编译无效的SQL。这两种行为，相反的顺序迭代和无效的SQL的可能是错误。

Also the function needs to return an empty array instead of the expression like shown in the linked answer, as this will cause invalid SQL to be compiled. Both of these behaviours, the reversed order iterating and the invalid SQL might be bugs.

虽然这会工作，它总是会选择所有相关的摘要，不仅是顶级的人，这可能被认为是相当低效，和看起来像

While this will work, it will always select all associated abstracts, not only the top ones, which might be considered rather inefficient, and look something like

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

这篇关于如何限制每个记录/组包括联想？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！