CODEWITHSUNDEEP
mysqllaravel
eylay
eylay

Reputation: 2167

laravel join with average of a column in another table

I have a Food model and Review model. in my reviews table in database I have two columns : food_id and rate. Each user can rate foods and rate is an integer number between 1~5.

I need to get all records in foods table and average of rate column in reviews where food_id is the id of each food.

$foods = Food::join('reviews', 'foods.id', '=', 'reviews.food_id')->select(
            'foods.*',
            'AVERAGE(reviews.rate) WHERE reviews.food_id = foods.id AS food_rate'
)->get();

And of course this code will raise SQLSTATE[42000]: Syntax error

How can I join with average of a column in another table?

SELECT  foods.*, AVG(reviews.rate) AS food_rate
    FROM foods
        LEFT OUTER JOIN reviews
            ON reviews.food_id = foods.id
    GROUP BY foods.id

in fact this MYSQL code needs to be written with Laravel

Upvotes: 0

Views: 1277

Answers (1)

eylay
eylay

Reputation: 2167

$foods = Food::select(
            '*',
            \DB::raw('AVG(reviews.rate) AS food_rate'),
        )->leftJoin('reviews', 'foods.id', '=', 'reviews.food_id')->groupBy('foods.id');

and we need to set 'strict' => false in config/database.php

Upvotes: 1

Related Questions