0

I have a table places and table rating. Every place have multiple rows in rating table. I want to get all places and every place object should have one property called user_rating. Its value is the average of rating.rate_no for this place.If there is no ratings for this place (no rows in rating table) user_rating should be 0 My code is:

$data = DB::table('places')
    ->join('rating', 'places.id', '=', 'rating.place_id', 'left outer')
    ->select(
        'places.*',
        'rating.rate_no',
        DB::raw('avg(rating.rate_no) as user_rating')
    )
    ->groupBy('rating.rate_no')
    ->get();

This code group all places in one object with avarage(rating) which I do not want.

Moauya Meghari
  • 471
  • 2
  • 6
  • 23
  • 3
    Possible duplicate of [Laravel Eloquent groupBy() AND also return count of each group](http://stackoverflow.com/questions/18533080/laravel-eloquent-groupby-and-also-return-count-of-each-group) – Shadow Apr 12 '16 at 12:08
  • Just apply avg() instead of count() as shown in the duplicate topic. – Shadow Apr 12 '16 at 12:08
  • @Shadow it did not work , i will put the **new code** in the next comment,help me if you can . – Moauya Meghari Apr 12 '16 at 12:19
  • $data = DB::table('places') ->join('rating', 'places.id', '=', 'rating.place_id', 'left outer') ->select( 'places.*', 'rating.rating_no', DB::raw('avg(rating.rating_no) as user_rating')) ->groupBy('rating.rating_no') ->get(); – Moauya Meghari Apr 12 '16 at 12:20
  • The did not work is not a proper error description, pls provide details as to what exactly that means. Btw, the solution described there does work, so you probably implemented it incorrectly. – Shadow Apr 12 '16 at 12:24
  • the result became 9 objects with user_rating from 2 - 10 .there is 4000 place object in database – Moauya Meghari Apr 12 '16 at 12:28
  • Use leftJoin method instead of join. – Shadow Apr 12 '16 at 12:36
  • I already use left outer join to get all places even if its not have ratings in raing table. – Moauya Meghari Apr 12 '16 at 12:41
  • Then you would have a record for all 4k places. – Shadow Apr 12 '16 at 12:41
  • no i would not. I want every single place of the 4K places in a single object with its data beside this data i want the avarage of its rating from the rating table – Moauya Meghari Apr 12 '16 at 12:45

0 Answers0