Get all rows where the field is not NULL in CakePHP 3

Question

How can I get all the instances of a Table where the fields are not NULL ?

Here is the configuration:

I have a Table 1 where the instances have a relationship "hasmany" with a Table 2. I want to get all the instances of Table 1 linked with a Table 2 instance not NULL.

The CakePHP doc helped me finding the exists() and isNotNull() conditions but I did not achieve.

Here is how I imagined:

$Table1 = TableRegistry::get('Table1')->find('all')->contain([
            'Table2' => [
                'sort' => ['Table2.created' => 'desc']             
            ]
        ])->where([
            'Table1.id' => $id,
            'Table2 IS NOT NULL'
        ]);
        $this->set(compact('Table1'));

But it obviously does not work.

edit : I expect to get all the line of the Table1 which contain existing Not NULL Table2 line(s) linked. The problem is in the 'where' array with the 'Table2 IS NOT NULL', it does not work. And without this line 'Table2 IS NOT NULL', I get all the Table1 line which contain a Table2 line or not (because some line of Table1 are not linked at all and I don't want to get these lines).

Answer 1

Assuming the tables follow convention and use "id" as the primary key, I suggest the easiest fix would be testing that field for NOT NULL.

I.e., replace this:

'Table2 IS NOT NULL'

with this:

'Table2.id IS NOT NULL'

or:

'Table2.id !=' => null

or:

'Table2.id >' => 0

Answer 2

I've successfuly get the Table1 lines with its existing Table2 line(s) associated.

query = TableRegistry::get('Table1')->find();
                $query->select(['Table1.id', 'count' => $query->func()->count('Table2.id')])->matching('Table2')->group(['Table1.id'])->having(['count
        >' => 0]);

                $table1Ids = [];
                foreach ($query as $z)
                {
                    $table1Ids[] = $z->id;
                }

                $table1= TableRegistry::get('Table1')->find('all')->contain([
                    'Table2' => [
                        'sort' => ['Table2.created' => 'desc']             
                    ]
                ])->where([
                    'id IN' => $table1Ids,
                ]);