Re: complex query with virtual fields

I don't save a column in the invoices table because when an invoice gets saved there are sometimes discounts that get saved with them, if you try to calculate the balance based on payments and discounts in the afterSave method, you can't determine the balance due because the related data hasn't been saved yet.

On Friday, October 26, 2012 6:46:29 PM UTC-5, cricket wrote:

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 <kenne...@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...@googlegroups.com.
> To unsubscribe from this group, send email to
> cake-php+u...@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.