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.
On Thu, Mar 15, 2012 at 5:30 PM, Michael Bayer <[email protected]> wrote:
> I had an answer for pretty much the same question here:
> https://groups.google.com/forum/?fromgroups#!searchin/sqlalchemy/friends/sqlalchemy/tOrvG98TLNw/bwFM82-SwXwJ
>
>
> On Mar 15, 2012, at 5:08 PM, zz elle wrote:
>
> 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)
>
> On Thursday, March 15, 2012 1:03:27 AM UTC+1, Jon Parise wrote:
>>
>> 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.
>>
>>
>>
>>
>>
>
> On Thursday, March 15, 2012 1:03:27 AM UTC+1, Jon Parise wrote:
>>
>> 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.
>>
>>
>>
>>
>>
>
> --
> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" 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/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" 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/sqlalchemy?hl=en.
