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.