laravel - eloquent - get sum of related model specific column

ciccioassenza picture ciccioassenza · Nov 2, 2014 · Viewed 17.2k times · Source

assuming that I have the table

orders

with fields

id, userId, amount, description

and the table

user

with various fields

how if I wand to get all the users (with all its fields) and also the sum of the "amount" column of the orders related to that user?

assuming that I have:

user:{id:15,firstName:jim,lastName:morrison,gender:male}

and

order:{id:1,userId:15,amount:10,description:"order xxx"},

order:{id:3,userId:15,amount:40,description:"order yyy"}

I would like to receive:

user:{id:15,firstName:jim,lastName:morrison,gender:male,orderAmount:50}

Of course I would like to avoid the foreach statement.

I've setted this on my user model

public function userOrder (){
    return $this->hasMany('Order', 'userId');
}

And I've tryed this:

return $this->hasMany('Order', 'userId')->sum('amount');

without any luck...

Answer

Kristo picture Kristo · Nov 2, 2014

Some thaughts and hopefully an answer to your question:

I would rename the user table to users to stick to laravel conventions. http://laravel.com/docs/4.2/eloquent#basic-usage

I would name the method in the User model orders

public function orders()
{
    return $this->hasMany('Order', 'userId');
}

To query a user, his orders and sum afterwards his orders amount values:

$userdata = User::with( 'orders' )->where( 'userId', 15 )->first();
$sum = $userdata[ 'orders' ]->sum( 'amount' );