Soft Deletion in Joined Subqueries

[deep110]

• SQLAlchemy Easy Soft-Delete version: v0.8.3 i.e Latest
• Python version: 3.11.0
• Operating System: MacOS

Description

Simple Joins are working as expected, but joins with a subquery is not working.

What I Did

class Client(BaseModel):
    __tablename__ = "clients"

    name = Column(String, nullable=False, unique=True)

    agents = relationship("Agent", back_populates="client")
    
class Agent(BaseModel):
    __tablename__ = "agents"

    name = Column(String, nullable=False)
    client_id = Column(Integer, ForeignKey("clients.id"), nullable=True)

    client = relationship("Client", back_populates="agents")

I am trying to jointly load clients with agents:

client_with_agents = db.query(Client).options(joinedload(Client.agents)).first()

There are some deleted agents that are getting included. Did some debugging in the below function SubQuery type is not handled.

def rewrite_from_orm_join(self, stmt: Select, join_obj: Union[_ORMJoin, Join]) -> Select:
        """Handle multiple, and potentially recursive joins."""

        # Recursive cases (multiple joins)
        if isinstance(join_obj.left, _ORMJoin) or isinstance(join_obj.left, Join):
            stmt = self.rewrite_from_orm_join(stmt, join_obj.left)

        if isinstance(join_obj.right, _ORMJoin) or isinstance(join_obj.right, Join):
            stmt = self.rewrite_from_orm_join(stmt, join_obj.right)

        # Normal cases - Tables
        if isinstance(join_obj.left, Table):
            stmt = self.rewrite_from_table(stmt, join_obj.left)

        if isinstance(join_obj.right, Table):
            stmt = self.rewrite_from_table(stmt, join_obj.right)

        return stmt