mysql - 如何在没有很多连接语句的情况下制作新闻源

Question

制作新闻源的最佳方式是什么？

目前我有一个观察者，每当有人创建记录时，它就会创建一个新的新闻源事件记录。但是，为了处理隐私，我最终有 7 或 8 个连接以获得适当的输出。

看起来这将是缓慢且低效的。提取正确的新闻源事件记录作为范围的另一种策略是什么？

---

更多详情:

目前我有一个网站可以帮助用户跟踪他们正在处理的项目。有公共(public)项目和私有(private)项目(人们可以在其中邀请合作者)。

我希望在创建公共(public)项目时包含我的新闻源。当您受邀参加私有(private)项目时。当用户关注项目时。然后是您关注的其他用户的所有操作。然后对于私有(private)项目，我有另一个连接表来确定谁有权访问这些项目。 (我也想在新闻源中显示对这些项目中的每一个的评论)。

以下所有关系目前都在连接表中，这就是为什么我有很多连接。

要了解查询的类型 - 我认为它看起来像这样:

SELECT news_feed_activities.* FROM news_feed_activities LEFT JOIN user_following_relationships ON user_following_relationships.following_id = news_feed_activities.user_id LEFT JOIN user_project_relationships ON user_project_relationships.project_id = news_feed_activities.responding_to_id AND news_feed_activities.responding_to_type = 'Project' WHERE (user_following_relationships.user_id = 1 OR user_project_relationships.user_id = 1 OR news_feed_activities.user_id = 1 OR up2.user_id = 1) GROUP BY news_feed_activities.id ORDER BY news_feed_activities.id DESC

---

编辑:我想我可能最终会按照这些思路使用 Redis http://blog.waxman.me/how-to-build-a-fast-news-feed-in-redis

Answer 1

作为 RoR。

在你的 Controller 中:

@user = current_user # (?)
recent_since = 24.hours.ago
@news_feed = []

# 1) I want my newsfeed to include when public projects are created. 
@news_feed += Project.recent.open
# 2) When you are invited to a private project. 
@news_feed += @user.invites.received.pending
# 3) When a user follows a project.
@news_feed += @user.user_following_relationships.recent
# 4) And then all of the actions of the other users that you're following. 
@news_feed += @user.follows.collect(&:activities)
# 5) Then for the private projects I have another join table to determine who has access to the projects. (There are also comments on each of these projects that I want to show up in the newsfeed as well).
@news_feed += @user.projects.closed

@news_feed.sort!{ |a,b| a.created_at <=> b.created_at }

我也为您做了一些示波器示例。项目.rb

scope :recent, :conditions => ["created_at >= ?", 24.hours.ago]
scope :open, :conditions => "publicity = 'Public'"
scope :closed, :conditions => "publicity = 'Private'"

这是基于这样的原则，即您的新闻提要实际上是跨模型最近事件的摘要，而不是拥有“新闻提要”模型。