I've got a rails 3.1 application using the following Models:
class Post < ActiveRecord::Base has_many :comments end class Comment < ActiveRecord::Base belongs_to :post end
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)
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) end class Post < ActiveRecord::Base has_many :comments def most_recent_comment self.comments.last end end
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' end