For example,
php
$sql = $queryBuilder->batchInsert('user', ['name', 'age'], [
['Tom', 30],
['Jane', 20],
['Linda', 25],
]);
Note that the values in each row must match the corresponding column names.
The method will properly escape the column names, and quote the values to be inserted.
/**
* Generates a batch INSERT SQL statement.
* For example,
*
* ```php
* $connection->createCommand()->batchInsert('user', ['name', 'age'], [
* ['Tom', 30],
* ['Jane', 20],
* ['Linda', 25],
* ])->execute();
* ```
*
* Note that the values in each row must match the corresponding column names.
*
* @param string $table the table that new rows will be inserted into.
* @param array $columns the column names
* @param array $rows the rows to be batch inserted into the table
* @return string the batch INSERT SQL statement
*/
public function batchInsert($table, $columns, $rows)
{
if (empty($rows)) {
return '';
}
// SQLite supports batch insert natively since 3.7.11
// http://www.sqlite.org/releaselog/3_7_11.html
$this->db->open();
// ensure pdo is not null
if (version_compare($this->db->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '3.7.11', '>=')) {
return parent::batchInsert($table, $columns, $rows);
}
$schema = $this->db->getSchema();
if (($tableSchema = $schema->getTableSchema($table)) !== null) {
$columnSchemas = $tableSchema->columns;
} else {
$columnSchemas = [];
}
$values = [];
foreach ($rows as $row) {
$vs = [];
foreach ($row as $i => $value) {
if (!is_array($value) && isset($columnSchemas[$columns[$i]])) {
$value = $columnSchemas[$columns[$i]]->dbTypecast($value);
}
if (is_string($value)) {
$value = $schema->quoteValue($value);
} elseif ($value === false) {
$value = 0;
} elseif ($value === null) {
$value = 'NULL';
}
$vs[] = $value;
}
$values[] = implode(', ', $vs);
}
foreach ($columns as $i => $name) {
$columns[$i] = $schema->quoteColumnName($name);
}
return 'INSERT INTO ' . $schema->quoteTableName($table) . ' (' . implode(', ', $columns) . ') SELECT ' . implode(' UNION SELECT ', $values);
}
/**
* This command will update auth tables with data from auth configs
*/
public function actionSyncDeploy()
{
$queryBuilder = new QueryBuilder(Yii::$app->db);
Yii::$app->db->createCommand("SET FOREIGN_KEY_CHECKS=0;")->execute();
if (file_exists($this->authItemConfig)) {
Yii::$app->db->createCommand()->delete('AuthItem')->execute();
$authItem = (require $this->authItemConfig);
$insertAuthItemQuery = $queryBuilder->batchInsert('AuthItem', ['name', 'type', 'description', 'rule_name', 'data'], $authItem);
Yii::$app->db->createCommand($insertAuthItemQuery)->execute();
}
if (file_exists($this->authItemChildConfig)) {
Yii::$app->db->createCommand()->delete('AuthItemChild')->execute();
$authItemChild = (require $this->authItemChildConfig);
$insertAuthItemChildQuery = $queryBuilder->batchInsert('AuthItemChild', ['parent', 'child'], $authItemChild);
Yii::$app->db->createCommand($insertAuthItemChildQuery)->execute();
}
if (file_exists($this->authRuleConfig)) {
Yii::$app->db->createCommand()->delete('AuthRule')->execute();
$authRule = (require $this->authRuleConfig);
$insertAuthRuleQuery = $queryBuilder->batchInsert('AuthRule', ['name', 'data'], $authRule);
Yii::$app->db->createCommand($insertAuthRuleQuery)->execute();
}
Yii::$app->db->createCommand("SET FOREIGN_KEY_CHECKS=1;")->execute();
Yii::$app->db->createCommand("DELETE aa FROM `AuthAssignment` aa LEFT JOIN AuthItem ai ON(aa.item_name = ai.name) WHERE ai.name IS NULL;")->execute();
Yii::$app->cache->flush();
}
/**
* This command will update auth tables with data from auth configs
*/
public function actionSyncDeploy()
{
$queryBuilder = new QueryBuilder(Yii::$app->db);
$authItemTable = Yii::$app->authManager->itemTable;
if ('pgsql' == Yii::$app->db->driverName) {
Yii::$app->db->createCommand("ALTER TABLE \"{$authItemTable}\" DISABLE TRIGGER ALL")->execute();
} else {
Yii::$app->db->createCommand("SET FOREIGN_KEY_CHECKS=0;")->execute();
}
if (file_exists($this->authItemConfig)) {
Yii::$app->db->createCommand()->delete($authItemTable)->execute();
$authItem = (require $this->authItemConfig);
if (!empty($authItem)) {
$insertAuthItemQuery = $queryBuilder->batchInsert($authItemTable, ['name', 'type', 'description', 'rule_name', 'data'], $authItem);
Yii::$app->db->createCommand($insertAuthItemQuery)->execute();
}
}
if (file_exists($this->authItemChildConfig)) {
Yii::$app->db->createCommand()->delete(Yii::$app->authManager->itemChildTable)->execute();
$authItemChild = (require $this->authItemChildConfig);
if (!empty($authItemChild)) {
$insertAuthItemChildQuery = $queryBuilder->batchInsert(Yii::$app->authManager->itemChildTable, ['parent', 'child'], $authItemChild);
Yii::$app->db->createCommand($insertAuthItemChildQuery)->execute();
}
}
if (file_exists($this->authRuleConfig)) {
Yii::$app->db->createCommand()->delete(Yii::$app->authManager->ruleTable)->execute();
$authRule = (require $this->authRuleConfig);
if (!empty($authRule)) {
$insertAuthRuleQuery = $queryBuilder->batchInsert(Yii::$app->authManager->ruleTable, ['name', 'data'], $authRule);
Yii::$app->db->createCommand($insertAuthRuleQuery)->execute();
}
}
if ('pgsql' == Yii::$app->db->driverName) {
Yii::$app->db->createCommand("ALTER TABLE \"{$authItemTable}\" ENABLE TRIGGER ALL")->execute();
} else {
Yii::$app->db->createCommand("SET FOREIGN_KEY_CHECKS=1;")->execute();
}
$this->processAssignments();
Yii::$app->cache->flush();
}