I was going to suggest replacing the balance_due in the 2nd query with the 1st complex query, but it looks like that would become pretty messy.
So, why not just add a balance column to the invoices table and update it from the model? On Fri, Oct 26, 2012 at 6:05 PM, fly2279 <kennethf...@gmail.com> wrote: > 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+unsubscr...@googlegroups.com. > Visit this group at http://groups.google.com/group/cake-php?hl=en. > > -- 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+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/cake-php?hl=en.