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

Answer 1

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

    ...
}