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?

Ontologism

You can't with ActiveRecord. – Dunnite

@MichaelHartl: any alternative way with out writing query by hand? – Ontologism

You could use DAO or the query builder. They'll both not disburden you from writing the inserts manually, though. – Dunnite

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, – Lannielanning

@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. – Ontologism

https://mcmap.net/q/1412565/-insert-multiple-data-into-database-in-yii-2 – Procter

Answer 1

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.

Lannielanning

I am getting this issue, Declaration of CDbMultiInsertCommand::execute() should be compatible with CDbCommand::execute($params = Array) – Pavlov

@MohdShahid just add the $param in the parameter for the extended execute function. – Nicolette

Answer 2

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

Spunky

Answer 3

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

Barbusse

Answer 4

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.

1. Auto increment column where in earlier code it was set as 0
2. Query statement which had double quotes.
3. 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.

Underwaist

Looks like great recipe for SQL injection (same as other answers in this question)... – Barbusse