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+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
Re: complex query with virtual fields
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+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
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 >
> 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+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
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 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.
