[FIXED] Retrive only records without record associated in another table

Issue

I have two tables Clazzes and Teachers, both are joined by a third Table clazzes_teachears. How I can retrive the Clazzes that has no Teachers associated ? now I can only return all Clazzes elements with or without Teacher associated.

ClazzesTable.php Initialize

public function initialize(array $config)
{
    parent::initialize($config);

    $this->table('clazzes');
    $this->displayField('name');
    $this->primaryKey('id');

    $this->belongsToMany('Teachers', [
        'foreignKey' => 'clazz_id',
        'targetForeignKey' => 'teacher_id',
        'joinTable' => 'clazzes_teachers'
    ]);
}

TeachersTable.php Initialize

public function initialize(array $config)
{
    parent::initialize($config);

    $this->table('teachers');
    $this->displayField('id');
    $this->primaryKey('id');

    $this->belongsToMany('Clazzes', [
        'foreignKey' => 'teacher_id',
        'targetForeignKey' => 'clazze_id',
        'joinTable' => 'clazzes_teachers'
    ]);
}

Note: doesn't exist ClazzesTeachersTable.php

Method to return All Clazzes with/without teachers associated (I need retrive only Clazzes without Teachers)

public function getAllClazzesRecursive(){
    return $this
        ->find('all')
        ->contain([
            'Teachers' => function($q) {
                return $q->select(['id', 'registry', 'url_lattes', 'entry_date', 'formation', 'workload', 'about', 'rg', 'cpf', 'birth_date', 'situation']);
            }
        ])->hydrate(false)->toArray();
}

Teacher sql:

 CREATE TABLE IF NOT EXISTS `teachers` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `registry` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

Clazzes sql:

 CREATE TABLE IF NOT EXISTS `clazzes` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

clazzes_teachers sql:

CREATE TABLE IF NOT EXISTS `clazzes_teachers` (
  `clazz_id` INT NOT NULL,
  `teacher_id` INT NOT NULL,
  PRIMARY KEY (`clazz_id`, `teacher_id`));

Solution

Use join to query builder , see more http://book.cakephp.org/3.0/en/orm/query-builder.html#adding-joins

 public function getAllClazzesRecursive(){
    return $this
        ->find('all')
        ->join([
          'table' => 'clazzes_teachers',
          'alias' => 'ct',
          'type' => 'LEFT',
          'conditions' => 'ct.clazz_id= clazzes.id',
         ])
       ->where('ct.id IS NULL')
       ->hydrate(false)->toArray();
}

you can also use leftJoin() directly.

Answered By - N Nem