laravel 如何在 Eloquent 关系中对数据透视表列进行 GROUP 和 SUM？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28267550/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to GROUP and SUM a pivot table column in Eloquent relationship?
提问by Thomas Jensen
In Laravel 4; I have model Projectand Part, they have a many-to-many relationship with a pivot table project_part. The pivot table has a column countwhich contains the number of a part ID used on a project, e.g.:
在 Laravel 4 中；我有模型，Project并且Part它们与数据透视表具有多对多关系project_part。数据透视表有一列count包含项目中使用的部件 ID 的编号，例如：
id project_id part_id count
24 6 230 3
Here the project_id6, is using 3 pieces of part_id230.
这里的project_id6，使用了 3 个part_id230。
One part may be listed multiple times for the same project, e.g.:
同一项目的一个部分可能会被多次列出，例如：
id project_id part_id count
24 6 230 3
92 6 230 1
When I show a parts list for my project I do not want to show part_idtwice, so i group the results.
当我为我的项目显示零件清单时，我不想显示part_id两次，所以我将结果分组。
My Projectsmodel has this:
我的项目模型有这个：
public function parts()
{
return $this->belongsToMany('Part', 'project_part', 'project_id', 'part_id')
->withPivot('count')
->withTimestamps()
->groupBy('pivot_part_id')
}
But of course my countvalue is not correct, and here comes my problem: How do I get the sum of all grouped parts for a project?
但当然我的count价值是不正确的，这就是我的问题：我如何获得一个项目的所有分组部分的总和？
Meaning that my parts list for project_id6 should look like:
这意味着我的project_id6零件清单应如下所示：
part_id count
230 4
I would really like to have it in the Projects-Partsrelationship so I can eager load it.
我真的很想把它放在Projects-Parts关系中，这样我就可以急切地加载它。
I can not wrap my head around how to do this without getting the N+1 problem, any insight is appreciated.
我无法在没有遇到 N+1 问题的情况下围绕如何做到这一点，任何见解表示赞赏。
Update:As a temporary work-around I have created a presenter method to get the total part count in a project. But this is giving me the N+1 issue.
更新：作为临时解决方法，我创建了一个演示者方法来获取项目中的总零件数。但这给了我 N+1 问题。
public function sumPart($project_id)
{
$parts = DB::table('project_part')
->where('project_id', $project_id)
->where('part_id', $this->id)
->sum('count');
return $parts;
}
采纳答案by Razor
From the code source:
从代码源：
We need to alias all of the pivot columns with the "pivot_" prefix so we can easily extract them out of the models and put them into the pivot relationships when they are retrieved and hydrated into the models.
我们需要使用“pivot_”前缀为所有枢轴列添加别名，以便我们可以轻松地将它们从模型中提取出来，并在它们被检索并加入模型时将它们放入枢轴关系中。
So you can do the same with selectmethod
所以你可以用select方法做同样的事情
public function parts()
{
return $this->belongsToMany('Part', 'project_part', 'project_id', 'part_id')
->selectRaw('parts.*, sum(project_part.count) as pivot_count')
->withTimestamps()
->groupBy('project_part.pivot_part_id')
}
回答by chuangbo
Try to sum in Collection,
试着总结一下Collection，
$project->parts->sum('pivot.count');
This is best way I found. It's clean (easy to read) and able to re-use all of your scope, ordering and relation attribute caching in partsmany-to-many defination.
这是我找到的最好方法。它很干净（易于阅读）并且能够在parts多对多定义中重用所有的范围、排序和关系属性缓存。
@hebron No N+1 problem for this solution if you use with('parts')to eager load. Because $project->parts(withoutfuntion call) is a cached attribute, return a instance of Collectionwith all your data. And sum('pivot.count')is a method of Collectionwhich contains pure funcional helpers (not relative to database, like underscorein js world).
该解决方案@hebron没有N + 1的问题，如果您使用with('parts')到渴望负荷。因为$project->parts（没有函数调用）是一个缓存属性，所以返回一个包含所有数据的Collection实例。Andsum('pivot.count')是一种Collection包含纯函数式助手的方法（与数据库无关，就像js 世界中的下划线）。
Full example:
完整示例：
Definition of relation parts:
关系部分的定义：
class Project extends Model
{
public function parts()
{
return $this->belongsToMany('Part', 'project_part', 'project_id', 'part_id')
->withPivot('count')
->withTimestamps();
}
}
When you use it (note that eager load is important to avoid N+1 problem),
使用时（注意急切加载对于避免N+1问题很重要），
App\Project::with('parts')->get()->each(function ($project) {
dump($project->parts->sum('pivot.count'));
});
Or you can define the sum function in Project.php,
或者你可以在 Project.php 中定义 sum 函数，
class Project extends Model
{
...
/**
* Get parts count.
*
* @return integer
*/
public function partsCount()
{
return $this->parts->sum('pivot.count');
}
}
If you want to avoid with('parts')on caller side (eager load parts by default), you can add a $withattribute
如果您想避免with('parts')在调用方（默认情况下急切加载部件），您可以添加一个$with属性
class Project extends Model
{
/**
* The relations to eager load on every query.
*
* @var array
*/
protected $with = ['parts'];
...
}
回答by Mahmoud
The best way that you can use is:
您可以使用的最佳方法是：
$project->parts->sum('pivot.count');
I faced the same problem, but this solved my issue.
我遇到了同样的问题，但这解决了我的问题。
