CODEWITHSUNDEEP
cakephpcakephp-2.0
monkeyhybrid
monkeyhybrid

Reputation: 356

Getting sum of column values from a related model when accessing another linked model in CakePHP

I have a MySQL table for bookings and a separate table for payments, with one or more (or none) payments belonging to each booking. The two tables have appropriate relationships setup within the Cake models and foreign keys established in the MySQL to link the two together.

When displaying a list of bookings in a view, I'd like to show a total of all payments for each booking alongside each booking's details, so that I can see what has been paid against a booking and what balance is remaining to be paid.

What would be the best way to go about achieving this in CakePHP? I could use afterSave() in the payment model to update a total stored in the booking model but I'm thinking a virtual field solution or something similar would be preferable for database integrity if there is such a way?

Upvotes: 1

Views: 153

Answers (1)

DSG
DSG

Reputation: 436

Since payments belong to a booking, a query for a booking should return an array of the associated payments. Use the afterFind callback function in the booking model to add up the payment totals and assign to a new element in the booking data structure.

$results[0][$this->alias]['total_payments'] += $results[0][$this->alias]['Payment']['payment_amount'];

http://book.cakephp.org/2.0/en/models/callback-methods.html#afterfind

Upvotes: 1

Related Questions