我有两个表,Clazzes
并且Teachers
都由第三个表连接clazzes_teachears
。我如何检索Clazzes
没有Teachers
关联的?现在,我只能返回Clazzes
带有或不带有Teacher的所有元素。
ClazzesTable.php初始化
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初始化
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'
]);
}
注意:不存在ClazzesTeachersTable.php
返回有/没有老师的所有Clazzes的方法(我只需要在Clazzes
没有的情况下进行检索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();
}
老师的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`));
使用join查询构建器,请参阅更多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();
}
您也可以leftJoin()
直接使用。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句