[SOLVED] SQLAlchemy association table with soft delete

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 way to tell SQLAlchemy not to return associated objects if the relationship has been removed (soft deleted).

Here is an example:

class A(Base):
    __tablename__ = "a"
    id = Column(Integer, primary_key=True)
    b_list = relationship("B", secondary=lambda: association_table, backref="a_list")

class B(Base):
    __tablename__ = "b"
    id = Column(Integer, primary_key=True)

association_table = Table(
    "a_b_association",
    metadata,
    Column("a_id", Integer, ForeignKey(A.id), primary_key=True),
    Column("b_id", Integer, ForeignKey(B.id), primary_key=True),
    Column("removed", Integer)
)

The example above will always return the relationship. I need to change the relationship to only return when "removed==0".

Thanks for any help with this.

I know the SQL query I want SQLAlchemy to make is:

SELECT A.id, B.id
FROM A
INNER JOIN a_b_association ON A.id=a_b_association.a_id
INNER JOIN B ON B.id=a_b_association.b_id
WHERE a_b_association.removed=0;

Solution

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.