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
精彩评论