0
<?php

$query = $this->restaurant->leftJoin('reviews', 'reviews.restaurant_id', '=', 'restaurants.id');
$query = $user_id == null ? $query : $query->where("user_id", $user_id);

$query = $query->select('restaurants.*')
    ->selectRaw('ROUND(AVG(reviews.rating), 1) as average_rating')
    ->groupBy('restaurants.id')
    ->havingRaw('AVG(reviews.rating) >= ?', [$request->from_rating])
    ->havingRaw('AVG(reviews.rating) <= ?', [$request->to_rating])
    ->orderByDesc("average_rating")
    ->paginate(
        $this->perPage,
        ['id', 'name', 'image', 'description', 'filesystem', 'created_at'],
        'page',
        $request->page
    );

return $query;

My query looks like this. I have two tables: Restaurants and Reviews. The Reviews table stores restaurant_id. The problem occurs when we have, for example, 10 restaurants and we have 1 Review only for one of the Restaurants. After running this query, only 1 restaurant is returned, because I use havingRaw which filters Restaurants according to the average rating. So if a Restaurant doesn't have any rating, that Restaurant doesn't get returned. How do I make that so all Restaurants still return, but their average rating becomes 0?

Karl Hill
  • 12,937
  • 5
  • 58
  • 95
Nika Kurashvili
  • 6,006
  • 8
  • 57
  • 123
  • This all might be a lot easier if you use Eloquent and do things the Laravel way? [Here's an example doing something similar](https://stackoverflow.com/a/27702074/6089612) which seems pretty neat, maybe it helps. The linked blog post clarifies what he's doing and is worth a read. – Don't Panic Mar 30 '19 at 08:31
  • Alternatively, don't do the rating filtering in your query (remove the `havingRaw()`s), and instead [filter the returned collection](https://laravel.com/docs/5.8/collections#method-filter)? Might not be efficient if are working with huge result sets. – Don't Panic Mar 30 '19 at 09:41
  • The problem is I make it work as you suggested, but I also want to filter by avg rating. your link didn't include that feature and that's why i can't make it work either. – Nika Kurashvili Mar 30 '19 at 10:01
  • Sure you can - `Restaurant::with('avgRating')->get()->filter();` – Don't Panic Mar 30 '19 at 10:54
  • i hate filter() function. it works on collections and is slower than database queries. – Nika Kurashvili Mar 30 '19 at 10:58

1 Answers1

0

you can use CASE WHEN to populate mysql with more specific condition. Also you may change the datatype of 'rating' table to int(5), in this case all the empty value will be shown as 0. So that will go as you want according to your question.

iamawesome
  • 652
  • 7
  • 15