Batch insert in Yii

Question

I need to insert multiple ActiveRecord objects in Yii if they are all 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 into one query, how to do this while using the active record?

Answer 1

Although this is not exactly Yii, it can be executed as an extension / component and is treated as a normal command, so transactions are still applied. It would be entirely possible to set this to use parameters, rather than string literals in the query, and also implement checking for null values ​​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(); } } 

Using:

 $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 complex and expanded for updating, etc.

Hope this helps.

Answer 2

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

Using:

 $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 3

Update for 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 ran into a problem with 3 things with earlier code.

1. Auto-increment column, where in the previous code it was set to 0
2. Query operator with double quotes.
3. The execute function should look like the parent execute function with a parameter.

I assume that the first 2 points relate to the database that I use postgresql, I hope the updated code works for all database systems.

Answer 4

Starting with Yii 1.1.14, the CDbCommandBuilder::createMultipleInsertCommand() method is available. If you need to insert multiple records in one query, you should probably use it, because all the other answers in this question are vulnerable to SQL injection , so it's easy to spoil something if you try to implement something like this yourself,

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

For an array of models, you can probably generate $data this way (note that it will not be checked):

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