Cakephp 3 delete filtering by associated data

Question

In CakePHP 3, suppose I want to delete all Departments that have some Employee named "John" (Departments have many Employees, Employees belongs to Department)

The easiest approach is to filter all those Departments, and delete them one by one in a foreach loop:

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

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

This will result in one SELECT query plus one DELETE query for each record. I'd prefer one only query to be sent and executed by the database. Something like this:

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

Reading the docs I find three ways to delete records:

• Using the Table object one by one, get the object, then delete it.
• Using the Table with deleteAll, which takes an array of conditions over the target table, not the associated ones.
• Using the ORM, again with only an array of conditions.

Is there an elegant way to obtain my query using CakePHP 3 and the ORM?

Answer 1

Two things:

FIRST: The scenario I suggested is not correct

My real case is much more complicated, so I made an example with the well known employee-department case. I made a mistake: what I need is to delete all employees that belong to a certain department. I want to remove data from a table based con conditions on a related table.

Actually, you can't delete departments where there is an employee named John, 'cause the department is not empty, there's John!! :D

Something like this:

-- 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';

SECOND: You can do this in CakePHP easily

You can get the same result of the previous query doing this:

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

The resulting mysql query will be:

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

...which is correct and moreover easy to understand. @arilia, you pointed in this direction with your first answer. Sorry for the confussion.

Answer 2

deleteAll is just a wrap around

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

So I though that, in a similar way, it would be possible to do:

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

but in this case you'll notice that the matching condition is ignored. I see that there is an open ticket about that

If you want to use just two queries you could do a query to retrieve all the ids and the delete all in the second query:

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

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

that results in something like:

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

another solutions could be transform your inner join in a subquery. Actually you want to delete all the departments that have almost an Employee named John.

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

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