How to load nested relationships in SQLAlchemy?

Question

On my Pyramid + SQLAlchemy website, I want the customer to view all their purchases. There is a lot of PurchaseOrder in the Purchase, and PurchaseOrder has a lot of PurchaseOrderDetail.

I want to optimize the entire purchase context (including orders and details), and therefore I am looking at SQLAlchemy loading strategies.

My model declaration is as follows:

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 I would like 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:

• How can I load PurchaseOrderDetails when I request a purchase model?
• Is this the best way to get an entire purchase connection?

Thank you in advance

Answer 1

Changing the subqueryload_all(...) your query to one of the following two parameters will complete the task:

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

The documentation on sqlalchemy.orm.subqueryload_all pretty clear in the examples given.

Answer 2

My statements, since I often do not comment on Stack Overflow, so I'm not sure if this is the place where I should post.

FYI, sqlalchemy.orm.subqueryload_all is deprecated (since version 0.9.0).

Now they want you to use a chain of methods.

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