from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext import baked

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    bs = relationship("B")
    cs = relationship("C")


class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    ab_id = Column(ForeignKey('a.id'))


class C(Base):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)
    ac_id = Column(ForeignKey('a.id'))

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)
s.add_all([
    A(id=1, bs=[B(), B()], cs=[C(), C()]),
    A(id=2, bs=[B(), B()], cs=[C(), C()])
])
s.commit()


bakery = baked.bakery(size=3)

base_bq = bakery(lambda s: s.query(A))
base_bq += lambda q: q.options(
    subqueryload(A.bs), subqueryload(A.cs)).order_by(A.id)

for x in range(1, 2):
    if x == 1:
        bq += (lambda q: q.filter_by(id=1))
    else:
        bq += (lambda q: q.filter_by(id=2))

    a = bq(s).all()[0]

    s.close()

    for b in a.bs:
        assert b.ab_id == 1
    for c in a.cs:
        assert c.ac_id == 1

#!

Traceback (most recent call last):
  File "test.py", line 55, in <module>
    assert b.ab_id == 1
AttributeError: 'C' object has no attribute 'ab_id'