如何在sqlalchemy中使用带有筛选器的连接表上的聚合函数？

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

# 创建数据库引擎和会话
engine = create_engine('mysql+pymysql://username:password@localhost/database')
Session = sessionmaker(bind=engine)
session = Session()

# 创建基类
Base = declarative_base()

# 定义模型类
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    orders = relationship('Order')

class Order(Base):
    __tablename__ = 'orders'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    amount = Column(Integer)

# 查询用户的订单总金额
total_amount = session.query(func.sum(Order.amount)).\
    join(User).\
    filter(User.name == 'John').\
    scalar()

print(f"John's total order amount: {total_amount}")