# Need help converting SQL query to find or AR association

**URL:** <https://www.ruby-forum.com/t/need-help-converting-sql-query-to-find-or-ar-association/164218>\
**Category:** Rails\
**Created:** [March 30, 2009, 4:51am UTC](https://www.ruby-forum.com/t/need-help-converting-sql-query-to-find-or-ar-association/164218 "2009-03-30T04:51:20Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![vince](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/b5e925/32.png) [@vince](https://www.ruby-forum.com/u/vince)\
**Post date:** [March 30, 2009, 4:51am UTC](https://www.ruby-forum.com/t/need-help-converting-sql-query-to-find-or-ar-association/164218/1 "2009-03-30T04:51:20Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![vince](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/b5e925/32.png) [@vince](https://www.ruby-forum.com/u/vince)\
**Post date:** [March 30, 2009, 6:19am UTC](https://www.ruby-forum.com/t/need-help-converting-sql-query-to-find-or-ar-association/164218/2 "2009-03-30T06:19:35Z")

</div>

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’ 🙂 ) but  
hopefully it gets you in the right direction.

Stu

---

<div class="post-metadata">

**Author:** ![vince](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/b5e925/32.png) [@vince](https://www.ruby-forum.com/u/vince)\
**Post date:** [March 30, 2009, 7:54am UTC](https://www.ruby-forum.com/t/need-help-converting-sql-query-to-find-or-ar-association/164218/3 "2009-03-30T07:54:16Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![vince](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/b5e925/32.png) [@vince](https://www.ruby-forum.com/u/vince)\
**Post date:** [March 30, 2009, 10:14am UTC](https://www.ruby-forum.com/t/need-help-converting-sql-query-to-find-or-ar-association/164218/4 "2009-03-30T10:14:14Z")

</div>

On Mar 30, 6:53 am, vince [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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