CDbCommand::createCommand() returns zero affected rows inside migration

Question

This code works just fine (all database items updated as expected):

foreach($idMap as $menuId=>$pageId)
{
    $sql = "UPDATE `menus_items` SET link = '/content/show?id=".$pageId."' WHERE id = ".$menuId."; ";

    $affectedRows = Yii::app()->db->createCommand($sql)->execute();

    echo $affectedRows." affected rows\n";
}

But it prints 0 affected rows for each executed query. Why?

The same effect is, when executing many rows affecting statements in one SQL query:

$sql = '';

foreach($idMap as $menuId=>$pageId)
{
    $sql .= "UPDATE `menus_items` SET link = '/content/show?id=".$pageId."' WHERE id = ".$menuId."; ";
}

$affectedRows = Yii::app()->db->createCommand($sql)->execute();
echo $affectedRows." affected rows\n";

What am I missing? Docs says, that CDbCommand::execute should return number of rows affected by the execution. Does this feature work, when used inside migration?

Answer 1

CDbCommand::execute returns the row Count from underlying PDO interface, PDOstatement::rowCount only returns the row count of the last statement.

I had tested this within migration to be sure that migrate script is not running any other commands for cleanup etc, this is not the case, I am able to get correct row values from within and outside migration as well.

The most likely reason you are getting 0 as the value is because of the update command did not affect any rows ( i.e. the link values were already set to the correct values), UPDATE will return 0 if no change has occurred. Perhaps you already run the migration on your test db and migrated down to test it few more times, however during the subsequent passes no update actually happened.

Note in the second scenario only the count of the last command ( a single row update will be shown even if update changes the table as PDOstatement::rowCount only returns the count for last statement executed.