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

Acetamide

Answer 1

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

Eleni

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 :) – Acetamide

I don't think you need to add parts.*, Eloquent add it behind the scenes. – Eleni

That's what I though as well, but it's not part of the query without adding it. – Acetamide

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)? – Eleni

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. pastebin.com/KTZRFigU Does this cover it? – Acetamide

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') – Eleni

Sorry, my bad. I do use version 4.2.16. Thank you for the tip, and the solution. Much appreciated :) – Acetamide

cool, this is clean solution for pivotting sum of group, at least for my case :) – Dietetic

Answer 2

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'];

    ...
}

Loren

Won't this give me the N+1 problem? – Acetamide

@hebron No N+1 problem of this solution. Example have been updated. – Loren

Answer 3

The best way that you can use is:

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

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

Enumeration