Crazy Horse
asked on
Logic behind "best rated" calculation
I want to to list the best rated items in order of best rated but am trying to figure out the logic behind it.
If you have a product with only 2 ratings and both are 5 star ratings, does that in fact make it better than another product which has 100 ratings of which say 90 are 5 star and the rest are 4 star?
2/2 is obviously 100% 5 star rating and 90/100 is only 90% 5 star rating but there are 100 reviews as opposed to 2 so you would think that the 90/100 5 star rating should be ranked higher than the 2 x 5 star ratings.
Is this logic correct or am I confusing myself? ;-)
And would you use SQL or PHP to calculate this?
If you have a product with only 2 ratings and both are 5 star ratings, does that in fact make it better than another product which has 100 ratings of which say 90 are 5 star and the rest are 4 star?
2/2 is obviously 100% 5 star rating and 90/100 is only 90% 5 star rating but there are 100 reviews as opposed to 2 so you would think that the 90/100 5 star rating should be ranked higher than the 2 x 5 star ratings.
Is this logic correct or am I confusing myself? ;-)
And would you use SQL or PHP to calculate this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
example image from the article:
Right on. Here's the ASKER
Thanks for the great answers guys. I have tried to use SQL to do the most of the work for me. I don't have much dummy data to test with but this seems to work as far as I can see. Do you think this is a viable solution?
Just a quick textual output from this:
gives me
75 : number of reviews = 3 ; avg rating = 3.0000 /5
76 : number of reviews = 2 ; avg rating = 4.0000 /5
Obviously I will join my products table to get a product name instead of using the ID.
SELECT COUNT(`review_id`) as no_of_reviews, `review_rating`, `product_id`, AVG(`review_rating`) as avg FROM `reviews` GROUP BY `product_id
Just a quick textual output from this:
echo $prod_id . " : " . " number of reviews = " . $no_of_reviews . " ; avg rating = " . $avg . " /5 <br />";
gives me
75 : number of reviews = 3 ; avg rating = 3.0000 /5
76 : number of reviews = 2 ; avg rating = 4.0000 /5
Obviously I will join my products table to get a product name instead of using the ID.
Yes, that looks fine to me. I can't really follow the values from the query results set into the echo statement, but the concept seems right.
ASKER
And now my least favorite thing about EE... having to select a best answer. Both are great answers which are pretty similar.
Can't I mark 2 answers as the best solution?
Or do I have to resort to flipping a coin or closing my eyes and clicking at random?
Can't I mark 2 answers as the best solution?
Or do I have to resort to flipping a coin or closing my eyes and clicking at random?
E-E has been tinkering with the ways of closing a question. You might want to hit "Request Attention" and bring this issue up to a moderator. I'm not sure it will do any good, but if we don't raise issues like this, the people in San Luis Obispo will never know about them.
ASKER
Yeah, good idea. I am going to try that. I think they should let you choose more than one answer as the best. Sometimes there are 2 or more answers of equal value.
My suggestion:
Mark Ray's first answer as best and if you feel my comment needs to be included then that can be the assist.
Mark Ray's first answer as best and if you feel my comment needs to be included then that can be the assist.
ASKER
Hi Julian,
Thanks for the suggestion. I have done what Ray suggested and requested attention because I really do think that EE should let users choose 2 users as the best answer. There have been a good few times when I have wanted to do this but can't.
Thanks for the suggestion. I have done what Ray suggested and requested attention because I really do think that EE should let users choose 2 users as the best answer. There have been a good few times when I have wanted to do this but can't.
ASKER
A moderator got back to me and requested that I post my concern in what looks like the "bugs" section. I have done so but not sure if anything will happen or when. I don't want to leave this question open unnecessarily so I will go according To Julian's suggestion above. Thanks!