如何连接三张表并执行func.sum

Question

我想知道怎么把三个表，客户（Clients）、订单（Orders）和存款（Deposits）连接起来，并对每个客户的订单总额（Orders.total）和存款总额（Deposits.total）进行求和。查询的结果应该包含客户的邮箱（Clients.email）、订单总额的和和存款总额的和。

到目前为止，我尝试了不同的查询方式，比如：

listeclients = db.session.query(Clients,func.sum(Clients.orders.total).\
    label("ctotal"),func.sum((Clients.deposits.total).\
    label("dtotal"))).group_by(Client.id).all()

但是我得到了不同的错误，比如：

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Clients.orders has an attribute 'total'

我想看看在sqlalchemy中怎么做，但我也愿意接受一些关于这个查询逻辑的提示……

我的映射设置正确吗？这样的连接语法是什么？我需要在某个地方使用eagerload吗？我之前在简单查询上有过成功，但像这样的查询对我来说有点难！任何帮助都很欢迎，哪怕只是原始SQL的逻辑。我在这方面卡住了……

class Clients(db.Model):
    __tablename__ = 'clients'    
    id = db.Column(db.Integer, primary_key = True)
    email = db.Column(db.String(60), index = True, unique = True)
    adresse = db.Column(db.String(64), index = True)
    telephone = db.Column(db.String(10), index = True)
    confirmed = db.Column(db.Boolean, default = False)
    orders = db.relationship('Orders')
    deposits = db.relationship('Deposits') 

class Orders(db.Model):
    __tablename__ = 'orders'    
    id = db.Column(db.Integer, primary_key = True)
    client_id = db.Column(db.Integer, db.ForeignKey('clients.id'))
    total = db.Column(db.Float)
    date = db.Column(db.DateTime, index = True, default=datetime.now)    
    client = db.relationship('Clients')

class Deposits(db.Model):
    __tablename__='deposits'
    id = db.Column(db.Integer, primary_key = True)
    date = db.Column(db.DateTime, index = True, default=datetime.now)    
    client_id = db.Column(db.Integer, db.ForeignKey('clients.id'))
    total = db.Column(db.Float)  
    cheque = db.Column(db.Boolean)
    client = db.relationship('Clients')

Answer 1

在SQL中，这个过程很简单：

select c.email, sum(o.total), sum(d.total)
from Clients c
left join Orders o
    on ...
left join Deposits d
    on ...
group by c.email

Answer 2

更新：下面的查询已经更新，以正确处理 sum：

sq1 = (db.session.query(Orders.client_id, func.sum(Orders.total).label("ctotal"))
        .group_by(Orders.client_id)).subquery("sub1")

sq2 = (db.session.query(Deposits.client_id, func.sum(Deposits.total).label("dtotal"))
        .group_by(Deposits.client_id)).subquery("sub2")

q = (db.session.query(Clients, sq1.c.ctotal, sq2.c.dtotal)
    .outerjoin(sq1, sq1.c.client_id == Clients.id)
    .outerjoin(sq2, sq2.c.client_id == Clients.id)
    )

---

另外，您可以使用 backref 来避免重复定义关系（在某些版本的 sqlalchemy 中，这样做可能会失败）：

class Clients(db.Model):
    orders = db.relationship('Orders', backref='client')
    deposits = db.relationship('Deposits', backref='client') 

class Orders(db.Model):
    # client = db.relationship('Clients')

class Deposits(db.Model):
    # client = db.relationship('Clients')