How to get the sum of another sum in an eloquent Laravel relationship

Question

I have the relations user->sales->detail_sales, a user has one or many sales, a sale has one or more details. What I'm trying to do is obtain with ELOQUENT, the total of all sales per user, that is, make a sum(totaldetails) of the calculated total with withCount.

Sale.php

public function detail()
{
    return $this->hasMany(Detail:class);
}


public function user()
{
    return $this->belongsTo('App\User');
}

DetailSale

public function sale()
{
    return $this->belongsTo(Sale::class);
}

User

public function sales()
{
    return $this->hasMany(Sale::class );
}

This query I currently have

return App\User::with(['sales' => function($query){
    // get total for detail_sales for sale
    $query->withCount(['detail as totaldetails' => function($que){
        $que->select(DB::raw('sum(subtotal)'));
    }]);
}])->get();

but I want that in the User model, to have a property like salestotal for example, is it possible?

@foreach($users as $user)
  {{ $user->salestotal}}
@endforeach

Use as like $que->select(DB::raw('sum(subtotal) as salestotal')); — Niklesh Raut
– Niklesh Raut, Commented May 1, 2019 at 2:03
@NikleshRaut What I need is the sum of all the values returned by sum (subtotal) ie sum (totaldetails) and have it as property in the parent model User. — DarkFenix
– DarkFenix, Commented May 1, 2019 at 2:06
@JonasStaudenmeir I have added the relations, I hope you understand what I want to obtain, if not comment and provide necessary information — DarkFenix
– DarkFenix, Commented May 1, 2019 at 2:14
where does the subtotal column lies, in the sales table or in the details table? could you share the tables structure with us please. — Tharaka Dilshan
– Tharaka Dilshan, Commented May 1, 2019 at 6:37

IGP · Accepted Answer · 2019-05-01 04:35:15Z

2

Why not just use the sum() function since it's all in a Collection?

@foreach($users as $user)
    {{ $user->sales->sum('totaldetails') }}
@endforeach

answered May 1, 2019 at 4:35

IGP

16.4k5 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jonas Staudenmeir · Accepted Answer · 2019-05-01 13:48:20Z

1

You can use withCount() with a HasManyThrough relationship:

class User extends Model
{
    public function details()
    {
        return $this->hasManyThrough(Detail:class, Sale::class);
    }
}

return App\User::withCount(['details as salestotal' => function($query) {
    $query->select(DB::raw('sum(subtotal)'));
}])->get();

@foreach($users as $user)
    {{ $user->salestotal }}
@endforeach

answered May 1, 2019 at 13:48

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

Comments

Ghanshyam Nakiya · Accepted Answer · 2019-05-01 06:57:17Z

0

I hope this helps:

    return App\User::with(['sales' => function($query){
     // get total for detail_sales for sale
        $query->withCount(['detail as totaldetails' => function($que){
       $que->select(DB::raw("SUM(subtotal) as salestotal"));
       }]);
    }])->get();

answered May 1, 2019 at 6:57

Ghanshyam Nakiya

1,71221 silver badges24 bronze badges

1 Comment

DarkFenix Over a year ago

Thank you for responding, but there is no point in copying the code of my question as an answer, I'm not looking for something I already have.

Collectives™ on Stack Overflow

How to get the sum of another sum in an eloquent Laravel relationship

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related