MYSQL order by like/dislikes and popularity

Question

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 looking for something like system in youtube.

It means that comment with 100like/100dislikes is higher in order than 1/1..

I hope this is understandable :)

Answer 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;