How to get average with orderBy Desc in Laravel 5

Question

I have 2 tables in my database.

books and ratings

in books id, name

in ratings

id, book_id, rating

i have set has many relationship for these models.

So in Book model -

public function ratings()
{
    return $this->hasMany('App\Rating');
}

in Rating Model -

public function book()
{
    return $this->belongsTo('App\Book');
}

Now i want to fetch all books with there average rating but order by high rating.

so i high rated books first and then low ratings.

So How i can join 2 tables to achieve this result.

Thanks.

Answer 1

That query would look something like this:

SELECT book.id, AVG(ratings.rating) AS avg_rating 
FROM ratings
JOIN book ON book.id = ratings.book_id
/* WHERE AVG(ratings.rating) > 4 You could say only return results where rating > 4 */
GROUP BY book.id
ORDER BY AVG(ratings.rating) DESC LIMIT 5;

You need to join the two tables, use an aggregate function with a group by on the book. Then just sort and limit the results.

UPDATE:

In response to your question:

SELECT book.id, COALESCE(AVG(ratings.rating), 0) AS avg_rating 
FROM book
*LEFT* JOIN ratings ON book.id = ratings.book_id    
GROUP BY book.id
ORDER BY AVG(ratings.rating);

Use of a view might be something of a compromise between ease of the ORM and sanity in your querying.

Answer 2

You can use a modified withCount():

$books = Book::withCount(['ratings as average_rating' => function($query) {
    $query->select(DB::raw('coalesce(avg(rating),0)'));
}])->orderByDesc('average_rating')->get();

Answer 3

Via Collections*

    $books = Book::with('ratings')
        ->get()
        ->sortBy(function($bk, $key) {
            if($bk->rating) {
                return $bk->rating->rating;
            }
            return null;
        });

Via Joins

  $books = Book::join('rating', 'rating.book_id', '=', 'book.id')
                 ->orderBy('ratings.rating', 'desc')
                 ->select('books.*')
                 ->get();