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
-~----------~----~----~----~------~----~------~--~---
