How can I compute a conditional sum in my model?

Question

Say I want something like this in Rails:

class Proposal < ActiveRecord::Base
    def interest_level
        self.yes_votes.count - self.no_votes.count
    end

  private
    def yes_votes
        self.votes.where(:vote => true)
    end

    def no_votes
        self.votes.where(:vote => false)
    end
end

1. What have I basically done wrong in the code above? (I realize it's probably terrible in numerous ways.)
2. What's the correct way to do this from a Rails standpoint?
3. What considerations should I be mindful of from a database standpoint? (e.g., even if code like the above were possible, I'm guessing it would be excessive on the DB side. But naturally I'm not really sure.)

Answer 1

Hi,

class Proposal < ActiveRecord::Base

def interest_level
    self.votes.sum('votes', :conditions => {:votes = true}) - self.votes.sum('votes', :conditions => {:votes = false})
end

end

thanks, Anubhaw

Answer 2

I don't really see anything overtly wrong with your code. Though there are a number of ways to accomplish what you seem to be trying to do, your method seems like it should work fine (though I have limited experience with Arel).

Another way to go about this might be simply changing the interest_level method:

def interest_level
    self.votes.count - self.no_votes.count * 2  # same as (total - no_votes) - no_votes
end

The above might be slightly quicker, though I highly doubt it will make much difference, count queries are pretty fast on indexed columns, and your version of that method is easier to read

Answer 3

Considering the database loading i recommend to implement a custom counter cache. I would do like this:

class Vote < ActiveRecord::Base

    def after_create
      self.update_counter_cache
    end

    def after_destroy
      self.update_counter_cache
    end

    def update_counter_cache
      self.proposal.yes_votes_count = self.proposal.votes.where(:vote=>true)
      self.proposal.no_votes_count = self.proposal.votes.where(:vote=>false)
      self.propsal.save
    end
end

Please note, you have to add two columns to your Proposal model.

add_columns_migration.rb

add_column :proposals, :yes_votes_count, :integer
add_column :proposals, :no_votes_count, :integer