嵌套连接子查询的SQLAlchemy计数函数

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    orders = relationship("Order", back_populates="user")

class Order(Base):
    __tablename__ = 'orders'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship("User", back_populates="orders")

engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# 假设已经有一些数据插入到这两个表中

# 使用子查询来计算每个用户的订单数量
subquery = (
    session.query(Order.user_id, func.count('*').label('order_count'))
    .group_by(Order.user_id)
    .subquery()
)

# 将子查询连接到User表，并选择用户及其订单数量
result = (
    session.query(User, subquery.c.order_count)
    .outerjoin(subquery, User.id == subquery.c.user_id)
    .all()
)

for user, order_count in result:
    print(f"User {user.name} has {order_count} orders.")