批量插入Yii

b1uwtaje  于 2022-11-19  发布在  其他
关注(0)|答案(4)|浏览(185)

我需要在Yii中插入多个ActiveRecord对象，如果把它们全部插入

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

现在我需要在一个查询中插入所有这些记录，在使用活动记录期间如何进行？

yii

4条答案

按热度按时间
inkz8wg9

inkz8wg91#

此类的新版本

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

用法为：

$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();
赞(0）分享  回复(0）举报  2022-11-09
ars1skjm

ars1skjm2#

虽然不完全像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;
  }
}
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();
}
}

用法为：

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

当然，它可以做得更详细和扩展，以允许更新等
希望这对你有帮助。

赞(0）分享  回复(0）举报  2022-11-09
jjjwad0x

jjjwad0x3#

Yii 1.1.14提供了CDbCommandBuilder::createMultipleInsertCommand()方法，如果你需要在一个查询中插入多条记录，你应该使用它，因为这个问题中的所有其他答案都容易受到SQL injection的攻击，所以如果你自己尝试实现类似的东西，很容易把事情搞砸。

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

对于模型数组，您可能会以这种方式生成$data（请注意，它不会进行任何验证）：

$data = [];
foreach ($models as $model) {
    $data[] = $model->getAttributes();
}
赞(0）分享  回复(0）举报  2022-11-09
jaql4c8m

jaql4c8m4#

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

}
我遇到的问题与3件事与早期的代码。
1.在早期代码中设置为0的位置自动递增列
1.包含双引号的查询语句。
1.执行函数应类似于带参数父执行函数
我猜前2点是与我正在使用的postgresql数据库有关的，希望更新后的代码适用于所有数据库系统。

赞(0）分享  回复(0）举报  2022-11-09
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com