如何对 Eloquent 关系中的数据透视表列进行 GROUP 和 SUM? [英] How to GROUP and SUM a pivot table column in Eloquent relationship?

问题描述

在 Laravel 4 中；我有模型 Project 和 Part，它们与数据透视表 project_part 具有多对多关系.数据透视表有一列 count，其中包含项目中使用的部件 ID 的编号，例如:

In Laravel 4; I have model Project and Part, they have a many-to-many relationship with a pivot table project_part. The pivot table has a column count which contains the number of a part ID used on a project, e.g.:

id  project_id  part_id count
24  6           230     3

这里的project_id 6，是使用3个part_id 230.

Here the project_id 6, is using 3 pieces of part_id 230.

同一项目的一个部分可能会被多次列出，例如:

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

当我显示我的项目的部件列表时，我不想显示 part_id 两次，所以我将结果分组.

When I show a parts list for my project I do not want to show part_id twice, so i group the results.

我的 Projects 模型有这个:

public function parts()
{
    return $this->belongsToMany('Part', 'project_part', 'project_id', 'part_id')
         ->withPivot('count')
         ->withTimestamps()
         ->groupBy('pivot_part_id')
}

当然，我的 count 值不正确，我的问题来了:如何获得项目所有分组部分的总和?

But of course my count value is not correct, and here comes my problem: How do I get the sum of all grouped parts for a project?

这意味着我的 project_id 6 的零件清单应如下所示:

Meaning that my parts list for project_id 6 should look like:

part_id count
230     4

我真的很想把它放在 Projects-Parts 关系中，这样我就可以急切地加载它.

I would really like to have it in the Projects-Parts relationship so I can eager load it.

如果没有遇到 N+1 问题，我无法解决如何做到这一点，感谢任何见解.

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.

public function sumPart($project_id)
{
    $parts = DB::table('project_part')
        ->where('project_id', $project_id)
        ->where('part_id', $this->id)
        ->sum('count');

    return $parts;
}

推荐答案

来自 代码源:

我们需要使用pivot_"前缀为所有枢轴列起别名，以便我们可以轻松地将它们从模型中提取出来，并在它们被检索并混合到模型中时将它们放入枢轴关系中.

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.

所以你可以用 select 方法做同样的事情

So you can do the same with select method

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')
}

这篇关于如何对 Eloquent 关系中的数据透视表列进行 GROUP 和 SUM?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！