Eager loading sum from two pivot tables with Eloquent

Question

I have a Part entity that has a many-to-many relationship with Order and Project. The pivot tables contains a quantity field which might have a positive or negative value, and by adding the sums of both pivot tables, for a specific part, I get the current stock.

I am able to do this, but get the N+1 problem. And I am having a hard time figuring out how I can do it with eager loading. I've read this post, but I don't understand how to adapt it to my needs.

So I am looking for a way to provide a eager loadable stock property on the Part model, suggestions to how I can accomplish this?

score 0 · Accepted Answer · edited May 23 '17 at 10:27

I was able to figure it out with the help of this post. This is what I did:

Part model

Create two relationship orderPartsCount and projectPartsCount, add attribute calculatedStock to sum them and provide an easy way of retrieving.

public function orderPartsCount()
{
    $a = $this->orders();
    $a1 = $a->selectRaw($a->getForeignKey() . ', sum(count) as stock')
            ->where('done', '>', 0)
            ->groupBy($a->getForeignKey()
        );

    return $a1;
}

public function projectPartsCount()
{
    $b = $this->projects();
    $b1 = $b->selectRaw($b->getForeignKey() . ', sum(count) as stock')
            ->where('status', '>', 0)
            ->groupBy($b->getForeignKey()
        );

    return $b1;
}

public function getCalculatedStockAttribute()
{
    $orders = $this->orderPartsCount->first() ? $this->orderPartsCount->first()->stock : 0;
    $projects = $this->projectPartsCount->first() ? $this->projectPartsCount->first()->stock : 0;

    // invert project parts, since they are listed as positive counts but shall reduce the stock
    return $orders + ( $projects * -1);
}

Part controller

Eager load orderPartsCount and projectPartsCount in the controller.

public function index()
{
    return View::make('parts.index', [
        'parts' => Part::with('category', 'location', 'orderPartsCount', 'projectPartsCount')
            ->orderBy('description_no')
            ->paginate(100)
    ]);
}

Eager loading sum from two pivot tables with Eloquent

1 Answers1