Load Only Subset Of Joined Rows In Sqlalchemy Orm

I have following mapped classes defined class A(Base): __tablename__ = 'a' id = sqla.Column(sqla.Integer, primary_key = True) number_a = sqla.Column(sqla.Integer) b

Solution 1:

Do this using contains_eager, but be aware that you are tricking SQL Alchemy, so do not reuse this UnitOfWork to do the regular relationship related tasks:

result = (
    session.query(A)
    .join(B)
    .filter(B.number_b == 51)
    .options(contains_eager(A.b_collection))  # this is the key
    ).all()