tags:

views:

60

answers:

1

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.

+2  A: 

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.

David Andres
Thanks, but unfortunately my current schema does not allow that. I only have one field for votes (which can be 0 or 1), so I can't refer to a down_vote field. Is there a way to define "down_vote" using a function instead of referencing a field?
@unknown: been heavily editing this post. See the most current.
David Andres
Actually nevermind... I see that you updated your comment. I will try that and let you know. Thanks!
It worked! Thanks for your quick reply and helpful solution. Unfortunately I don't have the reputation points to vote you up, but I selected your post as the accepted answer.
@unknown: thanks anyway, dude. Glad it worked for you! Does the CASE statement make sense by the way?
David Andres
Once I selected your post as the answer, I had enough reputation points, so I voted you up.Yeah, the CASE statement makes sense. I just didn't imagine using it within a SUM() function. Thanks for your help on that.
Thanks, man. I only needed the case statement to morph 0-valued votes to negatives. After the fact, the SUM is merely collect the results of these calculations.
David Andres