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.