Symmetric relationships

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.

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 (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.

Thanks for your advice, but unfortunately I would also like to select all friends of Pam at once.

Steve Rawlinson wrote:

  

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

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.

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.