title: SQLAlchemy declarative many-to-many self-join via Association object
body: <p>I have a table Users and a table Friends which maps users to other users as each user can have many friends. This relation is obviously symmetric: if user A is a friend of user B then user B is also a friend of user A, I only store this relation once. The Friends table has additional fields besides the two User ID's so I have to use an association object. </p>
  
  <p>I am trying to define this relationship in declarative style in the Users class (which extends the declarative base), but I can't seem to figure out how to do this. I want to be able to access all friends of a given user via a property friends, so say friends = bob.friends.</p>
  
  <p>What's the best approach for this problem? I tried to many different setups to post here, and none of them worked for various reasons.</p>
  
  <p>EDIT: My latest attempt looks like this:</p>
  
  <pre><code>class User(Base):
      __tablename__ = 'users'
      id = Column(Integer, primary_key=True)
  
      # Relationships
      friends1 = relationship('Friends', primaryjoin=lambda: id==Friends.friend1ID)
      friends2 = relationship('Friends', primaryjoin=lambda: id==Friends.friend2ID)
  
  
  class Friends(Base):
      __tablename__ = 'friends'
      id = Column(Integer, primary_key=True)
      friend1ID = Column(Integer, ForeignKey('users.id') )
      friend2ID = Column(Integer, ForeignKey('users.id') )
      status = Column(Integer)
  
      # Relationships
      vriend1 = relationship('Student', primaryjoin=student2ID==Student.id)
      vriend2 = relationship('Student', primaryjoin=student1ID==Student.id)
  </code></pre>
  
  <p>This however results in the following error:</p>
  
  <pre><code>InvalidRequestError: Table 'users' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.
  </code></pre>
  
  <p>I must admit that at this point I am thoroughly confused because of many failed attempts and might have made more than one stupid mistake in the above.</p>