Self-referencing many-to-many with the same backref
Marco Flores
subject, here's a snippet of my code:
friendship = Table(
'friendships', Base.metadata,
Column('friend_a_id', Integer, ForeignKey('users.id'),
primary_key=True),
Column('friend_b_id', Integer, ForeignKey('users.id'),
primary_key=True)
)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
friends = relationship('User',
secondary=friendship,
primaryjoin=id==friendship.c.friend_a_id,
secondaryjoin=id==friendship.c.friend_b_id,
)
Like I said in the subject, this backref (or back_populate) the
friends attribute on the other end of the relationship as well. So far
every example I've seen backrefs to a differently named attribute,
which can be accomplished with backref='any_name_other_than_this_one'.
But I actually want backref='friends'.
Have you any ideas?
Thanks a lot in advance, this has me pulling my hair off.
Michael Bayer
"User.friends" is an attribute that can only be associated with one relationship() at a time (a backref() is a second relationship()). A relationship can only join across the "secondary" table in terms of "parent" to "child" in one way also, so one of friend_a_id or friend_b_id can be on one side of the join at a time.
What you're looking for here is the union of linkages between User->User going from friend_a_id to friend_b_id, and User->User going from friend_b_id to friend_a_id. At the SQL level you'd ideally be using UNION to do this.
So here you can do one of three things:
class User(Base):
# ...
_friends = relationship("User", secondary=friendship, <join conditions>,
backref=backref("_r_friends", collection_class=set),
collection_class=set)
@property
def friends(self):
return self._friends.union(self._r_friends)
that's the simplest way, though not necessarily the most efficient, depending on how the collections are used. It is also directly writable.
Or a variant which does it at the SQL level when you ask for "friends":
class User(Base):
# ...
_friends = # same thing ...
@property
def friends(self):
return object_session(self).\
query(User).\
with_parent(self, "_friends").\
union(
object_session(self).query(User).\
with_parent(self, "_r_friends")
).all()
Then *maybe* you can do this, which would be the slickest of all:
friendship_union = select([friendship.c.friend_a_id, friendship.c.friend_b_id]).\
union(select([friendship.c.friend_b_id, friendship.c.friend_a_id]))
class User(Base):
# ...
friends = relationship("User", secondary=friendship_union,
primaryjoin=id=friendship_union.c.a_id,
secondaryjoin=id=friendship_union.c.b_id,
viewonly=True,
collection_class=set)
That third option should work in theory. It's viewonly because SQLAlchemy can't write to the "union", it wouldn't know how to organize the data within the "friendship" table. So the first option includes that it is directly writable.