Cakephp 3按关联数据删除筛选

Question

在CakePHP 3中，假设我想删除所有具有名为"John“的Employee的部门(部门有许多员工，员工属于部门)

最简单的方法是过滤所有这些部门，并在foreach循环中逐个删除它们：

$departments = $this->Departments->find()
    ->matching('Employees', function ($q) {
        return $q->where(['Employees.name' => 'John']);
    })
    ->all();

foreach ($departments as $department) {
    $this->Departments->delete($department);
}

这将导致对每个记录进行一个SELECT查询和一个DELETE查询。我希望数据库只发送和执行一个查询。就像这样：

DELETE Departments 
FROM departments Departments 
INNER JOIN employees Employees  ON 
    Employees.department_id = Departments.id 
    AND Employees.name = 'John';

阅读文档后，我发现了三种删除记录的方法：

• 一个一个地使用表对象，获取对象，然后删除它。
• deleteAll，它接受目标表上的一系列条件，而不是关联的条件。
• 使用ORM，同样只有一个条件数组。

是否有一种优雅的方法可以使用CakePHP 3和ORM获得我的查询？

Answer 1

有两件事：

优先:我建议的方案是不正确的

我的实际情况要复杂得多，所以我以著名的员工部门案例为例。我犯了一个错误:我需要的是删除属于某个部门的所有员工。我希望从相关表的基于con条件的表中删除数据。

事实上，如果有一个叫John的雇员，你不能删除部门，因为部门不是空的，是John！:D

就像这样：

-- Create structures
CREATE TABLE IF NOT EXISTS departments (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(45) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

CREATE TABLE IF NOT EXISTS employees (
  id int(11) NOT NULL AUTO_INCREMENT,
  department_id int(11) NOT NULL,
  name varchar(45) NOT NULL,
  PRIMARY KEY (id),
  KEY fk_employees_department_idx (department_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

ALTER TABLE employees
  ADD CONSTRAINT fk_employees_departments FOREIGN KEY (department_id) REFERENCES departments (id) ON DELETE NO ACTION ON UPDATE NO ACTION;


-- Populate some data
INSERT INTO departments (id, name) VALUES
(1, 'Sales'),
(2, 'TI'),
(3, 'HHRR');

INSERT INTO employees (id, department_id, name) VALUES
(1, 1, 'John'),
(2, 1, 'Mary'),
(3, 2, 'Mark'),
(4, 3, 'Lorenzo');


-- Delete all employees that belong to the TI department (Mark should be removed)
DELETE emp FROM employees emp
INNER JOIN departments dep ON emp.department_id = dep.id AND dep.name = 'TI';

第二步:您可以在CakePHP轻松地中完成此操作

您可以获得上一次查询的相同结果：

    $departments = $this->Employees->Departments->find()
        ->select(['Departments.id'])
        ->where(['Departments.name' => 'TI']);
    $this->Employees->deleteAll(['Employees.department_id IN' => $departments]);

得到的mysql查询将是：

DELETE FROM employees WHERE department_id in 
(SELECT Departments.id AS `Departments__id` 
FROM departments Departments 
WHERE Departments.name = 'TI')

...which是正确的，而且容易理解。@arilia，你的第一个回答指向了这个方向。抱歉弄糊涂了。

Answer 2

deleteAll只是一个包装

$this->Departments->query()
   ->delete()
   ->where([...])
   ->execute();

因此，我认为，以类似的方式，有可能做到：

$departments = $this->Departments->query()
    ->delete()
    ->matching('Employees', function ($q) {
        return $q->where(['Employees.name' => 'John']);
    })
    ->execute();

但是在这种情况下，您会注意到匹配条件被忽略了。我看到这里有一个开放的票证

如果只想使用两个查询，可以执行一个查询来检索第二个查询中的所有ids和delete all：

$departments_ids = $this->Departments->find()
    ->select(['id'])
    ->matching('Employees', function ($q) {
        return $q->where(['Employees.name' => 'John']);
    })
    ->toArray();

$this->Departments->deleteAll(['id' => $departments_ids ]);

这样做的结果如下：

DELETE FROM departments WHERE id IN
(
    // id list here
)

另一个解决方案可以是在子查询中转换内部连接。实际上，您想删除几乎有一个名为John的员工的所有部门。

$employees = $this->Departments->Employees->find()
    ->select(['department_id'])
    ->distinct()
    ->where(['Employees.name' => 'John']);

$departments = $this->Departments->query()
    ->delete()
    ->where(['id IN' => $employees])
    ->execute();