DB::table( 'articles' )
->join( 'ratings', 'ratings.article_id', '=', 'articles.id' )
->where( 'articles.state', 1 )
->groupBy( 'articles.id' )
->select( 'articles.id', DB::raw( 'AVG( ratings.rank )' ) )
->get();
Last updated
2 years ago.
Thanks for your answer, but I would like to stick with Eloquent and not Fluent, as I have 4 relations on my Article model so it's easier to use. But maybe eloquent is not made for that ?
Last updated
2 years ago.
The problem is that you need to use group by in order to use avg(), and I believe that there is no way to do queries with Fluent that uses aggregate functions that needs other tables to work.
Last updated
2 years ago.
Solution
Ok I found the solution, by using Eloquent + Fluent, to be able to use my relationships. This is my code to get a collection of all my Articles + User + Rating average :
$articles = Articles::where('state', '=', '1')
->with('user')
->leftJoin('ratings', 'ratings.article_id', '=', 'articles.id')
->select(array('articles.*',
DB::raw('AVG(rating) as ratings_average')
))
->groupBy('id')
->orderBy('ratings_average', 'DESC')
->get();
Last updated
2 years ago.
Sign in to participate in this thread!
Laravel
Laravel News
Laracasts
Laravel Podcast