how to sort outer query based on a internal query field??

hello

I need to sort all products based on their average rating here is my query:

category->products()->whereHas('reviews',function($query){

 $query->groupBy('reviews.productId')->select(DB::raw("avg(rating) as product_rating"));

})->orderBy('product_rating','desc');

the error says that product_rating field doest exists. how can I rewrite this to be able to use product_rating in order by??

select * from products inner join product_categories on products.id = product_categories.productId where product_categories.categoryId = ? and exists (select avg(rating) as product_rating from reviews where products.id = reviews.productId group by reviews.productId) and products.deleted_at is null order by product_rating desc

thanks

Ashraam

6 years ago

Best Answer

Level 41

Try this maybe

DB::table('products')->join('reviews', 'products.id', '=', 'reviews.productId')->where('category_id', $category->id)->selectRaw('products.*, AVG(reviews.rating) as product_rating')->orderByDesc('product_rating')->get();