Last active
November 4, 2023 10:56
-
-
Save gskema/a182aaf7cc04001aebba9c1aad86b40b to your computer and use it in GitHub Desktop.
PHP PDO / Doctrine DBAL bulk insert query
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
namespace YourApp\Repository\Query; | |
use Doctrine\DBAL\Connection; | |
use Doctrine\DBAL\Schema\Identifier; | |
/** | |
* Class BulkInsertQuery | |
* | |
* @package YourApp\Repository\Query | |
*/ | |
class BulkInsertQuery | |
{ | |
/** @var Connection */ | |
protected $connection; | |
/** @var Identifier */ | |
protected $table; | |
/** @var string[] */ | |
protected $columns = []; | |
/** @var array[] */ | |
protected $valueSets = []; | |
/** @var int[] PDO::PARAM_* */ | |
protected $types = []; | |
/** @var int|null */ | |
protected $lastInsertId = null; | |
/** @var int|null */ | |
protected $numInsertedRows = null; | |
/** | |
* BulkInsertQuery constructor. | |
* | |
* @param Connection $connection | |
* @param string $table | |
*/ | |
public function __construct( | |
Connection $connection, | |
string $table | |
) { | |
$this->connection = $connection; | |
$this->table = new Identifier($table); | |
} | |
/** | |
* @param array $columns | |
* | |
* @return $this | |
*/ | |
public function setColumns(array $columns) | |
{ | |
$this->columns = $columns; | |
return $this; | |
} | |
/** | |
* @param array $valueSets | |
* @param array|null $types | |
* | |
* @return $this | |
*/ | |
public function setValues(array $valueSets, array $types = null) | |
{ | |
$this->valueSets = $valueSets; | |
$this->types = $types; | |
return $this; | |
} | |
/** | |
* @return $this | |
*/ | |
public function execute() | |
{ | |
$sql = $this->getSQL(); | |
$parameters = array_reduce($this->valueSets, function (array $flattenedValues, array $valueSet) { | |
return array_merge($flattenedValues, array_values($valueSet)); | |
}, []); | |
$this->connection->executeQuery($sql, $parameters, $this->getPositionalTypes()); | |
$this->lastInsertId = $this->connection->lastInsertId(); | |
$this->numInsertedRows = count($this->valueSets); | |
return $this; | |
} | |
/** | |
* @return array | |
*/ | |
public function getLastInsertIds() | |
{ | |
$lastInsertIds = []; | |
if (null !== $this->lastInsertId && $this->numInsertedRows > 0) { | |
$lastInsertIds = range( | |
$this->lastInsertId, | |
$this->lastInsertId + $this->numInsertedRows - 1 | |
); | |
} | |
return $lastInsertIds; | |
} | |
/** | |
* @return string | |
*/ | |
protected function getSQL() | |
{ | |
$platform = $this->connection->getDatabasePlatform(); | |
$escapedColumns = array_map(function (string $column) use ($platform) { | |
return (new Identifier($column))->getQuotedName($platform); | |
}, $this->columns); | |
// (id, name, ..., date) | |
$columnString = empty($this->columns) ? '' : '('.implode(', ', $escapedColumns).')'; | |
// (?, ?, ?, ... , ?) | |
$singlePlaceholder = '('.implode(', ', array_fill(0, count($this->columns), '?')).')'; | |
// (?, ?), ... , (?, ?) | |
$placeholders = implode(', ', array_fill(0, count($this->valueSets), $singlePlaceholder)); | |
$sql = sprintf( | |
'INSERT INTO %s %s VALUES %s;', | |
$this->table->getQuotedName($platform), | |
$columnString, | |
$placeholders | |
); | |
return $sql; | |
} | |
/** | |
* @return int[] PDO::PARAM_* | |
*/ | |
protected function getPositionalTypes() | |
{ | |
if (empty($this->types)) { | |
return []; | |
} | |
$types = array_values($this->types); | |
$repeat = count($this->valueSets); | |
$positionalTypes = []; | |
for ($i=1; $i<=$repeat; $i++) { | |
$positionalTypes = array_merge($positionalTypes, $types); | |
} | |
return $positionalTypes; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
It's working great! Just a small change:
The definition should match the property definition. Property is defined as
int[]
and accoring to PHPDoc it can not beNULL
.When property types are added automatically from PHPDoc using an IDE, it might throw a TypeException because of that.