Search code examples
phpmysqlsortingrating

MYSQL sorting content by rating logic and opinion?


I'm designing a site and don't know how to rate the system in terms of logic.

Outcome is I want an item with 4 stars with 1000 votes to be ranked higher than an item with 1 vote of 5 stars. However, I don't want an item with 1 star with 1000 votes to be ranked higher than an item with 4 stars and 200 votes.

Anyone have any ideas or advice on what to do?

I found these two questions

Sorting by weighted rating in SQL?

MySQL Rating System - Find Rating

and they have their drawbacks and in the first one I don't understand what the winner means by "You may want to denormalize this rating value into event for performance reasons if you have a lot of ratings coming in." Please share some insight? Thank you!


Solution

  • Here's a quick sketch-up of such a system which works by defining a bonus factor xₙ for each flag number. According to your question you want:

    x₄*4*1000 > x₅*1*5
    

    and

    x₁*1*1000 < x₄*4*200
    

    Setting the factors to for example x₁=1, x₄=2 and x₅=2 will satisfy this, but you will of course want to adjust it and add the missing factors.