Laravel 5.2 — проблемы с сортировкой и LEFT JOIN?

Question

[Annywebart]

Привет всем!
Возникли проблемы с join, подскажите, пожалуста.
Необходимо выбрать товары, отсортированные по рейтингу. Рейтинг высчитывается, исходя из отзывов к товару.

$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);

Отзывы могут быть не опубликованы, их учитывать не нужно. Также у отзыва может быть не выставлен рейтинг (тогда это обычный комментарий). Вроде все отлично, но вот если у товара есть неопубликованный отзыв, то этот товар не выбирается.
Подскажите, пожалуйста, в чем может быть проблема?

Answer 1

[Aleksey Ratnikov]

У вас неполный GROUP BY в запросе:

->addSelect(\DB::raw('(SUM(products_reviews.rating) / COUNT(products_reviews.id)) as `rating`'))

Использование аггрегирующих функции без GROUP BY посреди обычного запроса с кучей колонок дает странные результаты (не говоря уже о том, что в СУБД здорового человека так вообще делать нельзя).

UPD: Подзапрос для получения рейтинга, вместо джоина:

->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
'))

Comments

[Annywebart]

Спасибо, дело в том, что я просто пропустила эту строку, когда писала сообщение. GROUP BY есть.
$query->groupBy('products.id');

[Aleksey Ratnikov]

Annywebart: Все равно неполный GROUP BY - вы считаете отношение суммы с количество с группировкой по id и берете еще кучу полей, которые в группировке не участвуют. Вы сделали джоин просто чтобы рейтинг получить? Переделайте эту часть лучше на подзапрос(добавил к ответу).