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 received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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
On Mar 29, 7:50 pm, vince
<stha...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> 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 received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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? On Mar 30, 9:18 am, shenry <stuarthe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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 > > On Mar 29, 7:50 pm, vince <stha...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > 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 received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2009-Mar-30 08:13 UTC
Re: need help converting SQL query to find or AR association
On Mar 30, 6:53 am, vince <stha...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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> On Mar 30, 9:18 am, shenry <stuarthe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > 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 > > > On Mar 29, 7:50 pm, vince <stha...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > 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 received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---