From: Bill C. <sql...@ce...> - 2006-01-27 00:52:09
|
I'm just learning my way around sqlobject while preparing to use it extensively in a rewrite of an accounting system. There are many cases where it would be useful to let sql do the heavy lifting when calculating totals and such, say when computing customer balances, or invoice balances where one might have to sum over line items or payments. Given a skeleton setup: class Invoices(SQLObject): taxable DecimalCol(length=10, precision=2) balancedue DecimalCol(length=10, precision=2) customer ForeignKey('Customers') #... class Customers(SQLObject): taxable DecimalCol(length=10, precision=2) balancedue DecimalCol(length=10, precision=2) # ... I want to do a query such as: select sum(taxable) as taxable, sum(balancedue) as balancedue from invoices where invoices.customer_id = customer.id Then use the resulting sums to set taxable and balancedue in the Customers record. Is there a reasonably clean way to do this directly with sqlobject? Bill -- INTERNET: bill@Celestial.COM Bill Campbell; Celestial Systems, Inc. URL: http://www.celestial.com/ PO Box 820; 6641 E. Mercer Way FAX: (206) 232-9186 Mercer Island, WA 98040-0820; (206) 236-1676 ``Microsoft IIS has more holes than a wheel of Swiss Cheese after a shotgun blast'' -- John Dvorak |
From: Oleg B. <ph...@ma...> - 2006-01-27 08:52:58
|
On Thu, Jan 26, 2006 at 04:51:18PM -0800, Bill Campbell wrote: > select sum(taxable) as taxable, sum(balancedue) as balancedue > from invoices > where invoices.customer_id = customer.id taxable, balancedu = \ Invoice.select(Invoice.q.customerID == Customer.q.id).accumulateMany( ("SUM", Invoice.q.taxable), ("SUM", Invoice.q.balancedu) ) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |