如何使用 SQL/ActiveRecord 按具有自引用关系的两列进行查询？

发布于 2024-11-04 08:24:05 字数 976 浏览 0 评论 0原文

所以我有一个 Story 和一个 Share 模型。 故事属于共享，并且共享拥有一个故事。它们都有时间戳。 Share 是自引用的，因此它上面有一个 parent_id 列，有时一个共享会嵌套在另一个共享下面。而且它不是无限嵌套的，只是向下一层。

最初，我只是对 Story 的 created_at 列进行排序。但现在我需要做一些更复杂的事情。如果 Story 有 Share 或嵌套共享，我也想对这些的 created_at 列进行排序。这个想法是，对该故事（嵌套共享）的进一步活动会将其提升到订单的顶部。

编辑：这是解决方案：

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

或者在 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')

感谢您的帮助！

原文

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 down.

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

偏爱自由 2024-11-11 08:24:05

您必须加入 Share 两次。在 SQL 中，它会是这样的：

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)

不知道如何在 Rails 中做到这一点，尝试使用 :includes 进行试验

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

回复收藏 0 原文

~没有更多了~