How do I query by two columns with a self-referential relation with SQL/ActiveRecord?

So I've got a Story and a Share model. The Story belongs_to a Share, and a Share has_one Story. They both have timestamps. Share is self-referential, so it's got a parent_id column on it, and sometimes a share will be nested underneath another. And it's not infinitely nestable, just one level d开发者_开发技巧own.

Initially I've just been sorting on the created_at column of Story. But now I need to do something a little more complex. If the Story has a Share or nested shares, I'd like to sort on the created_at columns of those as well. The idea being that further activity on that Story (nested shares) would bump it up to the top of the order.

EDIT: Here's the solution:

SELECT * FROM stories
LEFT OUTER JOIN shares AS s1 ON stories.share_id = s1.id
LEFT OUTER JOIN shares AS s2 ON s2.parent_id = s1.id
ORDERY BY s2.created_at DESC NULLS LAST, stories.created_at DESC

Or in ActiveRecord:

Story.joins('LEFT OUTER JOIN shares AS s1 ON stories.share_id = s1.id').
      joins('LEFT OUTER JOIN shares AS s2 ON s2.parent_id = s1.id').
      order('s2.created_at DESC NULLS LAST, stories.created_at DESC')

Thanks for the help!

You have to join Share twice. In SQL it would be something like that:

select * from stories
left outer join shares as s1 on s1.story_id=stories.id
left outer join shares as s2 on s1.parent_id=s2.id
order by greatest(s2.updated_at,s1.updated_at,stories.updated_at)

Don't know how to do that in Rails, try to experiment with :includes