Laravel: Selecting Items with WHERE condition is in joined table

Question

I have two tables (films and reviews). One Film HasMany Reviews and one Review BelongsTo one Film

Relations: Film.php

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

and Review.php

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

Since I am using Laravel Eloquent relations, I want to build a list of films ordered by reviews using Eloquent. Reviews go from 0 = non rated, to 5 = maximum rating.

In plain English I need:

• join the two tables
• get all the films that has reviews
• group by film_id
• calculate the average (one film could have many reviews so I need the average)
• organize the resulting list
• get the list

My solutions using the Query Builder get an error:

$best_films = DB::table('films')
             ->join('reviews', 'reviews.film_id', '=', 'films.id')
             ->select('films.id', 'avg(reviews.rating)')
             ->groupBy('films.id')
             ->orderByRaw('avg(reviews.rating) DESC')
             ->get();

With this I get the following error: Column not found: 1054 Unknown column 'avg(reviews.rating)' ...

Using Eloquent ORM I can not order by columns in oteher table. Something like this does not work:

 $films = Film::whereHas('reviews')->orderBy('reviews.film_id', 'desc')->get();

I get the error: Column not found: 1054 Unknown column 'reviews.film_id' in 'order clause'...

My Question: Any idea how to solve that problem? (I would prefer using Eloquent Orm)

Answer 1

You should try this

best_films = DB::table('films')
             ->join('reviews', 'reviews.film_id', '=', 'films.id')
             ->select('films.id', DB::raw('avg(reviews.rating)'))
             ->groupBy('films.id')
             ->orderByRaw('avg(reviews.rating) DESC')
             ->get();

Answer 2

I think you have to short data in your relational model like this

public function reviews() { return $this->hasMany('App\Review')->orderBy('film_id', 'desc'); }