How can I order my entries by sum from a separate table?

Question

I am wondering how I can order posts in my PostController#index to display by a column total in a separate table. Here is how I have it set up.

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

and

Class Vote < ActiveRecord::Base
  :belongs_to :post
end

I user can either vote up or down a particular post. I know there are likely better ways to do what I am currently doing but looking for a fix given my current situation. When a user votes up a post, a value of 1 is passed to the Vote Table via a hidden field. When a user votes down a post a value of -1 is passed to the same column (names vote).

I am wondering how I can display my posts in order of the sum of the vote column (in the vote table) for a particular post.

Another way to say that is, if a particular post has a net vote sum of 5, I want that to appear above a post with a net vote sum of 4.

I am assuming that I need to affect the PostController#index action in some fashion. But not sure how to do that.

Answer 1

You can use something like following.

class Post < ActiveRecord::Base
  :has_many :votes
   named_scope :post_list, 
                :select=>" posts.id, posts.title,posts.description,  votes.total",       
                :joins => :votes, "LEFT JOIN votes ON posts.id = votes.post_id"
                :order => "votes.total DESC"

end

Answer 2

Try this:

@posts = Post.all(:select => "posts.*, SUM(votes.vote) as vote_total",
         :joins => "LEFT JOIN votes AS votes ON votes.post_id = posts.id",
         :group => "posts.id",
         :order => "vote_total DESC")
@posts.each do |post|
  post.vote_total
end