Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

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;
}
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

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

    ...
}

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...