Named_scope order messages by the date of the last comment

Question

Posts has_many Comments

I use searchlogic , which will order by named areas. So, I would like a named area that orders the latest comments for each comment.

named_scope :ascend_by_comment, :order => ...comments.created_at??...

I'm not sure how to make :joinsand get only the last comment and sort it by field created_at, all in named_scope.

I am using mysql, fyi.

EDIT:

This is the SQL query I'm trying to emulate:

SELECT tickets.*, comments.created_at AS comment_created_at FROM tickets 
INNER JOIN 
(SELECT comments.ticket_id, MAX(comments.created_at) AS created_at 
  FROM comments group by ticket_id) comments 
ON tickets.id = comments.ticket_id ORDER BY comment_created_at DESC;

Answer 1

named_scope :ascend_by_comment,
  :joins => "LEFT JOIN comments ON comments.post_id = posts.id",
  :group => "id",
  :select => "posts.*, max(comments.created_at) AS comment_created_max",
  :order => "comment_created_max ASC"

You can try to optimize it, but it should work and give you some tips on how to do this.

Edit:

, ( ?), , , :joins => "..." :joins => :comments.

Answer 2

, , - :

named_scope :ascend_by_comment, :joins => :comments, :order => "comments.created_at DESC"