如何在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

Answer 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');