Laravel 5.2 - Sorting and LEFT JOIN issues?

Question

MySQL

Hi all!
There were problems with join, tell me, please.
You must select products sorted by rating. The rating is calculated based on the reviews for the product.

$query = Product::where('products.is_published', '=', 1)
            ->where('products.published_at', '<=', Carbon::now())
            ->with('category')
             ->leftJoin('products_reviews', 'products_reviews.product_id', '=', 'products.id')
                ->where(function($q) {
                    $q->where(function ($qu) {
                        $qu->where('products_reviews.is_published', '=', 1)
                            ->where('products_reviews.rating', '!=', 0);
                    })->orWhere('products_reviews.id', '=', null);
                })
                ->addSelect(\DB::raw('(SUM(products_reviews.rating) / COUNT(products_reviews.id)) as `rating`'))
                ->groupBy('products.id')
                ->query->orderBy('rating', 'DESC');
$products = $query->paginate($limit);

Reviews may not be published, they do not need to be taken into account. Also, the review may not have a rating (in this case it is a normal comment). Everything seems to be fine, but if a product has an unpublished review, then this product is not selected.
Tell me, please, what could be the problem?

Answer 1

You have an incomplete GROUP BY in your query:
Using aggregating functions without GROUP BY in the middle of a regular query with a bunch of columns gives strange results (not to mention the fact that you can’t do this at all in a DBMS of a healthy person).
UPD: Subquery for getting rating instead of join:

->addSelect(\DB::raw('
  SELECT
    (SUM(products_reviews.rating) / COUNT(products_reviews.id)) AS `rating`
  FROM products_reviews
  WHERE
    products_reviews.product_id = products.id
    AND products_reviews.is_published = 1
    AND products_reviews.rating != 1
'))