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 >0ORDERBY ci_lower_bound DESC;