Displaying Posts by number of "vote" records in separate table

Question

I am new to Rails so go easy. I have created a blog and also created the ability for users to indicate they "like" a particular post. The way I am implementing this is by using the post table and a separate table 'vote'. When the user clicks the "like" button it sends the record to the 'vote' table with a value of '1' and the particular post id.

I would like to display the "most liked" posts in the sidebar. How do I go about calling such a thing. I would like to display the post_title and the number of 'votes', that is, I would like to somehow query the 'vote' table for the post_id's that have the most records and display them in descending order.

I hope this is an easy question.

Answer 1

This is actually best done by adding a counter cache to the post model, avoiding the database count on every load.

This railscast episode explains how to setup the counter cache.

Assuming you named your counter cache votes_count, you can do this to get the 10 most popular posts from your controller.

@popular_posts = Post.find(:all, :limit => 10, :order => "votes_count DESC")

Answer 2

There are several ways to accomplish this, but probably the most versatile and Rails-ish would be to create a module with a method to do the ranking, and then have any classes or associations that can be "liked" extend that module.

# lib/likable.rb
#
module Likable
  def most_liked (limit = 10)
    # This may be possible without a find_by_sql... see the API docs.
    find_by_sql("SELECT Posts.*, SUM(votes.count) AS num_votes FROM Posts, Votes WHERE Posts.id = post_id GROUP BY post_id ORDER BY num_votes DESC LIMIT #{limit}")
  end
end

# app/models/post.rb
#
require 'likable'

class Post < ActiveRecord::Base
  extend Likable
  # ...whatever else you've got in here
end

# app/models/user.rb (or any other model with things that can be "liked")
#
require 'likable'

class User < ActiveRecord::Base
  has_many :posts, :extend => Likable
  # ...the rest of the User class
end

This lets you do things like...

Post.most_liked                 # => an array of the 10 most liked posts
@some_user.posts.most_liked(5)  # => that user's 5 most liked posts

If you needed to later, you could add methods to the module to see, eg, how many votes a particular Post has. You could also change the post_id to a target_id in Vote and make it a polymorphic association, and then you could use your Likable module to vote for anything, not just posts (you would need to generalize the call to find in most_liked if you did that).