Including related data calculations in an ActiveRecord query

Question

Let's say I have some models: User, Postand Vote. The user has many messages and the mail has many votes. Votes can be either a vote or a simple vote (stored as logical). What I am considering is the best way to make such requests:

• All users and the total number of votes they received.
• All users and the mail that received the most votes.
• All users and the total number of votes (both up and down) that they received.

There are three ways that I can do to improve efficiency:

• Calculate the vote count in the controller using cycles. This will potentially make a lot of additional queries and query data that I don’t need, for example, every post and every vote. For example (third request):

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

• Store the vote count as fields in the user model and use callbacks to update these counters whenever a vote is taken. This will simplify the query, but I would like the models to be more loosely coupled and not display the user model schema every time I need some data on the related model.

• Use some kind of query that will perform these types of calculations through SQL, and not look for more data than I need. This seems like the best choice, but I don’t know how to approach it. Any suggestions / examples? Thank!

Answer 1

vote_fu . :

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

, :

.

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) ,

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

1.2)

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

2.1)

# 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) .

# 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) . 1.2.1