Level 24
Your question doesn't really specify how you want to determine the top products. By the average rating?
1 like
Aug 6, 2018
10
Level 3
Get top review products?
I have problem with getting the top selling products.
I have 2 tables, products, and product_reviews
- products(id, name, price)
- product_reviews(id, product_id, rating)
a product can has many product_reviews
==============================
product
==============================
id | Name | price
----------------------------------------------
1 | Book . | 100
2 | Sock | 5000
3 | Strape | 9000
==============================
product_reviews
==============================
id | product_id | rating
---------------------------------------------
1 | 1 | 4
2 | 1 | 5
3 | 2 | 5
4 | 1 | 3
5 | 2 | 4
6 | 1 | 5
7 | 3 | 4
As you can see, the product with id 1 probaby has the most review and good review also.
How do I use eloquent to get the result?
Level 67
ProductReview::select('product_id')
->with('product')
->selectRaw('sum(rating) as rating')
->groupBy('product_id')
->orderBy('rating', 'desc')
->take(5)
->get();
So, that should get the top 5 product_reviews as determined by the total of their rating, along with the associated product.
If you want to get it by the average rating (instead of total), just change "sum" to "avg" in the selectRaw.
1 like
Level 3
@staudenmeir Oh, I didn't think about that. What type of rating do you have in mind?
@Cronix I'm not fully understand about the with() and the selectRaw() part. Can we chain selectRaw after already specify select and after the eager loading?
Level 67
I don't know what you mean. The order of the things doesn't matter (except the get()). It will all be parsed down to the same 2 queries.
Just try it. I used your data when I was testing.
When using selectRaw('sum(rating) as rating') (total of all ratings for each product)
all: [
App\Vote {#787
product_id: 1,
rating: "17",
},
App\Vote {#796
product_id: 2,
rating: "9",
},
App\Vote {#785
product_id: 3,
rating: "4",
},
],
when using selectRaw('avg(rating) as rating') (average of all ratings for each product)
all: [
App\Vote {#781
product_id: 2,
rating: "4.5000",
},
App\Vote {#791
product_id: 1,
rating: "4.2500",
},
App\Vote {#804
product_id: 3,
rating: "4.0000",
},
],
Not sure which one you want.
However, neither of these give you getting the top selling products.. That would be determined by the quantity of items sold, not their ratings.
1 like
Level 3
@Cronix I already get it working but just asking because I didn't know that the order doesn't matter except the get(). Also, I have Top Review Product, Top Sell Product. Getting this to work will also get the other one to work.
Anyway, is it possible to get only the product column?
Level 67
Do you actually need them by themselves? You can always just iterate over the reviews and access the product properties so it would really be the same thing.
$reviews = ProductReview::select('product_id')
->with('product')
->selectRaw('sum(rating) as rating')
->groupBy('product_id')
->orderBy('rating', 'desc')
->take(5)
->get();
@foreach ($reviews as $review)
{{ $review->product->name }}
@endforeach
If you really need them isolated, just create an array of the products.
$products = [];
foreach ($reviews as $review) {
$products[] = $review->product;
}
then pass products to the view (or whatever you're doing)
@foreach ($products as $product)
{{ $product->name }}
@endforeach
1 like
Level 1
using groupBY() in your query. I think you can solve your problem
1 like
Level 3
@Cronix I thought that there would be a way without iterating. Alright cool! Have a nice day.
Level 67
@GTHell If it's working for you, please mark as solved.
1 like
Level 3
@Cronix you mean choose best answer?
Please or to participate in this conversation.