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
but I'm not sure how to go about this using cake model methods.
Should I be putting something in finderQuery in the hasMany
association?
Or I could use this custom SQL in a Model->query() - not sure if this
is the ideal?
Or should I simply sum the values using PHP and not ask the database
to do this?
Also, when I tested the above as SQL in a Model->query(), I noticed
that the SUM() column was orphaned in the result array. Is there any
way to make it become part of the Transaction key of the result array?
"SUM(TransactionItem.amount) AS Transaction.total" doesn't seem to
work.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
