如何在 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.

我的项目模型有这个:

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屋！