Display Avarage of rating with Query Builder? | Laravel 5.6

Hi, I have a query and I need average rating number (ex: 5, 3, 2 etc..) of single article. Now I have two table posts and ratings, that I get from a simple Eloquent query Post::where('id', '=', '1')->first(); This articles can have multiple ratings, that are stored in an other table. I need to get the average of ratings. So my query is now like this:


public function view_post($post_id)    
  { 
    $resume = DB::table('posts')
    ->leftJoin('post_categories', 'posts.cat_id', '=', 'post_categories.id')
    ->leftJoin('location', 'posts.location_id', '=', 'location.id')
    ->leftJoin('user_reviews', 'posts.id', '=', 'user_reviews.post_id')
    ->select('posts.*', 'location.state_name','location.state_flag', 'post_categories.category_name', DB::raw( 'AVG( user_reviews.rating )' ))
    //->groupBy('posts.id')
    ->where(array('posts.id'=>$post_id))->first();

I can't use groupBy (If I use and after run I have error)... How can I do it?Thanks!

m7vm7v

7 years ago

Level 51

I would advice you to makea relationship between your tables - like

Article.php

public function ratings(){
    return $this->hasMany(Rating::class); //or hasManyTrough as I see you might be having the ratings on post, not the article itself
}

so then you could grab a really nice collection by $article->ratings->avg('the_field_from_the_rating_table_with_the_number_value');

1 like

Please or to participate in this conversation.