Batch insert in Yii

Question

I need to insert multiple ActiveRecord object in Yii,if all of them inserted

$transaction = Yii::app()->db->beginTransaction();
for ($i = 0;$i < 10;$i++){
    $model = new Mymodel();
    $model->x = $i;
    if (!$model->save()){
        $transaction->rollback();
        break;
    }
}
if ($transaction->active)
    $transaction->commit();

Now I need to insert all of them in one query,How can I do it during using active record?

Answer 1

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();

Answer 2

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.