python - How to join 3 tables and perform func.sum -

how join 3 tables, clients, orders , deposits , perform func.sum on orders.total , deposits.total each client.id exists in database ? query result should include columns clients.email, func.sum(orders.total) , func.sum(deposits.total).

so far, i've tried different queries along lines of :

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

giving me different errors such as:

attributeerror: neither 'instrumentedattribute' object nor 'comparator' object associated clients.orders has attribute 'total' 

i see how 1 in sqlalchemy, i'd settle hints behind logic of such query ...

are mappings correct? syntax of such join? should use eagerload somewhere? i've had success simpler queries, 1 on head ! welcome, logic of in raw sql. i'm stuck w/ ...

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

update: updated query below handle 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)     ) 

---

also, instead of defining relationships twice (which might fail on versions of sqlalchemy), can use backref:

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