Laravel / Eloquent hasMany relationship sum()

Question

I can't figure out how to eager load a sum of a relationships columns.

Database (simplified) is as follows;

TABLES

PRODUCT       PRODUCT_VARIATIONS
*ID*          *ID*
*NAME*        *NAME*
              *AVAILABLE_STOCK*

I have my relationships set up as follows;

public function variations()
{
    return $this->hasMany('Product_variation');
}

When loading all Products I want to be able to see the SUM of all stock for that product attached to the product object itself.

A product may have many variations.

I can return the entire INDIVIDUAL variations attached to the products (See Below)

$products = Product::with('variations')->paginate(15);

but I just want to return all the products with a simple integer showing their available_stock count taking into account all variations.

I want to be able to type

@foreach ($products as $product)
$product->available_stock  // Returns INT
@endforeach

Answer 1

Eloquent does not natively support relation count eager loading.

Read this article on how to implement it yourself:

How to get hasMany relation count efficiently.

Answer 2

The thing is, you don't want count, but sum. So here's what you need, just like in my article that @Joseph linked, only with different aggregate function:

public function availableStock()
{
    return $this->hasOne('Product_variation')
       ->selectRaw('product_id, sum(available_stock) as aggregate')
       ->groupBy('product_id');
}

public function getaAvilableStockAttribute()
{
    if ( ! array_key_exists('availableStock', $this->relations)) {
       $this->load('availableStock');
    }

    $relation = $this->getRelation('availableStock');

    return ($relation) ? $relation->aggregate : null;
}

Then you can do what you asked for:

$products = Product::with('availableStock')->get();
$products->first()->availableStock; // '155' | null

// or simply
Product::first()->availableStock; // '155' | null

Answer 3

OK, thanks for your answer @joseph, now I know I was on a wild goose chase.

Solved the problem with an unattractive foreach

$products = Product::with('variations')->remember(2)->paginate(15);
    foreach ($products as $product) {
        $i = 0;
        foreach ($product->variations as $variation)
        {
            $i = $i + $variation->available_stock;
        }
        unset($product->variations);
        $product->available_stock = $i;
    }