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

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 2

Books::withAvg('ratings', 'rating')->orderBy('ratings_avg_rating', 'desc')->take(5)->get();

this works in Laravel 8