> 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.
$this->Transaction->bindModel(array('hasOne' =>
array('TransactionItem')));
$data = $this->Transaction->find('all', array(
'recursive' => 0,
'fields' => array('Transaction.title', 'SUM(TransactionItem.amount)
AS total'),
'conditions' => '1=1 GROUP BY Transaction.id'
));
> Should I be putting something in finderQuery in the hasMany
> association?
No. This is unrelated to what you want to do.
> Or I could use this custom SQL in a Model->query() - not sure if this
> is the ideal?
You can do, but it's not necessary.
> Or should I simply sum the values using PHP and not ask the database
> to do this?
This would be easier except when you need to paginate and order by the
aggregate. Might as well make the database work for a living.
> 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.
Depends on your database. If you're using postgres, you can do
'SUM(TransactionItem.amount) AS Transaction__total'
Since you're only returning 2 fields, just use Set::combine() to
return an associative array:
$data = Set::combine($data, '{n}.Transaction.title', '{n}.0.total');
If you really want to rename the field in-place, see the links in this
thread:
http://groups.google.com/group/cake-php/browse_thread/thread/9c024a328b182a26/
hth
grigri
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
