How to GROUP and SUM a pivot table column in Eloqu

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

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

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

    ...
}