CakePHP inefficient database queries: can they be avoided?

Question

My table structure:

boxes (id, boxname)
boxes_items (id, box_id, item_id)

I was looking at the SQL logs for the "delete box" action, and am slightly horrified.

SELECT COUNT(*) AS count FROM boxes Box WHERE Box.id = 191
SELECT BoxesItem.id FROM boxes_items BoxesItem WHERE BoxesItem.box_id = 191
SELECT COUNT(*) AS count FROM boxes_items BoxesItem WHERE BoxesItem.id = 1685
DELETE FROM boxes_items WHERE boxes_items.id = 1685
SELECT COUNT(*) AS count FROM boxes_items BoxesItem WHERE BoxesItem.id = 1686
DELETE FROM boxes_items WHERE boxes_items.id = 1686

    -- snip 50 more SELECT & DELETE statements --

SELECT COUNT(*) AS count FROM boxes_items BoxesItem WHERE BoxesItem.id = 1733
DELETE FROM boxes_items WHERE boxes_items.id = 1733

DELETE FROM boxes WHERE boxes.id = 191

This is perhaps the least efficient way to delete from these tables that I could conceive of. I mean, it could be replaced with this:

DELETE FROM boxes WHERE id = 191
DELETE FROM boxes_items WHERE box_id = 191

Is there any reason Cake does it this way? If not, do you know of any way that I can streamline the procedure without breaking the core libraries?

---

Here's the relevant bits of code:

// app/controllers/boxes_controller.php    /////////////

public function delete($id = null) {
    if ($this->Box->del($id)) {
        $this->redirect(array('action'=>'index'));
    }
}

// app/models/box.php    ///////////////////////////////

class Boxes extends AppModel {
    var $hasAndBelongsToMany = array(
        'Item'
    );
}

// app/models/app_model.php    /////////////////////////

class AppModel {
    var $actsAs = array('Containable');
    var $recursive = -1;
}

Answer 1

Unfortunately that's how Cake does it.

You could override the del() method in your model with something like this rough mockup:

function del($id, $cascade = true) {
    if ($cascade) {
        $this->BoxesItem->deleteAll(array('BoxesItem.box_id' => $id));
    }
    return parent::del($id, false);
}

Answer 2

If you have a hasMany relationship, which I am assuming is the case you might want to try setting the "exclusive" flag:

http://book.cakephp.org/view/82/hasMany

exclusive: When exclusive is set to true, recursive model deletion does the delete with a deleteAll() call, instead of deleting each entity separately. This greatly improves performance, but may not be ideal for all circumstances.