How do I make a newsfeed without many join statements

Question

What is the best way to approach making a newsfeed?

Currently I have an observer that creates a new newsfeedactivity record everytime someone creates a record. But, to deal with privacy I end up have 7 or 8 joins to get the appropriate output.

It seems like this is going to be slow and inefficient. What's another strategy for pulling out the right newsfeedactivity records as a scope?

---

More details:

Currently I have a site to help users track projects that they're working on. There are public and private projects (where people can invite collaborators).

I want my newsfeed to include when public projects are created. When you are invited to a private project. When a user follows a project. And then all of the actions of the other users that you're following. 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).

All of the following relationships are currently in join tables, which is why I have a lot of joins.

To get an idea of the type of query - I'm thinking it would look something like this:

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

---

EDIT: I think I'm probably going to end up using Redis along these lines http://blog.waxman.me/how-to-build-a-fast-news-feed-in-redis

Answer 1

As RoR.

In your 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 }

I did some sample scopes for you too. project.rb

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

This is based on the precept that your news feed is actually a summary of recent activity across models rather than having a 'newsfeed' model.