Re: complex query with virtual fields

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 <kennethfrey@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+unsubscribe@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+unsubscribe@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.