How to sum a pivot table column in model?

@w0nk1 Describe exactly what you want to achieve.

From the code I think this is what he wants to do: in the Drink model, he has a many-to-many relation with the Ingredient model, of which the amount needed for this drink is saved in to the pivot data field 'milliliters'. I guess he wants to calculate this total number of milliliters for this drink.

I guess this is similar to the relation between orders and products in a web shop; the amount of products ordered is stored in the pivot table and one might like to calculate the total number of products.

Yeah right! I want a new calculated value for "milliliters". So I just wanna sum it up in the model.

@w0nk1 You don't care about the ingredients, but only total volume, right?

public function getVolume()
{
    return $this->ingredients()->sum('milimeters');
    // in case you have milimeters in the related table as well, use prefix:
    return $this->ingredients()->sum('ingredients_relations.milimeters');
}

@JeroenVanOort I know :) Here's the thing: http://xyproblem.info/

And what if someone would want to eager load this? I've made something like this before:

public function volumeRelation()
{
    $this->ingredients()
        ->selectRaw('drink_id, count(*) as count')
        ->groupBy('drink_id');
}

public function getVolumeAttribute()
{
    return $this->volumeRelation->first() ? $this->volumeRelation->first()->count : 0;
}

But I don't know if this is the most elegant solution one could use.

Sorry for the confusion. You are right, the explanation wasn't the best. Sorry for that! Let me try to explain it in detail:

I'm developing a beverage recipe application with following tables (which are important for my question):

        Schema::create('drinks', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('name', 25);
            $table->string('description');
            $table->string('pre_steps', 500)->nullable();
            $table->string('post_steps', 500)->nullable();
            $table->string('picture_url', 25)->nullable();
            $table->integer('id_category')->unsigned();
            $table->tinyInteger('active')->default(1);
        });

        Schema::create('ingredients', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('name', 25);
            $table->smallInteger('time_per_milliliter');
            $table->tinyInteger('active')->default(1);
        });

        Schema::create('ingredients_relations', function(Blueprint $table)
        {
            $table->integer('id_drink')->unsigned();
            $table->integer('id_ingredient')->unsigned();
            $table->tinyInteger('milliliters');
        });

So if I need a output a drink I need the following things:

• id of the drink
• name of the drink
• description of the drink
• ingredients of the drink with the amount of ml for that specific drink
• the complete amount of all needed ingredients

It's all fine just the last thing with the overall amount I couldn't accomplish.

<?php namespace mixuino;

use Illuminate\Database\Eloquent\Model;

class Drink extends Model {

    public $timestamps = false;
    protected $fillable = array('name', 'description', 'pre_steps', 'post_steps', 'picture_url', 'id_category', 'active');

    public function categories() {
        return $this->hasMany('mixuino\Category');
    }

    public function ingredients() {
        return $this->belongsToMany('mixuino\Ingredient', 'ingredients_relations', 'id_drink', 'id_ingredient')->withPivot('milliliters')->orderBy('pivot_milliliters', 'desc'); // get the complete calculated milliliters value here?
    }

}

So it would be great to get it right of the box with:

Drink::with('ingredients')->get();

@w0nk1 Usually I would suggest what @JeroenVanOort showed above.

You need to (eager) load it as a separate relation. Here's how and when to use such solution http://softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently/ (example is for hasMany, but it doesn't really matter).

In this very case it seems to me, that you need all the data, not only the volume, so I suggest something else. Also to show different way of how eloquent can help you:

namespace Your\Namespace;

use Illuminate\Database\Eloquent\Collection;

class Ingredients extends Collection
{
    public function getTotalVolume()
    {
        return $this->reduce(function ($ml, $ingredient) {
            // we need to ensure ingredient was loaded in relation context
            $current = $ingredient->pivot ? $ingredient->pivot->mililiters : 0;

            return $ml + $current;
        });
    }
}

Next, let's make the Ingredient model use that collection:

public function newCollection(array $models = [])
{
    return new Ingredients($models);
}

