My design has Users with Friendships to other Users, and they can all save Shows to a list of SavedShows. Now for an individual show page, I'd like to get a list of the friends for a particular registered user that have the same show saved, but I can't seem to figure out the right query.
I thought it would be something like following (3 being a sample show ID), but this doesn't seem produce valid SQL:
current_user.friends.joins(:shows).where(:show_id => 3)
Should I be able to do this in ActiveRecord via associations alone, or do I need to hand write the SQL by hand? I'm using Rails 3.
Here's what the models look like
# user.rb
class User < ActiveRecord::Base
has_many :saved_shows
has_many :shows, :through => :saved_shows
has_many :friendships
has_many :friends, :through => :friendships
has_many :followings, :class_name => "Friendship", :foreign_key => "friend_id"
has_many :followers, :through => :followings, :source => :user
...
end
# friendship.rb
class Friendship < ActiveRecord::Base
belongs_to :user
belongs_to :friend, :class_name => 'User'
end
#show.rb
class Show < ActiveRecord::Base
has_many :saved_shows
has_many :users, :through => :saved_shows
...
end
#saved_show.rb
class SavedShow < ActiveRecord::Base
belongs_to :user, :counter_cache => true
belongs_to :show
...
end
Update: As I've just been told on another issue, you can't nest associations more than 2 levels. That would seem to explain my problem here.
I've gotten it to work (as far as I can tell so far), with this, but it ain't pretty:
@friends_saved = SavedShow.includes(:user).where("show_id = ? AND user_id in (?)", @show, current_user.friends.map(&:id)).map(&:user)
Basically, I get a list of my friends into an array. look up which of my friends saved that show with a SQL range ('in (?)', and then since that result is actually an array of saved shows, I extract the user only (which I eager loaded) into a new array.
If there's an easier way to do this, I'd be happy to hear it.