SQLAlchemy Relationship Join with Multiple Foreign Keys

Question

Using SQLAlchemy, I'm having trouble figuring out how to map the following relation: a user can be 'followed' by other users, thus becoming a 'leader' with 'followers'.

class User( Base ):
    __tablename__ = 'users'
    id = Column( Integer, primary_key = True )
    followers = relationship( 'User', primaryjoin = 'and_( User.id == UserFollower.leader_id )', cascade = 'all' )
    leaders = relationship( 'User', primaryjoin = 'and_( User.id == UserFollower.follower_id )', cascade = 'all' )

class UserFollower( Base ):
    __tablename__ = 'users_followers'
    leader_id = Column( Integer, ForeignKey( 'users.id' ), primary_key = True )
    follower_id = Column( Integer, ForeignKey( 'users.id' ), primary_key = True )
    follower = relationship( 'User', uselist = False, foreign_keys = [ follower_id ] )
    leader = relationship( 'User', uselist = False, foreign_keys = [ leader_id ] )

From the documentation I was under the impression that such a declaration would have

some_user.followers

perform a join of the 'users' and 'users_followers' tables on 'users.id = users_followers.leader_id' where 'user.id = some_user.id'.

However, this yields an error (similar to How to specify table relationships in SQLAlchemy with multi-level/multiple joins?). Although the proposed solution allows me to work around this issue, it prevents the use of the 'cascading' property of SQLAlchemy's relationships so that when deleting a User, the corresponding UserFollower objects are also deleted.

In summary: how can I have relationships joined to a table with multiple foreign keys referring to the same key and still preserve cascading (deletion)?

Answer 1

If you read the Self-Referential Many-to-Many Relationship section of SQLAlchemy documentation, you will realise that you have a classical many-to-many relationship. If you define this relation according to the documentation, the cascades will work just fine:

users_followers = Table(
    'users_followers', Base.metadata,
    Column('leader_id', ForeignKey('users.id'), primary_key=True),
    Column('follower_id', ForeignKey('users.id'), primary_key=True),
)


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    followers = relationship(
        'User',
        secondary=users_followers,
        primaryjoin=id == users_followers.c.leader_id,
        secondaryjoin=id == users_followers.c.follower_id,
        backref="leaders",
        cascade='all',
    )