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

i have 1 many (inverse) relation on laravel 5.4 application. there 2 models sale , vehicle related , associated scene.

the relation on sale model :

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 want is, sum of field 'vehicles.volume' of sale records within search conditions.

i have tried methods 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 resulted in following error:

sqlstate[42s22]: column not found: 1054 unknown column 'vehicles.volume' in 'field list' (sql: select sum(vehicles.volume) aggregate sales status = 1 , date_time < "2017-05-10 10:10:05" , exists (select * vehicles sales.vehicle_id = vehicles.id))

hopefully waiting solution 'get sum of volume of sales' using eloquent query.

• edited

you need use join before summing 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) total_volume');