Load only subset of joined rows in SQLAlchemy ORM

python, sqlalchemy

Solution

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()

Problem

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_collection = relationship('B', backref = backref('a')) class B(Base): __tablename__ = "b" id = sqla.Column(sqla.Integer, primary_key = True) id_a = sqla.Column(sqla.Integer, sqla.ForeignKey('a.id')) number_b = sqla.Column(sqla.Integer) ``` There are these objects stored in the DB: ``` a1 = A(number_a = 1) a2 = A(number_a = 2) b1 = B(number_b = 50, a = a2) b2 = B(number_b = 51, a = a2) ``` What I need is to query the relationship by issuing one query only so that it preloads `b_collection` by only results of that query: ``` result = session.query(A).join(B).filter(B.number_b == 51).all() ``` So now I would like `result[0].b_collection` to respect the filtering expression and do not include `B` instance `with number_b = 50`. So my expected output of: ``` for a in result: for b in a.b_collection: print(b.number_b) ``` would be: ``` 51 ``` However iterating throught `a.b_collection` allways issues a new query and loads both `B` objects into `b_collection`, so that the result is ``` 50 51 ``` Which is what I do not want to get. How to enforce the original query to load all objects and populate the relationship collection according to the given filtering criteria? Thank you for your responses.

Original source