Rails: Query to get recent items based on the timestamp of a polymorphic association

Question

I have the usual polymorphic associations for comments:

class Book < ActiveRecord::Base
  has_many :comments, :as => :commentable
end

class Article < ActiveRecord::Base
  has_many :comments, :as => :commentable
end

class Comment < ActiveRecord::Base
    belongs_to :commentable, :polymorphic => true
end

I'd like to be able to define Book.recently_commented, and Article.recently_commented based on the created_at timestamp on the comments. Right now I'm looking at a pretty ugly find_by_SQL query to do this with nested selects. It seems as though there must be a better way to do it in Rails without resorting to SQL.

Any ideas? Thanks.

For what it's worth, here's the SQL:

select * from 
    (select books.*,comments.created_at as comment_date 
    from books inner join comments on books.id = comments.commentable_id 
    where comments.commentable_type='Book' order by comment_date desc) as p 
group by id order by null;

Answer 1

Sometimes it's just best to add a field to the object of which you are commenting. Like maybe a commented_at field of datetime type. When a comment is made on an object, simply update that value.

While it is possible to use SQL to do it, The commented_at method may prove to be much more scalable.

Answer 2

Not sure what your method has looked like previously but I'd start with:

class Book < ActiveRecord::Base

  def self.recently_commented
    self.find(:all, 
              :include => :comments, 
              :conditions => ['comments.created_at > ?', 5.minutes.ago])
  end
end

This should find all the books that have had a comment created on them in the last 5 minutes. (You might want to add a limit too).

I'd also be tempted to create a base class for this functionality to avoid repeating the code:

class Commentable < ActiveRecord::Base
  self.abstract_class = true

  has_many :comments, :as => :commentable

  def self.recently_commented
    self.find(:all, 
              :include => :comments, 
              :conditions => ['comments.created_at > ?', Time.now - 5.minutes])
  end
end

class Book < Commentable
end

class Article < Commentable
end

Also, you might want to look at using a plugin to achieve this. E.g. acts_as_commentable.