Grab only the latest comment in Rails

Question

Hi all

In a typical User - Post - Comment model in Rails, every user can create a Post and also can create Comment, question is how to grab every user latest comment on specific post.

Example:

Post A have 3 user making comment
User 1 have comment 1, 2, 3, 4, 5, 6
User 2 have comment 1, 2, 3, 4
User 3 have comment 1, 2

So the view I want is just the latest comment for every user:

Post A have 3 user making comment
User 1 latest comment that is 6
User 2 latest comment that is 4
user 3 latest comment that is 2

How to do it ?

thanks

Answer 1

Something like this:

post.comments.for_user(current_user).last

add a named_scope in your model

class Comment 
    named_scope :for_user, lambda{ |user| {:conditions=>{:user_id => user.id}}
end

That should do the trick.

If you rather do it in rails,

messages_by_users  = post.messages.group_by(&:user)

messages_by_users.each do  |key, value|   
    messages_by_users[key] = value.last
end

Answer 2

I have had to get this kind of data and usually I end up doing two queries. In my case I have Blogs and their Posts and I wanted a list of the 3 most recent blog posts with the restriction that the blogs are unique, I dont want 2 posts from the same blog. I ended up doing something like this (MySQL):

q = <<-EOQ
SELECT id,pub_date FROM
  (
  SELECT id,blog_id,pub_date
  FROM posts 
  ORDER BY pub_date DESC 
  LIMIT 40
  )
t
GROUP BY blog_id
ORDER BY pub_date DESC
LIMIT #{num_posts}
EOQ
post_ids = Post.connection.select_values(q)
Post.find(:all, :include => [:blog], :conditions => ["id IN (?)", post_ids], :order => "posts.pub_date DESC")

So in your case you might have something like:

q = <<-EOQ
SELECT id FROM
  (
  SELECT id,post_id
  FROM comments 
  ORDER BY id DESC 
  LIMIT 40
  )
t
GROUP BY post_id
ORDER BY id DESC
LIMIT 10
EOQ
post_ids = Post.connection.select_values(q)
Post.find(:all, :include => [:blog], :conditions => ["id IN (?)", post_ids], :order => "posts.id DESC")

Answer 3

Assuming that your database is assigning sequential IDs to the comments, you can do this:

class Comment
  named_scope :most_recent, lambda {
     lastest_comments = Comment.maximum :id, :group => "user_id, post_id"
     { :conditions => [ "comment_id in ?", lastest_comments.map(&:last) ] }
  }
end

This gives you a two-query method that you can use in a variety of ways. The named_scope above pulls back the most recent comments for all users on all posts. This might be a problem if your database is gigantic, but you can certainly add conditions to make it more specific.

As it stands, it is a flexible method that allows you to do the following:

Comment.most_recent.find_by_user @user   #-> the most recent comments on all posts by a user
@user.comments.most_recent               #-> same as above

Comment.most_recent.find_by_post @post   #-> the most recent comments on a single post by all users
@post.comments.most_recent               #-> same as above

Comment.most_recent.find_by_user_and_post @user, @post    #-> the specific most recent comment by a certain user on a certain post
@post.comments.most_recent.find_by_user @user             #-> you get the idea