Re: complex query with virtual fields

Thanks Geoff for the idea. I got it working by adding an extra SUM() and retrieving invoices instead of customers. Now my virtual field in the Invoice model looks like:

'SUM(IF(Invoice.due_date > \''.date('Y-m-d', strtotime('31 days ago')).'\', (SELECT IF(SUM(amount) IS NOT NULL, Invoice.total_due - SUM(amount), Invoice.total_due) FROM transactions WHERE transactions.invoice_id = Invoice.id), 0))'

It's definitely not very clean but it works. 

On Saturday, October 27, 2012 9:57:19 AM UTC-5, Geoff Douglas wrote:

fly2279,

In order to get the aging for the customers, you need to use the data from the invoices model. You need to select the invoices, Cake will join in the customer data because of the belongsTo relationship, then you group on customer, summing up the various fields that you want.

If I where you I would do a join of a sub-select of the total payments, into an invoices select grouped by customer.

• Select Invoices
• join Customer (automatically done by cake)
• join sub on TotalPayments.invoice_id = Invoice.id
• select transactions as TotalPayments
• group by invoice
• group by customer

Does this make sense?

Happy Coding.

Geoff

On Friday, October 26, 2012 3:05:09 PM UTC-7, fly2279 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+unsubscribe@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.