MYSQL Order By Like/dislikes And Popularity

I have table of comments which includes likes and dislikes, now i have problem with proper order.. Actually my system shows comments with greatest amount of likes on top. I'm look

Solution 1:

This is classic problem how to rank upvote/downvote, plus/minus, like/dislike and so on. There are a few possible solutions but they may give wrong result in specific conditions.

I strongly recommend reading and using ordering like in How Not To Sort By Average Rating

PROBLEM:

You need some sort of "score" to sort by.

WRONG SOLUTION #1: Score = (Positive ratings) - (Negative ratings)

WRONG SOLUTION #2: Score = Average rating = (Positive ratings) / (Total ratings)

CORRECT SOLUTION: Score = Lower bound of Wilson score confidence interval for a Bernoulli parameter

Sample code (you can easily adapt it for your needs):

SELECT id, ((positive + 1.9208) / (positive + negative) - 
                1.96 * SQRT((positive * negative) / (positive + negative) + 0.9604) / 
                       (positive + negative)) / (1 + 3.8416 / (positive + negative)) 
       AS ci_lower_bound 
FROM your_tab 
WHERE positive + negative > 0 
ORDER BY ci_lower_bound DESC;