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;
}
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')
}
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments