named_scope to order posts by last comment date

Question

Posts has_many Comments

I'm using searchlogic which will order by named scopes. So, I'd like a named scope that orders by each post's most recent comment.

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

I'm not sure how to do a :joins and get only the most recent comment and sort by its created_at field, all in a named_scope.

I'm using mysql, fyi.

EDIT:

This is the SQL query I'd be 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

Hi,

You can do that by joining or including the associated model through the scope, something like this will do the trick:

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

Answer 2

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 hints how to do it.

Edit:

After you edited question and shown that you want inner join (no posts without comments?), you can of course change :joins => "..." with :joins => :comments.