How can I achieve a self-referencing many-to-many relationship on the SQLAlchemy ORM back referencing to the same attribute?

Here's the UNION approach I hinted at on the mailing list earlier today.

from sqlalchemy import Integer, Table, Column, ForeignKey, \    create_engine, String, selectfrom sqlalchemy.orm import Session, relationshipfrom sqlalchemy.ext.declarative import declarative_baseBase= declarative_base()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)    name = Column(String)    # this relationship is used for persistence    friends = relationship("User", secondary=friendship,                            primaryjoin=id==friendship.c.friend_a_id,                           secondaryjoin=id==friendship.c.friend_b_id,    )    def __repr__(self):        return "User(%r)" % self.name# this relationship is viewonly and selects across the union of all# friendsfriendship_union = select([                        friendship.c.friend_a_id,                         friendship.c.friend_b_id                        ]).union(                            select([                                friendship.c.friend_b_id,                                 friendship.c.friend_a_id]                            )                    ).alias()User.all_friends = relationship('User',                       secondary=friendship_union,                       primaryjoin=User.id==friendship_union.c.friend_a_id,                       secondaryjoin=User.id==friendship_union.c.friend_b_id,                       viewonly=True) e = create_engine("sqlite://",echo=True)Base.metadata.create_all(e)s = Session(e)u1, u2, u3, u4, u5 = User(name='u1'), User(name='u2'), \                    User(name='u3'), User(name='u4'), User(name='u5')u1.friends = [u2, u3]u4.friends = [u2, u5]u3.friends.append(u5)s.add_all([u1, u2, u3, u4, u5])s.commit()print u2.all_friendsprint u5.all_friends

I needed to solve this same problem and messed about quite a lot with self referential many-to-many relationship wherein I was also subclassing the User class with a Friend class and running into sqlalchemy.orm.exc.FlushError. In the end instead of creating a self referential many-to-many relationship, I created a self referential one-to-many relationship using a join table (or secondary table).

If you think about it, with self referential objects, one-to-many IS many-to-many. It solved the issue of the backref in the original question.

I also have a gisted working example if you want to see it in action. Also it looks like github formats gists containing ipython notebooks now. Neat.

friendship = Table(    'friendships', Base.metadata,    Column('user_id', Integer, ForeignKey('users.id'), index=True),    Column('friend_id', Integer, ForeignKey('users.id')),    UniqueConstraint('user_id', 'friend_id', name='unique_friendships'))class User(Base):    __tablename__ = 'users'    id = Column(Integer, primary_key=True)    name = Column(String(255))    friends = relationship('User',                           secondary=friendship,                           primaryjoin=id==friendship.c.user_id,                           secondaryjoin=id==friendship.c.friend_id)    def befriend(self, friend):        if friend not in self.friends:            self.friends.append(friend)            friend.friends.append(self)    def unfriend(self, friend):        if friend in self.friends:            self.friends.remove(friend)            friend.friends.remove(self)    def __repr__(self):        return '<User(name=|%s|)>' % self.name