Batch insert in Yii

问题:

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?

回答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.

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