Simple ActiveRecord Question

Question

I have a database model set up such that a post has many votes, a user has many votes and a post belongs to both a user and a post. I'm using will paginate and I'm trying to create a filter such that the user can sort a post by either the date or the number of votes a post has. The date option is simple and looks like this:

@posts = Post.paginate :order => "date DESC"

However, I can't quite figure how to do the ordering for the votes. If this were SQL, I would simply use GROUP BY on the votes user_id column, along with the count function and then I would join the result with the posts table.

What's the correct way to do with with ActiveRecord?

Answer 1

1) Use the counter cache mechanism to store the vote count in Post model.

# add a column called votes_count
class Post
  has_many :votes
end

class Vote
  belongs_to :post, :counter_cache => true
end

Now you can sort the Post model by vote count as follows:

Post.order(:votes_count)

2) Use group by.

Post.select("posts.*, COUNT(votes.post_id) votes_count").
  join(:votes).group("votes.post_id").order(:votes_count)

If you want to include the posts without votes in the result-set then:

Post.select("posts.*, COUNT(votes.post_id) votes_count").
  join("LEFT OUTER JOIN votes ON votes.post_id=posts.id").
  group("votes.post_id").order(:votes_count)

I prefer approach 1 as it is efficient and the cost of vote count calculation is front loaded (i.e. during vote casting).