[FIXED] CakePHP3 How to get all Clients with all their active Contracts what have Statements due

Issue

I have Clients who have many to many Contracts what have many to many Statements.

I want to get all Clients with their active contracts (Contracts.published = 1) where there are statements due (Statements.periodend <= 2019-09-30 and Statements.amount != NULL).

So if there is not an active contract or if there are no statements due under the contract i don't want to get the client in the resultset.

This way I can loop through the clients and send reminder emails for the statements due.

I compiled the following query;

$query = $this->Statements->Contracts->Clients->find();$query
    ->matching('Contracts', function ($q) {
        return $q->where(['Contracts.published'=>true]);
    })
    ->contain('Contracts.Statements', function ($q) use ($date) {
        return $q
            ->where(['amount IS'=>NULL])
            ->where(['Statements.periodend <=' => '2019-09-30'])
            ->order(['periodend'=>'ASC']);
    })
    ->order(['Clients.name'=>'ASC']);

Now it is returning all contracts for all clients.

Clients without active contracts are ignored as how it should be.

But I also want to remove the contracts/clients where no statements are due.

Solution

Nesting contains with innerjoins and adding some distinct was the solution to the problem;

$date = '2019-12-31';

$clients = $this->Statements->Contracts->Clients->find()
        ->contain('Contracts', function ($q) use ($date) {
            return $q
                ->innerJoinWith('Statements', function ($q) use ($date){
                    return $q
                        ->where(['Statements.amount IS' => NULL])
                        ->where(['Statements.periodend <=' => $date]);
                })
                ->where(['Contracts.published' => true])
                ->contain('Statements', function ($q) use ($date) {
                    return $q
                        ->where(['Statements.amount IS' => NULL])
                        ->where(['Statements.periodend <=' => $date])
                        ->order(['Statements.periodend' => 'ASC']);
                })
                ->distinct(['Contracts.id']);
        })
        ->innerJoinWith('Contracts.Statements', function ($q) use ($date) {
            return $q
                ->where(['Statements.amount IS' => NULL])
                ->where(['Statements.periodend <=' => $date]);
        })
        ->distinct(['Clients.id']);

Answered By - David