Last week I wrote about Model::deleteAll(), today I want to have a look at its brother: Model::updateAll(). I call it a black sheep as its usage is quite different from what you would expect if you know its brothers Model::deleteAll() and Model::findAll().

I think it is best if we do a simple example. Before we start, let’s have a quick look at the API:

/**
 * Allows model records to be updated based on a set of conditions
 *
 * @param array $fields Set of fields and values, indexed by fields
 * @param mixed $conditions Conditions to match, true for all records
 * @return boolean True on success, false on failure
 * @access public
 */

Looks fine, and should be easy to use. So we are ready to code.

Let’s implement a method to soft-delete all finished events. We may come up with the following method in our Event model:

function removeAllFinishedEvents() {
    $this->updateAll(array('Event.finished' => false, 'Event.removed' => true), array('Event.finished' => true, 'Event.removed' => false));
}

The call of updateAll() looks correct, doesn’t it? Unfortunately, it doesn’t work. You will get some SQL errors, as the generated SQL is wrong:

UPDATE `events` SET `Event`.`finished` = ,`Event`.`removed` = 1 WHERE `Event`.`finished` = 1 AND `Event`.`removed` = 0

The first issue — UPDATE doesn’t use an alias for the table name for some reason — is easy to solve, we simply remove the aliases in our code:

function removeAllFinishedEvents() {
    $this->updateAll(array('finished' => false, 'removed' => true), array('finished' => true, 'removed' => false));
}

But the generated SQL still fails:

UPDATE `events` SET `finished` = ,`removed` = 1 WHERE `finished` = 1 AND `removed` = 0

It seems that the “false” value we provided in the first array is interpreted as an empty string (even though it doesn’t get quoted). So let’s provide 0 as a string:

function removeAllFinishedEvents() {
    $this->updateAll(array('finished' => '0', 'removed' => true), array('finished' => true, 'removed' => false));
}

And voilà, it works.

It wasn’t as easy as we thought, and we encountered different issues: no table alias is used, and the data types of the values are not recognized.

The problem is, at least partially, that we didn’t use updateAll() in the way it is supposed to be used. To quote a comment by Nate:

“The purpose of updateAll() is to allow updating of fields based on calculated values of other fields.”

That means it is only thought to be used for stuff like:

$this->updateAll(array('price' => 'price + 1'));

Not really obvious and logical…