Need help converting SQL query to find or AR association

Rails
1

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)

2

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

3

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?

4

On Mar 30, 6:53 am, vince [email protected] wrote:

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