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