Python SQLAlchemy joinedload filter parameters

Question

I'm trying to make a left outer join relation between two tables using joinedload but it doesn't seems to have the same behavior as using outerjoin method.

Here is an example using outerjoin:

stmt = select(ServerFarm)
stmt = stmt.outerjoin(
  Server,
  (ServerFarm.name == Server.server_farm_id) & (Server.deleted == False)
)
stmt = stmt.filter(ServerFarm.name == name)

print(stmt)
# SELECT ... FROM server_farm
# LEFT JOIN server ON server_farm.name = server.server_farm_id and server.deleted = False
# WHERE server_farm.name = :name

So when I'm using joinedload this is what happens:

stmt = select(ServerFarm)
stmt = stmt.options(
  joinedload(
    ServerFarm.servers,
    (ServerFarm.name == Server.server_farm_id) & (Server.deleted == False)
  )
)
stmt = stmt.filter(ServerFarm.name == name)
# SELECT ... FROM server_farm
# LEFT JOIN server ON server_farm.name = server.server_farm_id
# WHERE server_farm.name = :name

On this case, it just ignores the (Server.deleted == False) option. What am I doing wrong? Is there a way to add a "custom" filter on a left outer join using joinedload? How?

Python SQLAlchemy joinedload filter parameters

0 Answers