获取JOIN查询中的平均值，在原始MySql中工作但在Laravel查询构建器中不起作用

Question

I got this query written out in Laravel query builder, bu when i run it, it returns this error: Call to a member function join() on float

$avgYield= DairyCropYield::avg('moisture')
            ->join('dairy_crops','dairy_crops.id','=','dairy_crop_yields.dairy_crop_id')
            ->join('dairy_crop_varieties','dairy_crops.dairy_crop_variety_id','=','dairy_crop_varieties.id')
            ->where('dairy_crop_varieties.crop','=',$crop->crop)
            ->whereYear('harvested_at','=',Carbon::now()->year)
            ->get();

But when i write the same query in mySql it wroks as exptected:

SELECT AVG(moisture) FROM dairy_crop_yields AS yield JOIN dairy_crops AS crop ON crop.id = yield.dairy_crop_id JOIN dairy_crop_varieties AS variety ON variety.id = crop.dairy_crop_variety_id WHERE variety.crop = 'corn' AND YEAR(harvested_at) = 2015

Any thoughts / suggestions on what I am not doing right in Laravel query builder. I do want to master and stick with Laravel's query builder. Thanks.

Answer 1

Put the avg after the get(), the avg method works on a collection. So what you want is to get() all the result first then only call the avg('moisture') on the available results.

$avgYield= DairyCropYield::join('dairy_crops','dairy_crops.id','=','dairy_crop_yields.dairy_crop_id')
        ->join('dairy_crop_varieties','dairy_crops.dairy_crop_variety_id','=','dairy_crop_varieties.id')
        ->where('dairy_crop_varieties.crop','=',$crop->crop)
        ->whereYear('harvested_at','=',Carbon::now()->year)
        ->get()
        ->avg('moisture');

Alternately, you can also do something like this using DB::raw

$avgYield= DairyCropYield::select(DB::raw('AVG(moisture)'))
    ->join('dairy_crops','dairy_crops.id','=','dairy_crop_yields.dairy_crop_id')
    ->join('dairy_crop_varieties','dairy_crops.dairy_crop_variety_id','=','dairy_crop_varieties.id')
    ->where('dairy_crop_varieties.crop','=',$crop->crop)
    ->whereYear('harvested_at','=',Carbon::now()->year)
    ->get();