Including calculations on related data in an ActiveRecord query

Question

Let's say I have some models: User, Post, and Vote. A user has many posts and a post has many votes. Votes can be either an up vote or a down vote (stored as a boolean). What I am considering is the best way to do queries like these:

1. All users, and the total number of up votes they've received.
2. All users, and the post that has received the most up votes.
3. All users, and the total number of votes (both up and down) they've received.

There are three ways I can think to do this, in increasing levels of efficiency:

1. Calculate vote counts in the controller using loops. This would potentially do a lot of extra queries and query data that I don't need, like each post and each vote record. For example (3rd query):

   @users = User.all
   @vote_count = @users.posts.votes.count # retrieves every post and vote, which I don't need
   

2. Store the vote counts as fields in the User model and use callbacks to update those counts whenever a vote is made. This would make the query simpler, but I'd like to keep the models more loosely coupled and not have the user model's schema grow every time I need some sort of data on a related model.

3. Use some sort of query that will do these types of calculations via SQL and not look up more data than I need. This seems like the best choice, but I don't know how to approach it. Any suggestions/examples? Thanks!

Answer 1

Use the vote_fu plugin. It supports following methods:

user.vote_count       # all votes
user.vote_count(true) # votes for
user.vote_count(false) # votes against
posts.votes_count   # all vote count
posts.votes_for     # votes for
posts.votes_against # votes against
posts.votes_total   # votes total

If you don't want to use the plugin then I would approach your scenario as follows:

I am assuming the following relationship amongst models.

class User < ActiveRecord::Base
  has_many :posts
  has_many :votes, :through => :posts
end

class Post < ActiveRecord::Base
  belongs_to :user
  has_many :votes
end

class Vote < ActiveRecord::Base
  belongs_to :post
end

1.1) To count all the votes voted by all the users

Vote.count  # all
Vote.count(:conditions => {:vote => true}) # all for
Vote.count(:conditions => {:vote => true}) # all against

1.2) To find the votes by a user

user.votes.count # all
user.votes.count(:conditions => {:vote => true}) # all for
user.votes.count(:conditions => {:vote => true}) # all against

2.1) Users with most most Up votes

# You can add the limit clause to restrict the rows
User.find(:all, :select => "users.*, count(votes.id) AS count", 
                :joins => [:posts, :votes],  
                :conditions => [" votes.vote = ? ", true],
                :group => "votes.id", :order => "count DESC")

2.2) Posts with most Up votes

# You can add the limit clause to restrict the rows
Post.find(:all, :select => "posts.*, count(votes.id) AS count", 
                :joins => [:votes],  
                :conditions => [" votes.vote = ? ", true],
                :group => "votes.id", :order => "count DESC")

3.1 ) For a user total number of votes Refer to 1.2.1