complex query with virtual fields

I am trying to get an accounts aging report to work correctly. I want to find all customers that have invoices with a balance within a date range.

Invoice hasMany Transaction - the transactions are payments made on that invoice, I have a virtual field in the Invoice model that takes the total invoice minus the sum of the transactions that belong to that invoice. The 'balance_due' virtual field in the Invoice model has this sql 'SELECT IF(SUM(amount) IS NOT NULL, Invoice.total_due - SUM(amount), Invoice.total_due) FROM transactions WHERE transactions.invoice_id = Invoice.id'.

In my Customer model that hasMany Invoice I want to retrieve all the customers and have a virtual field that has the total amount due based on a date. What I have for my 'aging' virtual field in the Customer model is: 'SELECT IF(SUM(balance_due) IS NOT NULL, SUM(balance_due), 0) FROM invoices WHERE invoices.balance_due > 0 AND invoices.due_date > \''.date('Y-m-d', strtotime('31 days ago')).'\' AND invoices.customer_id = Customer.id'

The customer's virtual field would work correctly if it was using a real column in the invoices table. How do I change the sql string to replace balance_due with something that will look like the result in the Invoice model? Do I need some kind of join or subquery?

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
 
---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com.
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.