CakePHP deleteAll for many to many relationship does not remove join records

Question

I'm on CakePHP v3.17 w/ Postgres 9.4

I'm trying to get $this->SomeTable->deleteAll([...]) to remove the records in the join table too.

Imaging a bus system with a table for Stops and a table for Routes. Stops are associated with many routes (because multiple bus routes can stop at each) and Routes obviously are associated with many stops.

RoutesTable.php:

$this->belongsToMany('Stops');

StopsTable.php:

$this->belongsToMany('Routes');

Here's the delete logic I want to use, but DOESN'T work because the records in the join table are left over:

    $stopsTable = TableRegistry::get('Stops');
    $stopsTable->deleteAll(['agency_id' => $agency->id]);

    $routesTable = TableRegistry::get('Routes');
    $routesTable->deleteAll(['agency_id' => $agency->id]);

Here is the logic that DOES work, but inefficiently because it has to loop over every single stop:

    $stopsTable = TableRegistry::get('Stops');
    foreach ($agency->stops as $stop) {
        $stopsTable->delete($stop);
    }
    $routesTable = TableRegistry::get('Routes');
    $routesTable->deleteAll(['agency_id' => $agency->id]);

What's the better / correct way to do this?

Here's a similar question but for v2.x so not necessarily relevant here.

Answer 1

There isn't a way to do what you are needing. But, I would certainly suggest doing what you already said in your opening post and wrap it in a transactional (note this is untested, but should work):

$stopsTable->connection()->transactional(function () use ($stopsTable, $stops) {
    foreach ($stops as $stop) {
        $stopsTable->delete($stop);
    }
});