SQL query to get "vote score"

Question

My votes table looks like this:

id: integer
vote: boolean
voteable_id: integer
voteable_type: string
voter_id: integer
voter_type: string

The vote column determines whether the row represents an "up vote" (vote = true) or a "down vote" (vote = false).

voteable_type is the class of the thing being voted on, voteable_id is the id of the thing being voted on, voter_type is the class of the voter, and voter_id is the id of the voter.

What I need is a query to get the top n posts in descending order by "vote score", where "vote score" is defined as (the number of up votes the post has) - (the number of down votes the post has).

Bonus points if your solution doesn't require me to resort to find_by_sql() (I'm working in Rails)

More bonus points if your solution works the same way in SQLite and PostgreSQL (though it's more important that it work in PostgreSQL).

Answer 1

Generically, you can do it by using a case statement with a sum:

select
    voteable_id,
    sum(case
        when vote then 1
        else -1
    end) as vote_score
from
    votes
group by
    voteable_id

Note that this is ANSI SQL, so will work across SQLite, MySQL, Postgres, Oracle, SQL Server, DB2, etc., etc.

To get your top N posts, you would simply append to the above query:

order by
    vote_score desc
limit 10

limit is used by Postgres and SQLite (a bit differently in MySQL), and not in Oracle or SQL Server, as an FYI.

So, to get the posts info associated with this:

select
    p.title,
    p.author,
    p.createdate,
    sum(case
            when v.vote then 1
            else -1
        end) as vote_score
from
    posts p
    inner join votes v on
        p.post_id = v.voteable_id
group by
    p.title,
    p.author,
    p.createdate
order by
    vote_score desc
limit 10

Answer 2

The conditional function common to SQLite and PostgreSQL (and other ANSI-compliant SQL implementations) is CASE -- see e.g. here for PostgreSQL and here for SQLite. So the inner part for "count +1 / -1 number of votes" is going to have to be

SUM(CASE WHEN vote THEN 1 ELSE -1 END)

and you'll also inevitably need a GROUP BY voteable_id to make this SUM work right.

This will need to be in the ORDER BY for the sorting (with a DESC); not sure if you ALSO want it in the results, but I'll assume you do, in which case it also needs to be in the SELECT (and you can refer to its alias in the ORDER BY). Finally, LIMIT n works in both engines.

So, putting it all together:

  SELECT voteable_id,
         SUM(CASE WHEN vote THEN 1 ELSE -1 END) AS vote_score
  FROM votes
  GROUP BY voteable_id
  ORDER BY vote_score DESC
  LIMIT 10

should satisfy all of your requirements.

Answer 3

If you're using VoteFu (which it looks like you are), then I'd recommend that you convert to using Integers to store the vote values instead of :boolean.

The only reason that votes in VoteFu are stored as booleans is that I felt I needed to maintain backward compatibility with Acts_As_Voteable. I've now decided that I placed too much weight on this concern.

I'm going to be releasing a new version of the VoteFu plugin that handles the conversion for you, but until then I think changing this yourself is a smart move. Here's how:

class VoteFuIntegerMigration < ActiveRecord::Migration  
  def self.up  
    add_column :votes, :vote_int, :integer
    Vote.find(:all).each do |vote|
       vote.vote_int = vote.vote? ? 1 : -1
       vote.save!
    end
    remove_column :vote, :vote
    rename_column :vote, :vote_int, :vote
  end
end

And then:

module Juixe 
  module Acts   
    module Voteable   
      module InstanceMethods  
        def votes_for  
          Vote.sum(:vote, :conditions => [  
            "voteable_id = ? AND voteable_type = ? AND vote > 0",  
            id, self.class.name])  
        end  

        def votes_against  
          Vote.sum(:vote, :conditions => [  
            "voteable_id = ? AND voteable_type = ? AND vote < 0",  
            id, self.class.name])  
        end  

        def votes_total  
          Vote.sum(:vote, :conditions => [  
            "voteable_id = ? AND voteable_type = ?",  
            id, self.class.name])  
        end  
      end
    end
  end
end

It's probably a good idea to introduce the module changes as a monkeypatch. The new VoteFu will have this code integrated (with tests, too.)