Displaying most popular posts on my blog - Ruby on Rails

Question

I have created a simple blog application using Ruby on Rails, both of which I am new to. I am trying to get my most 'voted' posts to display in my /views/posts/index.html.erb view. Here are the basics.

I have created a votes table and successfully allow a user to 'vote' for a post on the /views/posts/show.html.erb page. The VotesController passes the post_id to the Votes table column post_id and returns the vote count for that post via ajax to the same show page.

class Vote
  belongs_to :post`
end

and

class Post
  has_many :votes
end

I would like to display in /views/posts/index.html.erb the post title and the amount of times it has been voted for, ordered by posts with the highest vote count.

EDIT With the code below I have been able to display (in the /views/posts/show.html.erb page) the post_id from the Votes table and vote_count for that post_id. i.e. '55(7)'. Wondering if somehow I can also pass the post_title to the votes table (to column post_title) and then just display that instead of the post_id. Here is the code that is accomplishing that.

class PostsController < ApplicationController

     def index
        @tag_counts = Tag.count(:group => :tag_name, 
           :order => 'count_all DESC', :limit => 20)
        @vote_counts = Vote.count(:group => :post_id, :limit => 5)

        conditions, joins = {}, nil
        unless(params[:tag_name] || "").empty?
          conditions = ["tags.tag_name = ? ", params[:tag_name]]
          joins = :tags
        end
        @posts=Post.all(:joins => joins, :conditions=> conditions, :order => 'created_at DESC').paginate :page => params[:page], :per_page => 5

        respond_to do |format|
          format.html # index.html.erb
          format.xml  { render :xml => @posts }
          format.json { render :json => @posts }
          format.atom
        end
      end

VotesController

class VotesController < ApplicationController

  def create
    @post = Post.find(params[:post_id])
    @vote = @post.votes.create!(params[:vote])

       respond_to do |format|
       format.html { redirect_to @post}
       format.js
     end
  end
end

/views/posts/index.html.erb

Most Liked Posts<hr/>
        <% @vote_counts.each do |post_id, vote_count| %>    
                <div id="tag-wrapper">
                    <%= link_to(post_id, posts_path(:post_id => post_id)) %>
                    (<%=vote_count%>)</div>
        <% end %>

Answer 1

use mysql query.Something like following say

@posts=Post.find_by_sql("select p.post, count(v.vote) as vote from posts p, votes v where v.post_id=p.id group by p.id order by vote DESC")

on rhtml

for post in @posts 
  post.post
  post.vote
end

Answer 2

You can use the vote_fu plugin for this (use kandadaboggu-vote_fu version)

class User
 acts_as_voter
end

Add vote_total column(integer) to posts table.

class Post
 acts_as_voteable :vote_counter => true
end

Now you can do the following:

user.vote_for(post)       # vote up
user.vote_against(post)   # vote down
post.vote_total           # sum of +ve and -ve votes
post.vote_count           # count of votes

If you use the vote_counter directive, vote total is maintained in the posts table. No extra SQL is needed to calculate vote_total.

To sort the posts by vote_total do the following:

Posts.all(:order => "vote_total DESC")

Disclosure

I maintain kandadaboggu-vote_fu gem.

Edit

To implement this in your current schema, you can do the following:

Post.all(:joins => :votes, :select => "posts.*, count(*) as vote_total",
  :group => "votes.post_id", :order => "vote_total DESC", :limit => 10)

Edit 2

You can use the following code to implement this feature to work with your current logic. Please note that Rails uses INNER joins, so a post will not be selected if it does not have a vote. You can circumvent this by always voting once when the Post is created.

I have fixed your paginate call. Current logic is extremely inefficient. The pagination is applied on the array returned from the DB, so entire result set is loaded before pagination is performed. As rule, treat paginate like all method with two extra parameter.

Please note that the SQL below will put strain on your system resources. You might have to optimize by storing vote_total in the posts table.

conditions, joins = {}, :votes 

unless(params[:tag_name] || "").empty?
  conditions = ["tags.tag_name = ? ", params[:tag_name]]
  joins = [:tags, :votes]
end
@posts=Post.paginate(
          :select => "posts.*, count(*) as vote_total", 
          :joins => joins, 
          :conditions=> conditions, 
          :group => "votes.post_id", 
          :order => "vote_total DESC",
          :page => params[:page], :per_page => 5)

Edit 3

Logic for voting upon creation of a post.

class Vote
  belongs_to :user
  belongs_to :post
end

class Post
  belongs_to :user
  after_create :self_vote

  def self_vote
   # I am assuming you have a user_id field in `posts` and `votes` table.
   self.votes.create(:user => self.user)
  end
end

Iteration code for posts:

<% @posts.each do |post| %>
 <p><%= link_to(post.title, post_path(post)) %></p>
 <p>
     Created <%=  time_ago_in_words(post.created_at) %>. 
     Votes <%=  post.vote_total %>
 </p> 
<% end %>