views:

18

answers:

0

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.