Then you can do simply this:

$drink->ingredients->getTotalVolume();

@JarekTkaczyk thanks for your fast answer! ;)

Could you tell me where I've to put exactly your snippets? I tried some stuff out, but it haven't worked out. This is my ingredient model:

´´´ namespace mixuino; use Illuminate\Database\Eloquent\Model;

class Ingredient extends Model {

public $timestamps = false;
protected $fillable = array('name', 'time_per_milliliter', 'active');


public function drinks() {
    return $this->belongsToMany('mixuino\Drink', 'ingredients_relations', 'id_ingredient', 'id_drink')->withPivot('milliliters');
}

}

´´´

Thank you for your help! I'm totally stuck ;-/

@w0nk1 newCollection goes to the model, that should utilize this collection, so your Ingredient model. And the collection itself anywhere you see it in your project.

@JarekTkaczyk

´´´ class Ingredient extends Model {

public $timestamps = false;
protected $fillable = array('name', 'time_per_milliliter', 'active');


public function drinks() {
    return $this->belongsToMany('mixuino\Drink', 'ingredients_relations', 'id_ingredient', 'id_drink')->withPivot('milliliters');
}

public function newCollection($models = [])
{
    return new Ingredient($models);
}

} ´´´

I tried it like this but getting this error:

ErrorException in Ingredient.php line 21: Declaration of mixuino\Ingredient::newCollection() should be compatible with Illuminate\Database\Eloquent\Model::newCollection(array $models = Array)

Have I done a mistake?

@w0nk1 It was Ingredients, you may call it IngredientCollection for verbosity, or whatever suits you.

@JarekTkaczyk

´´´

use Illuminate\Database\Eloquent\Model; use Illuminate\Database\Eloquent\Collection;

class Ingredient extends Model {

public $timestamps = false;
protected $fillable = array('name', 'time_per_milliliter', 'active');


public function drinks() {
    return $this->belongsToMany('mixuino\Drink', 'ingredients_relations', 'id_ingredient', 'id_drink')->withPivot('milliliters');
}

public function newCollection($models = [])
{
    return new IngredientCollection($models);
}

}

´´´

same Error :-(

@w0nk1 You must typehint array:

public function newCollection(array $models = [])
{
    return new IngredientCollection($models);
}

@JarekTkaczyk

I very appreciate your help, but I running into an Error:

    public function newCollection(array $models = [])
    {
        return new IngredientCollection($models);
    }

FatalErrorException in Ingredient.php line 19: Class 'mixuino\IngredientCollection' not found

@w0nk1 Well, did you create that collection class? Do you reference its namespace correctly? The error is pretty clear.

@JarekTkaczyk thank you very much! That works like a charme.

But one more question, can I combine this output within this array ?

        return view('drinks.index')->with(
            [
                'categories' => Category::all(),
                'drinks'     => Drink::with('ingredients')->get() // how can I merge this output with the "getTotalVolume()"?
            ]
        );

@w0nk1 It should be already there. The thing is, whenever ingredients collection is created, it picks up your custom collection class, so if you defined it as above, then this will be true in your view:

@foreach ($drinks as $drink) 
  $drink->ingredients->getTotalVolume();
@endforeach

Now, another thing you can do is creating an accessor and calling it directly on the Drink, instead of the collection. This way you can easily append this value to the array/json representation:

// Drink model
public function getTotalVolumeAttribute()
{
    return $this->ingredients->getTotalVolume();
}

// then you can call directly
$drink->totalVolume

@JarekTkaczyk works perfect!

ATM I put the getTotalVolume() class collection in the app.php. What would be a good place to put those collection classes?

Thanks a lot!

I hope this going to help if problem not solved yet first define relationship like this

public function ingredients() {
        return $this->belongsToMany('mixuino\Ingredient', 'ingredients_relations', 'id_drink', 'id_ingredient')->withPivot('milliliters');
}

and then do this:

$drink->ingredients->sum("pivot.milliliters");