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?

@MichaelHartl: any alternative way with out writing query by hand? — Moein Hosseini, Aug 30 '13 at 09:34
You could use [DAO](http://www.yiiframework.com/doc/guide/1.1/en/database.dao) or the [query builder](http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder#building-data-manipulation-queries). They'll both not disburden you from writing the inserts manually, though. — Michael Härtl, Aug 30 '13 at 10:07
Why do you need to insert them all as a single query? Surely using the transaction almost exactly as you have, you can insert as separate queries, but still be able to roll back if necessary, — Zack Newsham, Sep 07 '13 at 23:47
@ZackNewsham: bulk insert has some benefits, execute query is faster than execute queries, it use less network between you php and database and so on. — Moein Hosseini, Sep 08 '13 at 04:51

valex · Answer 1 · 2014-11-11T12:03:23.790

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

score 5 · Accepted Answer · answered Sep 08 '13 at 08:08

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.

I am getting this issue, Declaration of CDbMultiInsertCommand::execute() should be compatible with CDbCommand::execute($params = Array) — Mohd Shahid, Apr 18 '14 at 13:18
@MohdShahid just add the $param in the parameter for the extended `execute` function. — Muhammad Omer Aslam, Aug 06 '19 at 12:14

score 5 · Answer 3 · answered May 31 '19 at 13:20

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

score -1 · Answer 4 · answered May 31 '19 at 10:19

Update for the Bulk Insert / Batch Insert for YII

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 $keyColumnName => $column){
        /** @var CDbColumnSchema $column */
        if($column->autoIncrement){
            unset($this->columns[$keyColumnName]);
            continue;
            // $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);
    $this->query = str_replace('"', "'", $this->query);

    return true;
}

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

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

    $this->setText($this->insert_template." ".$this->query);

    return parent::execute();
}

}

I was facing problem with 3 things with the earlier code.

Auto increment column where in earlier code it was set as 0
Query statement which had double quotes.
execute function should be similar to parent execute function with parameter.

I guess the first 2 points are related to database I am using postgresql, hope the updated code works for all database systems.

Looks like great recipe for SQL injection (same as other answers in this question)... — rob006, May 31 '19 at 13:04

Batch insert in Yii

4 Answers4

Linked