I've got a rails 3.1 application using the following Models:

class Post < ActiveRecord::Base
  has_many :comments

class Comment < ActiveRecord::Base
  belongs_to :post

I wish to retrieve 5 posts, most abundant in recent comments.

Issue is that after for instance Publish A has 3 comments which are newer than the newest discuss Publish B.

I would like the query to behave such as this:
The amount around the comments means 1 = latest 10 = earliest.

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

Can you really create a elegant query to get this done?

A potential option would be to update a :comment_up-to-date_at column within the publish table.

This will works:

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

This can return 5 last comments, with distinct posts.

So then you're able to just:

recent_commented_posts = comments.map(&:post)

and voila.

Order your comments ought to after which produce a model finder method.

class Comment < ActiveRecord::Base
  belongs_to :post
  default_scope :order => 'updated_at'  # or a named_scope ('scope' in rails3)

class Post < ActiveRecord::Base
  has_many :comments
  def most_recent_comment

in case your Comment model is really a Rails model, it will curently have a up-to-date_at column.

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

the main problem with that's that you simply only obtain the 5 latest comments - individuals aren't always in five different posts.

However, you'll find the five unique posts using the 5 last up-to-date comments such as this:

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

But this does not show all of the comments for individuals 5 posts.

It's most likely better to perform the second query first, to get at the posts through the publish_id , then list the appropriate comments for each one of the posts.

something along wrinkles: (obviously you must do this within 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'