Correct ActiveRecord joins query assistance

Question

Hi all,

Need a little help with a SQL / ActiveRecord query. Let's say I have this:

Article < ActiveRecord::Base
  has_many :comments
end

Comment < ActiveRecord::Base
  belongs_to :article
end

Now I want to display a list of "Recently Discussed" articles - meaning I want to pull all articles and include the last comment that was added to each of them. Then I want to sort this list of articles by the created_at attribute of the comment.

I have watched the Railscast on include /joins - very good, but still a little stumped.

I think I want to use a named_scope, something to this effect:

Article < ActiveRecord::Base
  has_many :comments

  named_scope :recently_commented, :include => :comments, :conditions => {   some_way_to_limit_just_last_comment_added }, :order => "comments.created_at DESC"
end

Using MySQL, Rails 2.3.4, Ruby 1.8.7

Any suggestions? :)

Answer 1

You have two solutions for this.

1) You treat n recent as n last. Then you don't need anything fancy:

Article < ActiveRecord::Base   
  has_many :comments

  named_scope :recently_commented, :include => :comments, 
                :order => "comments.created_at DESC",
                :limit => 100 
end

Article.recently_commented # will return last 100 comments

2) You treat recent as in last x duration.

For the sake of clarity let's define recent as anything added in last 2 hours.

Article < ActiveRecord::Base   
  has_many :comments

  named_scope :recently_commented, lambda { { 
                    :include => :comments, 
                    :conditions => ["comments.created_at >= ?", 2.hours.ago]
                    :order => "comments.created_at DESC",
                    :limit => 100 }} 

end

Article.recently_commented # will return last 100 comments in 2 hours

Note Code above will eager load the comments associated with each selected article. Use :joins instead of :include if you don't need eager loading.

Answer 2

You're gonna have to do some extra SQL for this:

named_scope :recently_commented, lambda {{
  :select => "articles.*, IFNULL(MAX(comments.created_at), articles.created_at) AS last_comment_datetime",
  :joins => "LEFT JOIN comments ON comments.article_id = articles.id",
  :group => "articles.id",
  :conditions => ["last_comment_datetime > ?", 24.hours.ago],
  :order => "last_comment_datetime DESC" }}

You need to use :joins instead of :include otherwise Rails will ignore your :select option. Also don't forget to use the :group option to avoid duplicate records. Your results will have the #last_comment_datetime accessor that will return the datetime of the last comment. If the Article had no comments, it will return the Article's created_at.

Edit: Named scope now uses lambda