如何限制每个记录/组包含的关联? [英] How to limit contained associations per record/group?
问题描述
我有一个模型，文章，其中有许多摘要.我想加载最新的 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 进入 Abstracts 表，使用它刚刚找到的 10 个文章的 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 建议这是 在包含模型上使用 limit() 的重复所以我在那里尝试了解决方案.即，我将此添加到我的模型中:
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;
} ]);
然后我尝试按类别查找文章，使用 contains(['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 的条件"部分.我把它拿出来，它工作正常.无法在互联网上找到应该如何看待的示例.. 有谁知道吗?
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?
推荐答案
您正在寻找的是 greatest-n-per-group 问题.您没有提到任何特定的 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
对于那些有点冒险的人，我开发了一些自定义关联，它们透明地集成到 ORM 层中，并允许对 hasMany 和 belongsToMany 关系:https://github.com/icings/partitionable.
For those who are a little bit adventurous, I've developed some custom associations that transparently integrate into the ORM layer, and allow for basic limit per group for hasMany and belongsToMany relations: https://github.com/icings/partitionable.
使用它们，问题的解决方案是建立这样的关联:
Using them, the solution for the question would be to set up an association like this:
$this
->partitionableHasMany('TopAbstracts')
->setClassName('Abstracts')
->setLimit(1)
->setSort([
'Abstracts.points' => 'DESC',
'Abstracts.id' => 'ASC',
]);
TopAbstracts 然后可以像任何其他关联一样被包含.
TopAbstracts could then be contained just like any other association.
所以让我们尝试一下，这里有三个可以应用于关联级别的选项(定义条件也可以移动到自定义查找器中)，但是您可能认为它们不是那个 "直截了当".
So let's give this a try, here's three options that can be applied on association level (defining the conditions could also be moved into custom finders), however you might consider them as not that "straightforward".
$this->hasOne('TopAbstracts', [
'className' => 'Abstracts',
'strategy' => 'select',
'conditions' => function (CakeDatabaseExpressionQueryExpression $exp, CakeORMQuery $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
)
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 (CakeDatabaseExpressionQueryExpression $exp, CakeORMQuery $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，它看起来像
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 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 (CakeDatabaseExpressionQueryExpression $exp, CakeORMQuery $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 条件编译到连接中条件.
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".
为了完整起见，当然总是可以手动加载关联的记录并适当地格式化结果，例如使用结果格式化程序，参见例如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 值的摘要.所以理论上，当对 points 进行降序排序时，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 (CakeDatabaseExpressionQueryExpression $exp, CakeORMQuery $query) {
$query->order(['TopAbstracts.points' => 'ASC']);
return [];
}
]);
此外，该函数需要返回一个空数组，而不是链接答案中所示的表达式，因为这会导致编译无效的 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屋！
