Great, thanks for the tips - I will try them out and see how I get on.
Jamie
On Apr 2, 2:07 pm, "Chris Hartjes" <[EMAIL PROTECTED]> wrote:
> On Sun, Mar 30, 2008 at 3:51 PM,acoustic_overdrive
>
>
>
> <[EMAIL PROTECTED]> wrote:
>
> > What's the recommended way to use aggregate functions like SUM()?
>
> > I'm building an accounting application that so far has three models:
>
> > - Client hasMany Transaction
> > - Transaction hasMany TransactionItem, belongsTo Client
> > - TransactionItem belongsTo Transaction
>
> > The total value of a Transaction (an invoice or receipt) is the sum of
> > its related TransactionItems, which each have a monetary value.
>
> > When listing transactions I also want to know the total value of each
> > and so I could do this with some SQL like this:
>
> > SELECT
> > SUM(TransactionItem.amount),
> > Transaction.title
> > FROM transaction_items AS TransactionItem
> > LEFT JOIN transactions AS Transaction
> > ON TransactionItem.transaction_id = Transaction.id
> > GROUP BY Transaction.id
>
> Have you tried setting (SUM(TransactionItem.amount) as a field in the
> fields parameter? Also, there's a little trick where you have to pass
> GROUP BY to the model via the conditions array
>
> $conditions = array('1=1 GROUP BY Transaction.id');
> $fields = array('SUM(TransactionItem.amount) as amount', 'Transaction.title'))
>
> $results = $this->TransactionItem->find('all', compact('conditions',
> 'fields));
>
> Hope that helps.
>
> --
> Chris Hartjes
> Internet Loudmouth
> Motto for 2008: "Moving from herding elephants to handling snakes..."
> @TheKeyBoard:http://www.littlehart.net/atthekeyboard
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
