Looking for proper JOIN syntax in given Paginate for SUM of related records


#1

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.


#2

I’m not sure if you have to use leftJoin while you use earlier ‘contain’


#3

Thanks jarekgol, but I just verified that it is required. Removing the leftJoin() results in the following error:
Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'PaymentsReceived.amount' in 'field list'