Yii 1.1: Using updateAll and deleteAll with scopes

5 followers

Using CActiveRecord->updateAll() and CActiveRecord->deleteAll() with scopes and criteria requires some tricks that are explained in this article. While findAll() , find() and some other methods use scopes automatically, updateAll() and deleteAll() do not.

The limitation of updateAll() and deleteAll()

If you tried, you will know that while these functions allow you to specify a $condition as a parameter, but that they do not use the conditions and scopes that you may have defined on the model(). In other words, the next code does not behave as you might expect:

Alert::model()->alert_id($alert_id)->is_ack(1)->updateAll(array('is_ack'=>0));

What you might expect is that we are updating all records with a given alert_id and is_ack status, but what is actually happening is that ALL records in the Alert table have 'is_ack' set to 0.

This behavior is by design and has been subject to discussions on the forum, etc.

The solution

Fortunately, there is a way to use the wonderful feature to define scopes and conditions in a high level manner.

The trick is to extract the criteria by using applyScopes(). The issue is that applyScopes() will suppose an alias 't' for the table. And that breaks the SQL query as the updateAll() and deleteAll() methods do not apply an alias to the table you are updating to or deleting from. They just keep their original name. Therefore, the extra trick is to set the alias to the table's name before applying the scope.

It goes like this:

Alert::model()->getDbCriteria()->alias=AlertHistory::model()->tableName();
   $criteria=new CDbCriteria();
   Alert::model()->alert_id($alert_id)->is_ack(1)->forEntity($this->trackerId)->web()->applyScopes($criteria);
   Alert::model()->updateAll(array('is_ack'=>0),$criteria);

So the above code first sets the alias to use with the criteria. It then sets the $criteria. And finally it calls updateAll() to update all the selected records.

Making the solution more robust

Now that is great, and most developers would stop there. However, the remaining issue with that is that you have to think about using applyScopes() and setting the alias every time. And if you forget it by accident, all your records will be updated with updateAll() (without taking the scopes into account) and you might just break the contents of your database.

Therefore, it is preferable to add some security. This can be done by updating the models as shown below. For full automation, you can update your Gii basemodel. The sample code for Gii is provided further below. By automating the generation, you protect yourself from using updateAll() in an unappropriate way by accident.

class Alerts extends CActiveRecord {
   [...]
 
    /**
     * Updates records with the specified condition.
     *
     * Modifies the standard method in that it takes into account the model conditions to select the records to update.
     *
     * @see CActiveRecord::updateAll()
     */
    public function updateAll($attributes,$condition=array(),$params=array())
    {
        //Next line must be added before defining the criteria/scopes.
        //Alerts::model()->getDbCriteria()->alias=Alerts::model()->tableName();
        $crit=new CDbCriteria($condition);
        $this->applyScopes($crit);
        return parent::updateAll($attributes,$crit);
    }
 
    /**
     * Deletes records with the specified condition.
     *
     * Modifies the standard method in that it takes into account the model conditions to select the records to update.
     *
     * @see CActiveRecord::updateAll()
     */
    public function deleteAll($condition=array(),$params=array())
    {
        //Next line must be added before defining the criteria/scopes.
        //Alerts::model()->getDbCriteria()->alias=Alerts::model()->tableName();
        $crit=new CDbCriteria($condition);
        $this->applyScopes($crit);
        return parent::deleteAll($crit);
    }
}

With the above code, all you have to do now is this:

Alert::model()->getDbCriteria()->alias=AlertHistory::model()->tableName();
   Alert::model()->alert_id($alert_id)->is_ack(1)->forEntity($this->trackerId)->web()->->updateAll(array('is_ack'=>0));

And what is more is that if you forget to assign the alias, you will get an exception !

Automating

So, as promised, in order to help you update your Gii basemodel, here is the extract of what I put in my basemodel for Gii (I use Awe/AweModel).

/**
     * Updates records with the specified condition.
     *
     * Modifies the standard method in that it takes into account the model conditions to select the records to update.
     *
     * @see CActiveRecord::updateAll()
     */
    public function updateAll($attributes,$condition=array(),$params=array())
    {
        //Next line must be added before defining the criteria/scopes.
        //<?php echo $modelClass;?>::model()->getDbCriteria()->alias=<?php echo $modelClass;?>::model()->tableName();
        $crit=new CDbCriteria($condition);
        $this->applyScopes($crit);
        return parent::updateAll($attributes,$crit);
    }
 
    /**
     * Deletes records with the specified condition.
     *
     * Modifies the standard method in that it takes into account the model conditions to select the records to update.
     *
     * @see CActiveRecord::updateAll()
     */
    public function deleteAll($condition=array(),$params=array())
    {
        //Next line must be added before defining the criteria/scopes.
        //<?php echo $modelClass;?>::model()->getDbCriteria()->alias=<?php echo $modelClass;?>::model()->tableName();
        $crit=new CDbCriteria($condition);
        $this->applyScopes($crit);
        return parent::deleteAll($crit);
    }

Total 4 comments

#17996 report it
Rohit Suthar at 2014/08/25 04:18am
RE: #17993

thanks for your valuable response :)..

#17993 report it
le_top at 2014/08/24 08:40am
@Suthar - Avoiding scopes

Dear Rohit You are correct that it is possible to add conditions directly as a parameter to the updateAll/deleteAll method. However, this Wiki is about using scopes with these methods, and the parameter that you add to the method call is not a scope.

The use of scopes increases the level of abstraction of the code, information hiding, factory, ..., and improves readability and ease of evolutions. It is also less error prone as a lot of errors are identified earlier.

A scope is described in the model, so any modification to the conditions needed for the scope has to be written/changed only in that location.

Suppose that 'type_id=1' corresponds to 'user' and you add a new 'type_id' which is '3' which corresponds to 'system_user' and that you actually want to update 'any_user'.

Using scopes you would write:

Example::model()->any_user()->inactive()->updateAll();

'inactive()' is better than a 'status=0' condition inside the condition. 'any_user()' is more readable than '(type_id=1 or type_id=3)' or 'type_id in (1,3)' .

That code does not have to change when you add a new type_id that is a kind of user as you would only update the scope, and not every location where you check the type_id.

#17978 report it
Rohit Suthar at 2014/08/20 09:38am
Another solution

I'm getting another solution, we can also updateAll records to easy way. eg.

Example::model()->updateAll(array('status' => 1), 'type_id = 1 AND status = 0');
Example::model()->updateAll(array('status' => 1, 'updated' => date('Y-m-d H:i:s')), 'type_id = 1 AND status = 0');
#17651 report it
Rohit Suthar at 2014/07/11 02:30am
Cool..

I'm waiting for this... thanks man!!

Leave a comment

Please to leave your comment.

Write new article