Getting rid of JOINs in updateAll() query

I guess I haven’t mastered the model relationships enough in order to appreciate them – and use correctly. On one hand they seem to be really cool and provide an easy access to linked database records while on the other hand, they just seem to stand in the way too often.

Caring about database queries performance as I am I find myself unbinding models more often than I might be binding them. I guess I should not set default binding in the model but that’s just a natural place to put the logic. I’m trying to follow DRY as much as possible.

Anyway, here’s a piece of code that unbinds all belongsTo models in order to remove JOINs from an updateAll() query.

  1.  
  2. $content = new Content();
  3. $content->unbindModel(array(
  4.   ‘belongsTo’ => array_keys($content->belongsTo)
  5. ));
  6. $content->updateAll(
  7.   array(‘num_of_purchases’ => ‘num_of_purchases+1’),
  8.   array(‘id’ => $purchasedContentIds)
  9. );
  10.  

(suggested by Serge Rodovnichenko)

Setting $content->recursive = -1 doesn’t work. I debugged the call in CakePHP 1.2.5.

Edit September 27th, 2009:

Let’s move this up to another level – let’s override the default updateAll() and add support for recursive = -1. Add this to your app_model.php

  1.  
  2.   /**
  3.    * A workaround for CakePHP lack of support for recursive
  4.    */
  5.   function updateAll($fields, $conditions = true, $recursive = null) {
  6.     if (!isset($recursive)) {
  7.       $recursive = $this->recursive;
  8.     }
  9.  
  10.     if ($recursive == -1) {
  11.       $belongsTo = $this->belongsTo;
  12.       $hasOne = $this->hasOne;
  13.       $this->unbindModel(array(
  14.         ‘belongsTo’ => array_keys($belongsTo),
  15.         ‘hasOne’ => array_keys($hasOne)
  16.       ), true);
  17.     }
  18.  
  19.     $result = parent::updateAll($fields, $conditions);
  20.    
  21.     if ($recursive == -1) {
  22.       $this->bindModel(array(
  23.         ‘belongsTo’ => $belongsTo,
  24.         ‘hasOne’ => $hasOne
  25.       ), true);
  26.     }
  27.    
  28.     return $result;
  29.   }
  30.  

Edit April 22th, 2011:

I just found out that I forgot to put back the unbinded associations. The function above already contains the fix.

6 Responses to “Getting rid of JOINs in updateAll() query”

  1. kleingeist says:

    Hey thanks,
    this was exactly what i was looking for :)

    And unfortunately i have to agree, that relationships seem to stand in the way too often :(
    But well, thats the cake way of coding and we choose it.

  2. Jesh says:

    Nice to know but any idea why Cake left join associated model when calling Model::updateAll() ?

  3. Sethathi says:

    Thanks man!

  4. Pankaj K says:

    Thank very much for this.

  5. Brian says:

    Thanks for this post. I know it is very old, but what about hasMany and hasAndBelongsToMany? Wouldn’t they need to be unset as well?

Leave a Reply