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

Question

I'm trying to implement a self-referential many-to-many relationship using declarative on SQLAlchemy.

The relationship represents friendship between two users. Online I've found (both in the documentation and Google) how to make a self-referential m2m relationship where somehow the roles are differentiated. This means that in this m2m relationships UserA is, for example, UserB's boss, so he lists him under a 'subordinates' attribute or what have you. In the same way UserB lists UserA under 'superiors'.

This constitutes no problem, because we can declare a backref to the same table in this way:

subordinates = relationship('User', backref='superiors') 

So there, of course, the 'superiors' attribute is not explicit within the class.

Anyway, here's my problem: what if I want to backref to the same attribute where I'm calling the backref? Like this:

friends = relationship('User',                        secondary=friendship, #this is the table that breaks the m2m                        primaryjoin=id==friendship.c.friend_a_id,                        secondaryjoin=id==friendship.c.friend_b_id                        backref=??????                        ) 

This makes sense, because if A befriends B the relationship roles are the same, and if I invoke B's friends I should get a list with A in it. This is the problematic code in full:

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,                            #HELP NEEDED HERE                            ) 

Sorry if this is too much text, I just want to be as explicit as I can with this. I can't seem to find any reference material to this on the web.

Answer 1

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

from sqlalchemy import Integer, Table, Column, ForeignKey, \     create_engine, String, select from sqlalchemy.orm import Session, relationship from sqlalchemy.ext.declarative import declarative_base  Base= 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 # friends 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]                             )                     ).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_friends print u5.all_friends 

Answer 2

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