Simple ActiveRecord 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 pos开发者_开发问答ts table.

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

---

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

---

Just do all the normal SQL stuff as part of the query with options.

@posts = Post.paginate :order => "date DESC", :join => " inner join votes on post.id..." , :group => " votes.user_id"

http://apidock.com/rails/ActiveRecord/Base/find/class

So I don't know much about your models, but you seem to know somethings about SQL so

named scopes: you basically just put the query into a class method:

named_scope :index , :order => 'date DESC', :join => .....

but they can take parameters

named_scope :blah, {|param| #base query on param }

for you, esp if you are more familiar with SQL you can write your own query,

@posts = Post.find_by_sql( <<-SQL ) 
  SELECT posts.*
  ....
SQL