Summary:
I have three related tables and want to SUM() data from the field/column of one of the related tables. I’ve gone through a lot of variations trying to figure out the best/correct syntax, and have something that works, but I’m new to Cake and have a feeling there’s a more elegant or more correct syntax that I should have used by merely tweaking the provided code from Bake, instead of where I ended up, and want to start off on the right foot as I continue to learn.
The tables and relationships:
accounts - our billable customer account (id, name, address) to which we send invoices [Accounts hasMany CustomerInvoices]
customer_invoices - billings sent to the accounts to be paid (id, invoice_number, account_id, amount, date). An Account will have many inv, so these belongsTo Accounts; and these also hasMany PaymentsReceived.
payments_received - payment records showing payments made against invoices (id, invoice_id, amount, date, payment_method), so these belongsTo CustomerInvoices
In the CustomerInvoicesController - the default code from Bake looks like this: and I thought I’d be able to simply add a join to the this->paginate statement, but was unable to find the correct syntax to use.
public function index()
$this->paginate = [
'contain' => ['Accounts', 'PaymentsReceived']
];
$customerInvoices = $this->paginate($this->CustomerInvoices);
$this->set(compact('customerInvoices'));
$this->set('_serialize', ['customerInvoices']);
}
…so I kept digging and trying variations until I ended up with the code below, which works. But again, I have a feeling I could’ve done something simpler with a minor tweak to the code provided by Bake, and since I’m just learning CakePHP, I want to make sure I’m starting out with the right syntax and approach going forward.
public function index()
$query = $this->CustomerInvoices
->find()
->contain([
'Accounts',
'PaymentsReceived'
]);
$query->select(['total_payments' => $query->func()->sum('PaymentsReceived.amount')])
->leftJoinWith('PaymentsReceived')
->group(['CustomerInvoices.id'])
->enableAutoFields(true);
$customerInvoices = $this->paginate($query);
$this->set(compact('customerInvoices'));
$this->set('_serialize', ['customerInvoices']);
If anyone can help identify the better syntax I should’ve used, or validate that the solution I came up with is the best/right way to go, that would be immensely helpful. Thank you in advance.