(Pages) Ratings and reviews - sorting issue

[Sonya*]

There is a bit of mess with ratings and reviews in databases and sorting by Highest rated.

We have 5 fields in tables cms_custom_database_X related to reviews and rating:

• rating_real
• rating_hits
• rating_value
• rating_reviews
• record_rating

Depending on what we allow on database, they are filled or not.

Case 1: Database allows stars rating (no reviews)

• rating_real -> not used
• rating_hits -> number of votes
• rating_value -> total stars (e. g. 1x4+2x5 = 14)
• rating_reviews -> not used
• record_rating -> average number of stars

Case 2: Database allows reviews (no star rating)

• rating_real -> not used
• rating_hits -> not used
• rating_value  -> not used
• rating_reviews -> number of reviews
• record_rating -> average number of stars

This is a database column mapping from applications/cms/Application.php:

'rating' => 'record_rating',
'rating_hits' => 'rating_hits',
'rating_average' => 'record_rating',
'rating_total' => 'rating_value',
'num_reviews' => 'record_reviews',

It seems you use the value mapped into rating_average to sort for Highest Rating, that's why you have odd results like this. Rating_average does not consider the number of votes/reviews.

Possible solutions

Solution #1 can be to use both columns record_average AND num_reviews or rating_hits. This would result in more accurate rating like:

1. 3x5 stars (average 5 with 3 votes) 
2. 2x5 stars (average 5 with 2 votes)
3. 5x4+6x5 (average 4 with 11 votes)
4. 10x4 stars (average 4 with 10 votes)
5. 1x4+2x5 (average 4 with 3 votes)
6. 1x4 stars (average 4 with 1 vote)

Solution #2 can be to calculate ratings based on total stars and votes https://calculator.academy/average-rating-calculator-star-rating/ You already calculate the total rating by star ratings in rating_value. Just do the same for reviews. Then divide the total number of stars by 5 or 10 (depending on how the rating system is configured in AdminCP). Save the result in rating_real (the unused column) and sort by it. The example from above would be different.

1. 5x4+6x5 (50/5) = 10
2. 10x4 stars (40/5) = 8
3. 3x5 stars (15/5) = 3 
4. 1x4+2x5 (14/5) = 2,8
5. 2x5 stars (10/5) = 2
6. 1x4 stars (4/5) = 0,8

Can you please look into it? It would really help to have a more sophisticated logic in sorting by rating. 

Thank you!

Edited September 27, 2021 by Sonya*