I think this might work, assuming you have two users with ids 2, 7: Friendship.find(:all, :select => 'distinct friend_id', :conditions => 'friend_id in (select friend_id from friendships where user_id in (2,7) )')
Of course, you could also do this in Ruby: user2.friendships.map(:&friend_id) | user7.friendships.map(:&friend_id) #> gives you the union of the two users friendships Can't say which of these two is faster. My guess is that with many records both will be slow. On Tue, Mar 10, 2009 at 10:10 PM, moritz <[email protected]> wrote: > > I would like to show mutual friendships using a join between two > friendship tables, using something like > > select o.friend_id > from friendships o > join friendships i > on i.friend_id = o.friend_id and ...; > > What is the active record way of doing this? > > Translating it into > > Friendship.find(:all, > :select => 'f.friend_id', > :conditions => 'friend_id = f.friend_id', > :joins => 'INNER JOIN friendships f') > > fails for the obvious reasons: > > Mysql::Error: Column 'friend_id' in where clause is ambiguous: SELECT > f.friend_id FROM `friendships` INNER JOIN friendships f WHERE > (friend_id = f.friend_id) > > Is there a way to alias the first friendships table? Or is there a > better, more rubyish way of doing it? I would like to refrain from > creating a association table for it. Thanks in advance. > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CommunityEngine" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/communityengine?hl=en -~----------~----~----~----~------~----~------~--~---
