5

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.

ᴍᴇʜᴏᴠ
  • 4,804
  • 4
  • 44
  • 57
emersonthis
  • 32,822
  • 59
  • 210
  • 375
  • 1
    Is there a unit test in the CakePHP core that covers your case and ensures the join table records are deleted? If not please report it as an issue on Github. Are you sure that only the join table records are left over and not the joined tables records as well? Try setting `'dependent' => true,` for your assoc, I'm not sure if it's by default true right now. – floriank Apr 19 '16 at 21:37
  • Hi @burzum I have tried it with the dependent => true, and yes, I'm certain that only the join table records are left over because I'm testing on empty tables... So after the test the Stops and Routes are empty but routes_stops is still full. I'm not sure about the unit test question... – emersonthis Apr 19 '16 at 23:45
  • You may need to enable [cascading deletes](http://book.cakephp.org/3.0/en/orm/deleting-data.html#cascading-deletes) instead of just dependent. – Greg Schmidt Apr 20 '16 at 15:36
  • I don't think bulk deletes work with cascading deletes due to the fact that before and after event do not get applied to bulk deletes. The documentation says that you need to load the records and delete them separately. – chrisShick Apr 20 '16 at 16:22
  • @chrisShick Can you share a link to where the docs say that? – emersonthis Apr 20 '16 at 16:23
  • @chrisShick do you mean greg's link? The doc are a bit confusing on this point: "By default entities in associated tables are removed using Cake\ORM\Table::deleteAll()." This sounds like it should work... – emersonthis Apr 20 '16 at 16:36
  • Yes, @emersonthis!! I apologize!! Greg's link is the one that refers to the documentation of what I mentioned. Yes, associated table entries are deleted with deleteAll. But deleteAll does not fire the before and after delete events. Therefore, if you deleteAll on the main model the associated will not be deleted. If you use a regular delete the associated models will be deleted with the deleteAll command. – chrisShick Apr 20 '16 at 16:38
  • @chrisShick I'm confused by this sentence: "If you use a regular delete the associated models will be deleted with the deleteAll command." Aren't `delete()` and `deleteAll()` two different functions? – emersonthis Apr 20 '16 at 22:58
  • Yes @emersonthis. Let's use your models as an example: If I say $stopsTable->delete($stop) then (if the dependent key is true) it will delete the associations using the deleteAll() function (inside of the beforeDelete callback) and then deletes the stop. If you use $stopsTable->deleteAll(), it will not delete the associations at all because the beforeDelete callback will not fire. – chrisShick Apr 20 '16 at 23:10
  • @chrisShick Ahhhh. The `delete()` *calls* the `deleteAll()` in the callback. I get what you're saying now. Thanks – emersonthis Apr 20 '16 at 23:12
  • Precisely!! The deleteAll however does not fire any callbacks. – chrisShick Apr 20 '16 at 23:13
  • ^ ...calls `deleteAll()` on the associations connected to that Entity being `delete()`'ed – emersonthis Apr 20 '16 at 23:13
  • @chrisShick So it sounds like there is actually no way to delete all the join records without looping over and `delete()`ing each Entity – emersonthis Apr 20 '16 at 23:14

1 Answers1

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);
    }
});
chrisShick
  • 1,096
  • 8
  • 21
  • Thanks. Are you confident that the `->transactional()` is necessary? I saw [in the docs](http://book.cakephp.org/3.0/en/orm/deleting-data.html#namespace-Cake\ORM) that "all deletes happen within a transaction" but wasn't sure what that meant. – emersonthis Apr 21 '16 at 00:48
  • The docs mean that the primary object being deleted and it's dependent associations are all deleted in a transaction. – chrisShick Apr 21 '16 at 01:03