Как выполнить подзапрос в SQLAlchemy?

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

from sqlalchemy.orm import subqueryload

sub_query = session.query(OrderItem.product_id, func.count(OrderItem.id).label('num_orders')).group_by(OrderItem.product_id).subquery()

query = session.query(Product).outerjoin(
    sub_query,
    Product.id == sub_query.c.product_id,
).options(subqueryload(Product.order_items))

for product in query:
    print(product.name, product.order_items[0].num_orders if product.order_items else 0)