How to GROUP and SUM a pivot table column in Eloquent relationship?

Question

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

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

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

My Projects model 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 count value is not correct, and here comes my problem: How do I get the sum of all grouped parts for a project?

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

part_id count
230     4

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

I can not wrap my head around how to do this without getting the N+1 problem, any insight is appreciated.

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;
}

chuangbo · Answer 1 · 2015-10-18T23:42:47.357

Try to sum in 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 parts many-to-many defination.

@hebron No N+1 problem for this solution if you use with('parts') to eager load. Because $project->parts (without funtion call) is a cached attribute, return a instance of Collection with all your data. And sum('pivot.count') is a method of Collection which contains pure funcional helpers (not relative to database, like underscore in js world).

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),

App\Project::with('parts')->get()->each(function ($project) {
    dump($project->parts->sum('pivot.count'));
});

Or you can define the sum function in Project.php,

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 $with attribute

class Project extends Model
{
    /**
     * The relations to eager load on every query.
     *
     * @var array
     */
    protected $with = ['parts'];

    ...
}

@hebron No N+1 problem of this solution. Example have been updated. — chuangbo, Oct 18 '15 at 23:38

Razor · Accepted Answer · 2015-02-10T13:52:45.630

10

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.

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

edited Feb 10 '15 at 13:52

answered Feb 09 '15 at 17:44

Razor

9,577
3
36
51

That seems to work, only I had to add `parts.*` to the `selectRaw` as well, otherwise I didn't get any of the other fields. I'll test it thoroughly and let you know. Thanks :) – Thomas Jensen Feb 09 '15 at 18:33
I don't think you need to add `parts.*`, Eloquent [add it](https://github.com/laravel/framework/blob/5.0/src/Illuminate/Database/Eloquent/Relations/BelongsToMany.php#L283) behind the scenes. – Razor Feb 09 '15 at 18:52
That's what I though as well, but it's not part of the query without adding it. – Thomas Jensen Feb 09 '15 at 19:31
pretty weird, how you actually build your query? can you share executed queries? what version you use? what exactly you get without `part.*` (dump it please)? – Razor Feb 09 '15 at 22:40
I am using Laravel 4.2, I have moved things around a bit. I do the `selectRaw` in the controller, not on the relationship, this way I can choose whether I want to merge rows or not. http://pastebin.com/KTZRFigU Does this cover it? – Thomas Jensen Feb 10 '15 at 07:44
I meant exactly what version 4.2.(?) ... anyway, I tested with an old version 4.2.1 and it works perfectly, but for L4.2.16 it seems that you must add `parts.*` which is more logical (answer updated). As note, when you use a join ( this is the case with belongsToMany) it is preferable to add the name of table to your columns `orderBy('tableName.description_en')` – Razor Feb 10 '15 at 14:06
Sorry, my bad. I do use version 4.2.16. Thank you for the tip, and the solution. Much appreciated :) – Thomas Jensen Feb 10 '15 at 14:09
cool, this is clean solution for pivotting sum of group, at least for my case :) – Andi Wijaya Oct 05 '20 at 09:37

score 2 · Answer 3 · edited Mar 26 '20 at 07:43

2

The best way that you can use is:

$project->parts->sum('pivot.count');

I faced the same problem, but this solved my issue.

edited Mar 26 '20 at 07:43

סטנלי גרונן

2,917
23
46
68

answered Mar 26 '20 at 03:33

Mahmoud

167
2
7

How to GROUP and SUM a pivot table column in Eloquent relationship?

3 Answers3

Linked