need help converting SQL query to find or AR association

I have 3 models

comments(user_id, title) followings(user_id, follower_id) users(email)

I need to find all comments made by people that a user is following, which in effect is this simple SQL query:

select comments.title from comments, followings, users where users.id = followings.follower_id   and followings.user_id = comments.user_id   and users.id = 1

Can someone help? I tried this but it didn't work:

class User ...   has_many :followings

class Following ...   has_many :comments, :foreign_key => 'user_id'

script/console User.find(:first).followings (this works) Following.find(:first).comments (this works) User.find(:first).followings.comments (this does NOT work)

You might want something like:

class Comment < ActiveRecord::Base   belongs_to :user end

class Following < ActiveRecord::Base   belongs_to :follower, :class_name => 'User', :foreign_key => 'follower_id'   belongs_to :followed, :class_name => 'User', :foreign_key => 'user_id' end

class User < ActiveRecord::Base   has_many :comments   has_many :admirers, :class_name => 'Following', :foreign_key => 'user_id'   has_many :followed, :class_name => 'Following', :foreign_key => 'follower_id' end

u = User.first

Then to get an array of all comments made by other users that this user is following:

u.followings.map { |f| f.followed.comments }.flatten

There's probably a more elegant way to do this (and I'm sure you could improve on my choice of words for 'admirers' and 'followed' :slight_smile: ) but hopefully it gets you in the right direction.

Stu

Thanks Stu. The solution above works but I'm looking for a find statement that will perform a single db query (like the SQL statement I posted) versus performing multiple queries and adding them using ruby. If a user has a hundred followers wouldn't your solution perform a hundred queries?

Thanks Stu. The solution above works but I'm looking for a find statement that will perform a single db query (like the SQL statement I posted) versus performing multiple queries and adding them using ruby. If a user has a hundred followers wouldn't your solution perform a hundred queries?

Actually more like 200 :=)

One way is to just use the same joins that your raw sql had, just in a more activerecordy way eg

Comment.find :all, :joins => {:user => :followings}, :conditions => ["followings.follower_id = ?", foo]

select * from comments inner join users on users.id = comments.user_id inner join followings on followings.user_id = users.id where followings.follower_id = 1

(I may have got the order on your self referential join table back to front, but should be easy enough to fix if that is the case)

Fred