SQLAlchemy Association Table With Soft Delete

I'm trying to configure my SQLAlchemy models to use an association table for a relationship. The issue I have is that the association table has a column 'removed'. I can't find a w

Solution 1:

I think you can use primaryjoin and secondaryjoin property of relationship obj and then add the condition to filter only non-deleted row.

class A(Base):
    __tablename__ = "a"
    id = Column(Integer, primary_key=True)
    b_list = relationship(
        "B", 
        secondary=association_table,
        primaryjoin=(association_table.c.a_id == id and association_table.removed == 0)
        backref="a_list"
    )

For ref, you can read more in docs.