laravel WhereHas by AVG-CodePudding

I want to return products that has average rating of 3 as example

            ->when($request['product_rating'], function ($query) use ($request) {
                $query->wherehas('reviews', function ($query) use ($request) {
                    $query->where('rating ',3);
                });
            })

this my code here

I have tried

            ->when($request['product_rating'], function ($query) use ($request) {
                $query->wherehas('reviews', function ($query) use ($request) {
                    $query->whereRaw('AVG(rating) >= ' . $request['product_rating']);
                });
            })

but it return error

SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'businessdotkomdb.reviews.id'; this is incompatible with sql_mode=only_full_group_by

CodePudding user response：

whereHas method checks if related models exists.

You can use $query->withAvg('reviews', 'rating'). In you're code you can get the avg value like this I think (I've never used this methode):

$avg = $result->rating_avg;

CodePudding user response：

insted :

 $query->whereRaw('AVG(rating) >= ' . $request['product_rating'];

use this:

 $query->havingRaw('AVG(rating) >= ' . $request['product_rating']

CodePudding user response：

This Worked For me

            ->when($request['product_rating'], function ($query) use ($request) {
                $query->wherehas('reviews', function ($query) use ($request) {
                    $query->selectRaw('reviews.*, avg(stars) as average_rating')
                        ->groupBy('reviewed_id')
                        ->havingRaw('average_rating = ?', [$request['product_rating']]);
                 });
            })

don't Forget to change = to be >= or <=