Bidirectional, self-referential association table

[Jon Parise]

I'm considering modeling many-to-many "friend" relationships between
users using an association table. The tricky aspect is that I'd like
the association table's entries to be "bidirectional", meaning the
single entry (A,B) represents friendship in both directions.

The "standard" way to model this doesn't support that usage:

friendship_table = Table('friendship',
Column('user_id', Integer, ForeignKey('user.id')),
Column('friend_id', Integer, ForeignKey('user.id'))
)

class User(Base):
id = Column(db.Integer, primary_key=True)
friends = db.relationship('User',
secondary=friendship_table,
primaryjoin=id==friendship_table.c.user_id,
secondaryjoin=id==friendship_table.c.friend_id)

... and I understand why it can't work as-is. My question is whether
or not this type of relationship is possible to represent using
relationship() (perhaps by using association_proxy()?).

For the time being, I've just been writing per-operation queries in
User instance methods and @property descriptors, but it would be nice
to wrap this up in an proper attributed object that plays nicely with
the session.

[zz elle]

I transform it into declarative way and its seems to work but it's not bidirectional.
To support bidirectional friendship(A, B friends) i would say that you might transform it into 2 unidirectional friendships (A friend of B and B friend of A).
You could perhaps hide this complexity with an association_proxy which creator_factory creates both both friendships

class User(Mixin, BASE):
    id = Column(Integer, primary_key=True)
    __tablename__ = 'user'
    friends = None

class FriendShip(Mixin, BASE):
    __tablename__ = 'friendship'
    user_id = Column(Integer, ForeignKey(User.id))
    friend_id = Column(Integer, ForeignKey(User.id))

User.friends = relationship(User, secondary='friendship',
                                               primaryjoin=User.id==FriendShip.user_id,
                                               secondaryjoin=User.id==FriendShip.friend_id)

[Michael Bayer]

I had an answer for pretty much the same question here:  https://groups.google.com/forum/?fromgroups#!searchin/sqlalchemy/friends/sqlalchemy/tOrvG98TLNw/bwFM82-SwXwJ

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/f5ydw1T4gloJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Jon Parise]

Thanks for the helpful responses.

I decided to change my approach and insert two unidirectional entries
for each friendship (one in each direction). This makes things a lot
clearer, and it will make the table easier to partition should the
need arise.

Specifically, I have a 'friendships' relationship on my User class
that references all of the user's friendships. I also added a
'friends' association_proxy to give me easier access to the friends'
User objects.

# The set of this user's confirmed friendships.
friendships = relationship('Friendship', collection_class=set,
primaryjoin='User.id==Friendship.user_id', lazy='select',
cascade='all, delete-orphan', single_parent=True)

# The set of this user's friends (derived from our `friendships` set).
friends = association_proxy('friendships', 'friend',
creator=lambda friend: Friendship(friend=friend))

I also added a few event listeners that keep the bidirectional
relationships in sync. For example, when a new Friendship is added,
the event lister notices and adds the reciprocal Friendship (and
vice-versa on deletes).

This approach is working well for me so far. Suggestions for further
improvement are of course welcome.