MySQL - How to run a SELECT query that orders results by a score -- such as upvotes - downvotes

Question

I have two tables -- an article table and a vote table. Users can either vote up or vote down articles of their choice (similar to Reddit). The fields I have in the vote table are:

• article_id
• user_id
• vote

The value for the vote field can either be 0 or 1... (0 if they vote down the article, 1 if they vote up).

What I'm trying to do is run a SELECT query that returns all articles that have the highest score. That is, upvotes minus downvotes. However, I'm completely lost on how this would be done. I'm able to return all articles that have the most upvotes, such as the following:

-- article table is called "article"
-- vote table is called "user_article_vote"

SELECT article.title, article.summary, COUNT( user_article_vote.vote ) AS votes
FROM article
INNER JOIN user_article_vote ON article.article_id = user_article_vote.article_id
WHERE user_article_vote.vote = '1'
ORDER BY votes

This will return the articles that have the most upvotes, but is it possible to return articles that have the highest score (upvotes - downvotes)?

Thanks in advance.

Answer 1

First off, I would change your downvotes to -1 instead of 0. The math works better that way.

How about changing your SELECT statement to the following:

SELECT article.title, 
       article.summary, 
       SUM( CASE user_article_vote.vote WHEN 0 THEN -1 ELSE 1 END ) AS balance
FROM article
INNER JOIN user_article_vote ON article.article_id = user_article_vote.article_id
ORDER BY balance DESC    

This is MySQL, so I don't know if you need a GROUP BY article.title, article.summary clause or not. That always confuses me.