rails 3.1 database query

Question

I have a rails 3.1 app with the following Models:

class Post < ActiveRecord::Base
  has_many :comments
end 

class Comment < ActiveRecord::Base
  belongs_to :post
end

I want to retrieve 5 posts, with the most recent comments.

Problem is that when for example Post A has 3 comments that are more recent than the most recent comment on Post B.

I want the query to do something like this:
The number on the comments means 1 = newest 10 = oldest.

Post A
 comment 1 #=>ok fetch the post
Post B
 comment 2 #=>ok fetch the post
 comment 3 #=>this post is fecthed go on find the next one
 comment 4 #=>this post is fecthed go on find the next one
Post C
 comment 5 #=>ok fetch the post
 comment 6 #=>this post is fecthed go on find the next one
Post D
 comment 7 #=>ok fetch the post
 comment 8 #=>this post is fecthed go on find the next one
Post E
 comment 9 #=>ok fetch the post
 comment 10

Is it possible to make a elegant query to do this?

A possible solution is to update a :comment_updated_at column in the post table.

Answer 1

This should works:

comments = Comment.order("updated_at DESC").group(:post_id).limit(5)

This will return 5 last comments, with distinct posts.

So after that you can just:

recent_commented_posts = comments.map(&:post)

and voila.

Answer 2

if your Comment model is a Rails model, it should already have a updated_at column.

Comment.order(:by => :updated_at).limit(5)

but the problem with that is that you only get the 5 newest comments - those are not necessarily in 5 different posts.

On the other hand, you can find the 5 unique posts with the 5 last updated comments like this:

comments = Comment.order(:by => :updated_at).select("DISTINCT(post_id)").limit(5)

But this doesn't show all the comments for those 5 posts.

It's probably best to do the second query first, to get to the posts via the post_id , then list the relevant comments for each of the posts.

something along these lines: (of course you need to do this in the view, without 'puts')

 comments.each do |c|
     puts c.post.name
     puts c.post.comments.order(:by => :updated_at)   # instead of just showing one comment 'c'
 end