How to aggregate query on related table's field in Laravel?

Question

I have a One To Many (Inverse) relation on my laravel 5.4 application. There are two models Sale and Vehicle which are related and associated with the scene.

The relation on the Sale model is :

public function vehicle()
    {
        return $this->belongsTo('App\Models\Vehicle','vehicle_id');
    }

Table sales has following fields : id, vehicle_id, date_time, status etc.

Table vehicles has following fields : id, reg_number, volume, status etc.

What I want is, sum of the field 'vehicles.volume' of Sale records within the search conditions.

I have tried some methods like the following one:

$query = Sale::where('status', 1);
$query = $query->where('date_time', '<', "2017-05-10 10:10:05");
$totalVolume  = $query->whereHas('vehicle')->sum('vehicles.volume');

and it resulted in the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'vehicles.volume' in 'field list' (SQL: select sum(vehicles.volume) as aggregate from sales where status = 1 and date_time < "2017-05-10 10:10:05" and exists (select * from vehicles where sales.vehicle_id = vehicles.id))

hopefully waiting for a solution to 'get the sum of the volume of the sales' using eloquent query.

Edited

I think you should start off by just writing out the raw MySQL query which you would run if you were using Workbench directly. Then, based on that, build the Laravel query. If you can't write the MySQL query then you're probably not ready to attack the Laravel side of things. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 14, 2017 at 5:30

Martin Mogusu · Accepted Answer · 2017-09-14 13:11:54Z

1

You need to use a join before summing the vehicles.volume column

$totalVolume = Sale::where('status', 1)
    ->where('date_time', '<', "2017-05-10 10:10:05")
    ->join('vehicles', 'vehicles.id', '=', 'sales.vehicle_id')
    ->select(DB::raw('sum(vehicles.volume) as total_volume');

answered Sep 14, 2017 at 13:11

Martin Mogusu

7757 silver badges7 bronze badges

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

1 Comment

iamab.in Over a year ago

Thank you Martin..

$totalQuantity = $totalQuantityQuery->join('vehicles', 'vehicles.id', '=', 'sales.vehicle_id')->select(\DB::raw('sum(vehicles.volume) as total_volume'))->get()

gives an object inside an array.. extracted the result using.. $totalQuantity[0]->total_volume

RamAnji · Accepted Answer · 2017-09-14 06:05:08Z

0

select sum(volume) as aggregate from vehicles INNER JOIN sales ON vehicles.id=sales.vehicle_id

answered Sep 14, 2017 at 6:05

RamAnji

4703 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to aggregate query on related table's field in Laravel?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related