'laravel WhereHas by AVG

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

phplaravel


Solution 1:[1]

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;

source of the avg function

Solution 2:[2]

insted :

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

use this:

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

Solution 3:[3]

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 <=

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stijn Leenknegt
Solution 2 amirhosein hadi
Solution 3 Kareem Elsharkawy

Related Questions