Rails: How to sort messages with comments?

Question

Message can have multiple comments:

class Message < ActiveRecord::Base
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :message
end

The following named scope returns messages that were created in a given range of time, ordered by creation time (latest first):

scope :created_between, 
      lambda { |rng| where("created_at" => (rng[:start_time]..rng[:end_time])).
                     order("created_at DESC") }

How could I write a named scope that returns messages that have post (the message itself or one of its comments) that was created in a given range of time, ordered by creation time of the latest post (latest first) ?

Example:

If the following messages exist:

Message 1            April, 2010
  Comment 1          May, 2011
  Comment 2          July 2011
Message 2            January 2011
  Comment 1          August 2011
  Comment 2          March 2011
  Comment 3          February 2011
Message 3            March 2009
  Comment 1          January 2010
Message 4            June 2011

then

scope :has_post_between({:start_time => <February 2010>, 
                         :end_time => <August 2011>}), ...

should return:

Message 2
Message 1
Message 4

Message 3 is not included because its posts were created before February 2010. Message 2 is first because it has the latest post (August 2011).

Answer 1

class Message < ActiveRecord::Base
  has_many :comments

  scope :updated_between, lambda { |rng|
    joins(:comments).
    where(:created_at => rng, :comments => { :created_at => rng }).
    order("comments.created_at DESC, messages.created_at DESC")
  }
end

Answer 2

This is off the top of my head but I think you can do:

Message.joins(:comments).where(["comments.start_time > ? AND comments.end_time < ?", start_time, end_time]);