Using updateAll and deleteAll with scopes

  1. The limitation of updateAll() and deleteAll()
  2. The solution
  3. Making the solution more robust
  4. Automating

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 _alertid and _isack 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);
    }
2 0
5 followers
Viewed: 32 170 times
Version: 1.1
Category: How-tos
Written by: le_top
Last updated by: le_top
Created on: Jul 11, 2014
Last updated: 3 years ago
Update Article

Revisions

View all history

Related Articles