# Symmetric relationships **URL:** \ **Category:** Rails\ **Created:** [June 10, 2007, 9:13pm UTC](https://www.ruby-forum.com/t/symmetric-relationships/106458 "2007-06-10T21:13:00Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Chef\_vom\_Dienst](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ecc23a/32.png) [@Chef\_vom\_Dienst](https://www.ruby-forum.com/u/Chef_vom_Dienst)\ **Post date:** [June 10, 2007, 9:13pm UTC](https://www.ruby-forum.com/t/symmetric-relationships/106458/1 "2007-06-10T21:13:00Z")
Hi, I’m building an application where you can have friends like in myspace. Only if two people agree to be friends, a friendship relation should be established. If they agreed, it should be an undirected (aka symmetric, bidirectional) graph. I don’t want to keep two entries for each friendship like in the following table: table friendships parent | child user1 | user2 user2 | user1 I hope you understand what I mean. I want that if anybody of the two friends quit friendship, the relationship between the two also gets deleted. Thanks. ---
**Author:** ![Chef\_vom\_Dienst](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ecc23a/32.png) [@Chef\_vom\_Dienst](https://www.ruby-forum.com/u/Chef_vom_Dienst)\ **Post date:** [June 11, 2007, 5:45pm UTC](https://www.ruby-forum.com/t/symmetric-relationships/106458/2 "2007-06-11T17:45:01Z")
What type of queries do you need to be able to do after this relationship is set up? For example Do Pam and Jeff have a friendship? List all the friends of Pam? List all the people who are not friends of Pam or Jeff? The best solution will depend on how you want to get to the data. steve ---
**Author:** ![Chef\_vom\_Dienst](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ecc23a/32.png) [@Chef\_vom\_Dienst](https://www.ruby-forum.com/u/Chef_vom_Dienst)\ **Post date:** [June 11, 2007, 8:44pm UTC](https://www.ruby-forum.com/t/symmetric-relationships/106458/3 "2007-06-11T20:44:12Z")
Most basically I just want to query the friends of Pam. If Pam and Jeff are friends, i want to get Pam as friend of Jeff and Jeff as friend of Pam. For this I would have to keep two database entries for each friendship, like in the following example: user\_id | friend\_id id\_of\_pam | id\_of\_jeff id\_of\_jeff | id\_of\_pam I’d like to do it with one, but I don’t think there is an elegant solution, or is it? A wish of mine also would be to get the shortest connection between person A and person B, like in [xing.com](http://xing.com) (single pair shortest path problem in graph theory), but I have no idea how to do that, and it’s not a necessity. Etherway, not having to keep two entries for each friendship (then you had also to monitor when a friendship gets deleted to also delete the other way round) would be favorable for this problem too. On Jun 11, 5:45 pm, Steve R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![Chef\_vom\_Dienst](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ecc23a/32.png) [@Chef\_vom\_Dienst](https://www.ruby-forum.com/u/Chef_vom_Dienst)\ **Post date:** [June 11, 2007, 11:46pm UTC](https://www.ruby-forum.com/t/symmetric-relationships/106458/4 "2007-06-11T23:46:54Z")
Thanks for your advice, but unfortunately I would also like to select all friends of Pam at once. On Jun 11, 10:36 pm, Steve R. \ **Author:** ![Chef\_vom\_Dienst](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ecc23a/32.png) [@Chef\_vom\_Dienst](https://www.ruby-forum.com/u/Chef_vom_Dienst)\ **Post date:** [June 11, 2007, 10:36pm UTC](https://www.ruby-forum.com/t/symmetric-relationships/106458/5 "2007-06-11T22:36:00Z") > Most basically I just want to query the friends of Pam. > If Pam and Jeff are friends, i want to get > Pam as friend of Jeff > and Jeff as friend of Pam. If you only want to be able to say is Pam a friend of Jeff then you could assign each user a unique prime number and store the multiple of the primes in a separate ‘friendship’ table. But that won’t help you list all the friends of Pam (unless you don’t mind iterating over every entry in the table). So, what exact queries do you want to be able to do? steve ---
**Author:** ![Chef\_vom\_Dienst](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ecc23a/32.png) [@Chef\_vom\_Dienst](https://www.ruby-forum.com/u/Chef_vom_Dienst)\ **Post date:** [June 12, 2007, 1:31pm UTC](https://www.ruby-forum.com/t/symmetric-relationships/106458/6 "2007-06-12T13:31:02Z")
> Thanks for your advice, but unfortunately I would also like to select > all friends of Pam at once. I can’t think of a way of doing this with only one table entry. I’m surprised because I thought I would be able to. It’s an interesting problem and I’ll pass it on to some colleagues. steve ---
**Author:** ![Chef\_vom\_Dienst](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ecc23a/32.png) [@Chef\_vom\_Dienst](https://www.ruby-forum.com/u/Chef_vom_Dienst)\ **Post date:** [June 17, 2007, 2:34pm UTC](https://www.ruby-forum.com/t/symmetric-relationships/106458/7 "2007-06-17T14:34:33Z")
No progress I’m afraid. You are aware that what you want is handled by Rails using has\_and\_belongs\_to\_many relationships? But it does use two entries in a table for each relationship, you just don’t have to actually handle the table yourself. steve ---
**Author:** ![Chef\_vom\_Dienst](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ecc23a/32.png) [@Chef\_vom\_Dienst](https://www.ruby-forum.com/u/Chef_vom_Dienst)\ **Post date:** [June 26, 2007, 12:38am UTC](https://www.ruby-forum.com/t/symmetric-relationships/106458/8 "2007-06-26T00:38:55Z")
Steve R. wrote: > I did this in the following way (YMMV): class Person \<\< AR::B has\_many :relation\_tos, :class\_name =\> ‘Relationship’, :foreign\_key =\> ‘related\_from’ has\_many :relation\_froms, :class\_name =\> ‘Relationship’, :foreign\_key =\> ‘related\_to’ has\_many :related\_tos, :through =\> :relation\_tos, :source =\> :relatee, :select =\> “modtypes._, relationships.relationship\_type, relationships.notes" # This gets the key join table details as well has\_many :related\_froms, :through =\> :relation\_froms, :source =\> :relator, :select =\> "modtypes._, relationships.relationship\_type, relationships.notes” # This gets the key join table details as well def related\_types related\_froms.find(:all) + related\_tos.find(:all) end The downside is, there’s two db queries to get the related people. I did this ages ago, in my early days on Rails, so haven’t really looked to see whether it can be improved. Hope this helps Chris p.s. It’s not really a person class, since that sort of relationship isn’t reflexive – e.g. think of uncle-nephew relationship. ## – [http://autopendium.com](http://autopendium.com) Stuff about old cars ---
**Author:** ![Chef\_vom\_Dienst](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/ecc23a/32.png) [@Chef\_vom\_Dienst](https://www.ruby-forum.com/u/Chef_vom_Dienst)\ **Post date:** [July 4, 2007, 2:17am UTC](https://www.ruby-forum.com/t/symmetric-relationships/106458/9 "2007-07-04T02:17:54Z")
How about using sql? avoiding habtm or hm :through? so given a table table friends int inviter\_id int Invitee\_id bool accepted And only one entry per friendship. then find all friends using… find\_by\_sql( ‘SELECT p.\* FROM people p, friends f WHERE f.accepted = ‘t’ AND ( (f.invitee\_id = #{id} AND p.id = f.inviter\_id) OR (f.inviter\_id = #{id} AND p.id = f.invitee\_id) )’ ) I haven’t tested this, but seems it should work in both directions.