Complex Rails queries across multiple tables, unions, and will_paginate. Solved.
- by uberllama
Hi folks. I've been working on a complex "user feed" type of functionality for a while now, and after experimenting with various union plugins, hacking named scopes, and brute force, have arrived at a solution I'm happy with. S.O. has been hugely helpful for me, so I thought I'd post it here in hopes that it might help others and also to get feedback -- it's very possible that I worked on this so long that I walked down an unnecessarily complicated road.
For the sake of my example, I'll use users, groups, and articles. A user can follow other users to get a feed of their articles. They can also join groups and get a feed of articles that have been added to those groups. What I needed was a combined, pageable feed of distinct articles from a user's contacts and groups.
Let's begin.
user.rb
has_many :articles
has_many :contacts
has_many :contacted_users, :through => :contacts
has_many :memberships
has_many :groups, :through => :memberships
contact.rb
belongs_to :user
belongs_to :contacted_user, :class_name => "User", :foreign_key => "contacted_user_id"
article.rb
belongs_to :user
has_many :submissions
has_many :groups, :through => :submissions
group.rb
has_many :memberships
has_many :users, :through => :memberships
has_many :submissions
has_many :articles, :through => :submissions
Those are the basic models that define my relationships. Now, I add two named scopes to the Article model so that I can get separate feeds of both contact articles and group articles should I desire.
article.rb
# Get all articles by user's contacts
named_scope :by_contacts, lambda {|user|
{:joins => "inner join contacts on articles.user_id = contacts.contacted_user_id",
:conditions => ["articles.published = 1 and contacts.user_id = ?", user.id]}
}
# Get all articles in user's groups. This does an additional query to get the user's group IDs, then uses those in an IN clause
named_scope :by_groups, lambda {|user|
{:select => "DISTINCT articles.*",
:joins => :submissions,
:conditions => {:submissions => {:group_id => user.group_ids}}}
}
Now I have to create a method that will provide a UNION of these two feeds into one. Since I'm using Rails 2.3.5, I have to use the construct_finder_sql method to render a scope into its base sql. In Rails 3.0, I could use the to_sql method.
user.rb
def feed
"(#{Article.by_groups(self).send(:construct_finder_sql,{})}) UNION (#{Article.by_contacts(self).send(:construct_finder_sql,{})})"
end
And finally, I can now call this method and paginate it from my controller using will_paginate's paginate_by_sql method.
HomeController.rb
@articles = Article.paginate_by_sql(current_user.feed, :page => 1)
And we're done! It may seem simple now, but it was a lot of work getting there. Feedback is always appreciated. In particular, it would be great to get away from some of the raw sql hacking. Cheers.
