Batch insert in Yii

A new version of this class

class CDbMultiInsertCommand extends CDbCommand{

    /** @var CActiveRecord $class */
    private $class;

    /** @var string $insert_template */
    private $insert_template = "insert into %s(%s) ";

    /** @var string $value_template */
    private $value_template = "(%s)";

    /** @var string $query */
    public $query;

    /** @var CDbColumnSchema[] $columns */
    private $columns;

    /** @var boolean $fresh */
    private $fresh;

    /** @var CDbConnection $db */
    private $db;

    /** @param CActiveRecord $class
     *  @param CDbConnection $db
     */
    public function __construct($class, $db = null){


        $this->class = $class;
        $this->createTemplate();
        if(is_null($db)){
            $this->db = Yii::app()->db;
        }
        else{
            $this->db = $db;
        }

        parent::__construct($this->getConnection());
    }
    private function createTemplate(){
        $this->fresh = true;
        $value_template = "";
        $columns_string = "";
        $this->columns = $this->class->getMetaData()->tableSchema->columns;
        $counter = 0;
        foreach($this->columns as $column){
            /** @var CDbColumnSchema $column */
            if($column->autoIncrement){
                $value_template .= "0";
            }
            else if($column->type == "integer" || $column->type == "boolean" || $column->type == "float" || $column->type == "double") {
                $value_template .= "%d";
            }
            else{
                $value_template .= "\"%s\"";
            }
            $columns_string .= $column->name;
            $counter ++;
            if($counter != sizeof($this->columns)){
                $columns_string .= ", ";
                $value_template .= ", ";
            }
        }

        $this->insert_template = sprintf($this->insert_template, $this->class->tableName(), $columns_string);
        $this->value_template = sprintf($this->value_template, $value_template);
    }

    /** @param boolean $validate
     *  @param CActiveRecord $record
     */
    public function add($record, $validate = true){
        $values = array();
        if($validate){
            if(!$record->validate()){
                return false;
            }
        }
        $counter = 0;
        foreach($this->columns as $column){
            if($column->autoIncrement){
                continue;
            }
            $values[$counter] = $record->{$column->name};
            $counter ++;
        }
        if(!$this->fresh){
            $this->query .= ",";
        }
        else{
            $this->query = "values";
        }

        $this->fresh = false;
        $this->query .= vsprintf($this->value_template, $values);
        return true;
    }

    public function getConnection(){
        return $this->db;
    }

    public function execute(){
        if(!$this->query)
            return;

        $this->setText($this->insert_template." ".$this->query);
        return parent::execute();
    }
}

Usage would be:

$transaction = Yii::app()->db->beginTransaction();
$multi = new CDbMultiInsertCommand(new Mymodel());
for ($i = 0;$i < 10;$i++){
    $model = new Mymodel();
    $model->x = $i;
    $multi->add($model, $shouldBeValidated);
}

$multi->execute();

if ($transaction->active)
    $transaction->commit();

While not entirely Yii like, it can be made as an extension/component, and is treated like a normal command, so transactions still apply. It would be entirely possible to set this up to utilise parameters rather than string literals in the query, and could also implement checking of null and default values.

class CDbMultiInsertCommand extends CDbCommand{

/** @var CActiveRecord $class */
private $class;

/** @var string $insert_template */
private $insert_template = "insert into %s(%s) ";

/** @var string $value_template */
private $value_template = "(%s)";

/** @var string $query */
public $query;

/** @var CDbColumnSchema[] $columns */
private $columns;

/** @var boolean $fresh */
private $fresh;

/** @var CDbConnection $db */
private $db;

/** @param CActiveRecord $class
 *  @param CDbConnection $db
 */
public function __construct($class, $db = null){
  $this->class = $class;
  $this->createTemplate();
  if(is_null($db)){
    $this->db = Yii::app()->db;
  }
  else{
    $this->db = $db;
  }
}
private function createTemplate(){
  $this->fresh = true;
  $value_template = "";
  $columns_string = "";
  $this->columns = $this->class->getMetaData()->tableSchema->columns;
  $counter = 0;
  foreach($this->columns as $column){
    /** @var CDbColumnSchema $column */
    if($column->autoIncrement){
      $value_template .= "0";
    }
    else if($column->type == "integer" || $column->type == "boolean" || $column->type == "float" || $column->type == "double") {
      $value_template .= "%d";
    }
    else{
      $value_template .= "\"%s\"";
    }
    $columns_string .= $column->name;
    $counter ++;
    if($counter != sizeof($this->columns)){
      $columns_string .= ", ";
      $value_template .= ", ";
    }
  }

  $this->insert_template = sprintf($this->insert_template, $this->class->tableName(), $columns_string);
  $this->value_template = sprintf($this->value_template, $value_template);
}

/** @param boolean $validate
 *  @param CActiveRecord $record
 */
public function add($record, $validate = true){
  $values = array();
  if($validate){
    if(!$record->validate()){
      return false;
    }
  }
  $counter = 0;
  foreach($this->columns as $column){
    if($column->autoIncrement){
      continue;
    }
    $values[$counter] = $this->class->{$column->name};
    $counter ++;
  }
  if(!$this->fresh){
    $this->query .= ",";
  }
  else{
    $this->query = "values";
  }
  $this->fresh = false;
  $this->query .= vsprintf($this->value_template, $values);
  return true;
}

public function getConnection(){
  return $this->db;
}

public function execute(){
  $this->setText($this->insert_template." ".$this->query);
  return parent::execute();
}
}

Usage would be:

$transaction = Yii::app()->db->beginTransaction();
$multi = new CDbMultiInsertCommand(new Mymodel());
for ($i = 0;$i < 10;$i++){
    $model = new Mymodel();
    $model->x = $i;
    $multi->add($model, $shouldBeValidated);
}

$multi->execute();

if ($transaction->active)
    $transaction->commit();

Of course it could be made more elaborate and extended to allow for updates, etc

Hope this helps.

Since Yii 1.1.14 there is CDbCommandBuilder::createMultipleInsertCommand() method available. If you need to insert multiple records in one query, you should probably use it, since all other answers in this question are vulnerable to SQL injection, so it is easy to mess up something if you're trying to implement something like that by yourself.

Yii::app()->db->getCommandBuilder()
    ->createMultipleInsertCommand('table_name', $data)
    ->execute();

For array of models you can probably generate $data in this way (note that it will not make any validation):

$data = [];
foreach ($models as $model) {
    $data[] = $model->getAttributes();
}