Simple tweak for model relations in CakePHP

Posted in CakePHP on 30.06.2009.

What happens when you wait for half an hour to delete one row in your database? Investigation!

Here is a naughty bit I discovered a few days ago.

You probably already know your can make a model in cake "dependable". When a model is dependable it means that when you delete stuff from model A, related stuff in model B will be automagically deleted by cake.

Let's say A=DocumentHeader and B=DocumentItem, B being dependable on A. If you delete a document, cake will automagically delete all the items in B related to that document. But, what's not obvious is that cake will delete items in B one by one. Well, I don't know, maybe it's obvious to some, but it's not really obvious to me. So if you have 200.000 document items, you'll have to wait a while before cake executes delete from ... for each and every one of those.

Last week, I just happened to have such a relation and I wasn't aware of the above fact. Let's just say it was slow. Painfully slow. Nasty.

Since people don't like painfully slow and nasty things (except sex of course), we need to fix that. Luckily for us, there is a built-in parameter which tells cake to delete all the related stuff with a single query. In other words, cake will use deleteAll() to delete stuff in the related model.

Solution? Just add "exclusive":

class DocumentHeader
    var $hasMany = array
            'DocumentItem' => array
                'className' => 'DocumentItem',
                'foreignKey' => 'document_id',
                'dependent' => true,
                'exclusive' => true, // voila!

Honestly, that's all you need to do to get a kick-ass performance boost. It's so good that I wonder why is this not the default behaviour?

Anyway, if you don't have any special requirements in before/after delete operations in your DocumentItem, this is the only way not to shoot yourself in the genitals. Or foot. Whichever you prefer.

Happy baking!

Article comments — View · Add

Page 1 of 2
1 · 2

Chad :: 30.06.2009 22:33:21
Thats a good tip. I don't remember seeing this in older versions of cake 1.2. Is this something they recently introduced?
Richard@Home :: 01.07.2009 03:51:29
Will this still delete items dependant on DocumentItem too?
lecterror :: 01.07.2009 04:15:58
@Chad: I didn't see it either, so I assume it must be newish..

@Richard: That's actually a very good question. I will try it out and let you know.
lecterror :: 02.07.2009 02:36:05
@Richard: Just tried it, it appears to be working correctly. Another win for cake.
Jesh :: 03.07.2009 21:07:17
What i normally do is set the relationship between two tables in database instead of manually set the dependencies in PHP. Of course for MySQL your table type should be InnoDB.