How to load nested relationships in SQLAlchemy?

Question

On my Pyramid+SQLAlchemy website I want a customer to view all purchases he has placed. A Purchase has many PurchaseOrder and an PurchaseOrder has many PurchaseOrderDetail.

I want to get all the Purchase context (including orders and details) in an optimized way and therefore I'm looking at SQLAlchemy loading strategies.

My model declaration looks like this:

class Purchase(Base):
    __tablename__ = 'purchase'
    __table_args__ = {'schema':'db','autoload':True}

    customer = relationship(Customer)
    billing_address = relationship(Address,primaryjoin="Address.AddressId==Purchase.BillingAddressId")
    shipping_address = relationship(Address,primaryjoin="Address.AddressId==Purchase.ShippingAddressId")
    orders = relationship(PurchaseOrder)

class PurchaseOrder(Base):
    __tablename__ = 'purchase_order'
    __table_args__ = {'schema':'db','autoload':True}

    company = relationship(Company)
    delivery_service = relationship(DeliveryService)
    details = relationship(PurchaseOrderDetail)

class PurchaseOrderDetail(Base):
    __tablename__ = 'purchase_order_detail'
    __table_args__ = {'schema':'db','autoload':True}

    product_variant = relationship(ProductVariant)

And what I would like is something in this form:

    db_session = DBSession()
    p = db_session.query(Purchase).\
        options(joinedload_all(Purchase.customer,
                                Purchase.billing_address,
                                Purchase.shipping_address)
                ,subqueryload_all(Purchase.orders,
                                Purchase.orders.details)).all()

However, the Purchase.orders.details part is not allowed and raises the following exception:

Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "C:\apps\pyramid\lib\site-packages\sqlalchemy\orm\attributes.py", line 139, in __getattr__
    key)
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'details'

So, my questions are:

1. How can I load the PurchaseOrderDetails when querying the Purchase model?
2. Is this the best way to get all the Purchase conext?

Thanks in advance

Answer 1

The subqueryload_all function is deprecated since version 0.9.

Use method chaining with subqueryload instead:

from sqlalchemy.orm import subqueryload

session.query(MyClass).options(
    subqueryload("someattribute").subqueryload("anotherattribute")
)

Answer 2

Changing the subqueryload_all(...) part of your query to one of the two following options will do the job:

# option-1:
subqueryload_all(
    'orders.details', # @note: this will load both *orders* and their *details*
    )

# option-2:
subqueryload_all(
    Purchase.orders,       # @note: this will load orders
    PurchaseOrder.details, # @note: this will load orders' details
    )

Documentation on sqlalchemy.orm.subqueryload_all is pretty clear on this in the examples listed.