CakePHP - deleting data by associated key

Question

I have a few tables that are joined through a distant relationship - for example:

A.id = B.a_id, B.id = C.b_id, C.id = D.c_id

And given A.id, I want to delete all the rows in D that are associated with A.id.

Since Model::deleteAll() does not accept any joins, only conditions, how do I go about it?

All the models (A, B, C, D) already have belongTo relationships defined.

My last resort would be raw SQL, but I would like to know if there's a way in CakePHP to do it.

I could not find similar questions as they all were about deleting ALL the associated data, rather than just one table's data via an associated key.

@Salines it is in the second line of the question, they are related through B and C models, sorry if I couldn't make it more clear. — xiankai, Feb 04 '15 at 14:21
You can execute a query on B to get its id's, then use B.id's to get the C.id's which you can use in the `DeleteAll` method, but that will be inefficient (by needing to run at least 1 additional `find` before the `deleteAll`). Just run the raw SQL and document your code how the SQL works. — AgRizzo, Feb 04 '15 at 14:23
@AgRizzo hmm, raw SQL would indeed be my last resort, but for what you propose in CakePHP, how would you write the find that gets C.id from A.id? You can put it in an answer. — xiankai, Feb 04 '15 at 14:35
Take a look at @Salines answer. I added a comment how I would use the `deleteAll` after getting a list of C.id's that need to be deleted (use the C.id's in comparison to the D.C_id field) — AgRizzo, Feb 04 '15 at 21:50

score 1 · Answer 1 · answered Feb 04 '15 at 15:29

Use Containable behavior to find D records

public function deleteD($idA){
$this->ModelA->Behaviors->load('Containable');

$options = array(
    'contain' => array(
       'ModelB' => array(
           'ModelC' = array(
               'ModelD'
           )
       )
    ),
    'conditions' => array('ModelA' => $idA)
);
$findDIds = $this->ModelA->find('all',$options);
debug($findDIds); // find right path to ModelD
$ids = Hash::extract($findDIds,'{n}.ModelD.id');
$this->loadModel('ModelD');
   foreach($ids as $id){
       $this->ModelD->delete($id);
   }
}

Note, I not tested this function.

Better performance would be to use `deleteAll`. You could change your `find` to have `'fields'=>array('DISTINCT C.id')` (and you don't need to `contain` to ModelD). The results of that `find` should be able to be used in the `deleteAll` conditions (something like `$conditions=>array('D.c_id' => $findDIds)`) — AgRizzo, Feb 04 '15 at 21:46

CakePHP - deleting data by associated key

1 Answers1