I really do hope that someone replies to this inquiry. I think I have spent about the whole day trying to "use the framework" to do a simple query like this. Is it possible????????
On Mar 30, 12:51 pm, acoustic_overdrive <[EMAIL PROTECTED]> wrote: > What's the recommended way to use aggregate functions likeSUM()? > > 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 thesumof > 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 simplysumthe 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 theSUM() 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 -~----------~----~----~----~------~----~------~--~---
