Skip to content

Commit 63f68c0

Browse files
Tigrovvjik
Tigrov
and
vjik
authored
Fix batchInsert() with associative arrays (#769)
* Refactor DMLQueryBuilder * Get uniques using `getTableIndexes()` and `getTableUniques()` * Fix @psalm-var * Fix #61 (point 2) * Fix #61 (point 2) add test * Improve test * Remove methods with `NotSupportedException` * Fix test issues * Fix test issues * Revert "Remove methods with `NotSupportedException`" * Add line to CHANGELOG.md * Change order of checks * Improve performance of quoting column names up to 10% using `array_map()` * Fix `batchInsert()` with associative arrays * Update after merge * Remove old comments * Fix psalm * Add line to CHANGELOG.md --------- Co-authored-by: Sergei Predvoditelev <sergei@predvoditelev.ru>
1 parent 826d6ea commit 63f68c0

3 files changed

Lines changed: 91 additions & 17 deletions

File tree

‎CHANGELOG.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@
1616
- Bug #761: Quote aliases of CTE in `WITH` queries (@Tigrov)
1717
- Chg #765: Deprecate `SchemaInterface::TYPE_JSONB` (@Tigrov)
1818
- Enh #770: Move methods from concrete `Command` class to `AbstractPdoCommand` class (@Tigrov)
19+
- Bug #769, #61: Fix `AbstractDMLQueryBuilder::batchInsert()` for values as associative arrays (@Tigrov)
1920

2021
## 1.1.1 August 16, 2023
2122

‎src/QueryBuilder/AbstractDMLQueryBuilder.php‎

Lines changed: 21 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@
1919

2020
use function array_combine;
2121
use function array_diff;
22+
use function array_fill_keys;
2223
use function array_filter;
2324
use function array_keys;
2425
use function array_map;
@@ -57,39 +58,45 @@ public function batchInsert(string $table, array $columns, iterable $rows, array
5758

5859
$values = [];
5960
$columns = $this->getNormalizeColumnNames('', $columns);
61+
$columnNames = array_values($columns);
62+
$columnKeys = array_fill_keys($columnNames, false);
6063
$columnSchemas = $this->schema->getTableSchema($table)?->getColumns() ?? [];
6164

6265
foreach ($rows as $row) {
6366
$i = 0;
64-
$placeholders = [];
65-
66-
foreach ($row as $value) {
67-
if (isset($columns[$i], $columnSchemas[$columns[$i]])) {
68-
$value = $columnSchemas[$columns[$i]]->dbTypecast($value);
67+
$placeholders = $columnKeys;
68+
69+
foreach ($row as $key => $value) {
70+
/** @psalm-suppress MixedArrayTypeCoercion */
71+
$columnName = $columns[$key] ?? (isset($columnKeys[$key]) ? $key : $columnNames[$i] ?? $i);
72+
/** @psalm-suppress MixedArrayTypeCoercion */
73+
if (isset($columnSchemas[$columnName])) {
74+
$value = $columnSchemas[$columnName]->dbTypecast($value);
6975
}
7076

7177
if ($value instanceof ExpressionInterface) {
72-
$placeholders[] = $this->queryBuilder->buildExpression($value, $params);
78+
$placeholders[$columnName] = $this->queryBuilder->buildExpression($value, $params);
7379
} else {
74-
$placeholders[] = $this->queryBuilder->bindParam($value, $params);
80+
$placeholders[$columnName] = $this->queryBuilder->bindParam($value, $params);
7581
}
7682

7783
++$i;
7884
}
85+
7986
$values[] = '(' . implode(', ', $placeholders) . ')';
8087
}
8188

8289
if (empty($values)) {
8390
return '';
8491
}
8592

86-
$columns = array_map(
93+
$columnNames = array_map(
8794
[$this->quoter, 'quoteColumnName'],
88-
$columns,
95+
$columnNames,
8996
);
9097

9198
return 'INSERT INTO ' . $this->quoter->quoteTableName($table)
92-
. ' (' . implode(', ', $columns) . ') VALUES ' . implode(', ', $values);
99+
. ' (' . implode(', ', $columnNames) . ') VALUES ' . implode(', ', $values);
93100
}
94101

95102
public function delete(string $table, array|string $condition, array &$params): string
@@ -430,13 +437,11 @@ protected function normalizeColumnNames(string $table, array $columns): array
430437
*/
431438
protected function getNormalizeColumnNames(string $table, array $columns): array
432439
{
433-
$normalizedNames = [];
434-
435-
foreach ($columns as $name) {
436-
$normalizedName = $this->quoter->ensureColumnName($name);
437-
$normalizedNames[] = $this->quoter->unquoteSimpleColumnName($normalizedName);
440+
foreach ($columns as &$name) {
441+
$name = $this->quoter->ensureColumnName($name);
442+
$name = $this->quoter->unquoteSimpleColumnName($name);
438443
}
439444

440-
return $normalizedNames;
445+
return $columns;
441446
}
442447
}

‎tests/Provider/CommandProvider.php‎

Lines changed: 69 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -339,7 +339,7 @@ public static function batchInsert(): array
339339
':qp3' => false,
340340
],
341341
],
342-
'with associative values' => [
342+
'with associative values with different keys' => [
343343
'type',
344344
['int_col', 'float_col', 'char_col', 'bool_col'],
345345
'values' => [['int' => '1.0', 'float' => '2', 'char' => 10, 'bool' => 1]],
@@ -356,6 +356,74 @@ public static function batchInsert(): array
356356
':qp3' => true,
357357
],
358358
],
359+
'with associative values with different keys and columns with keys' => [
360+
'type',
361+
['a' => 'int_col', 'b' => 'float_col', 'c' => 'char_col', 'd' => 'bool_col'],
362+
'values' => [['int' => '1.0', 'float' => '2', 'char' => 10, 'bool' => 1]],
363+
'expected' => DbHelper::replaceQuotes(
364+
<<<SQL
365+
INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp0, :qp1, :qp2, :qp3)
366+
SQL,
367+
static::$driverName,
368+
),
369+
'expectedParams' => [
370+
':qp0' => 1,
371+
':qp1' => 2.0,
372+
':qp2' => '10',
373+
':qp3' => true,
374+
],
375+
],
376+
'with associative values with keys of column names' => [
377+
'type',
378+
['int_col', 'float_col', 'char_col', 'bool_col'],
379+
'values' => [['bool_col' => 1, 'char_col' => 10, 'int_col' => '1.0', 'float_col' => '2']],
380+
'expected' => DbHelper::replaceQuotes(
381+
<<<SQL
382+
INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp2, :qp3, :qp1, :qp0)
383+
SQL,
384+
static::$driverName,
385+
),
386+
'expectedParams' => [
387+
':qp0' => true,
388+
':qp1' => '10',
389+
':qp2' => 1,
390+
':qp3' => 2.0,
391+
],
392+
],
393+
'with associative values with keys of column keys' => [
394+
'type',
395+
['int' => 'int_col', 'float' => 'float_col', 'char' => 'char_col', 'bool' => 'bool_col'],
396+
'values' => [['bool' => 1, 'char' => 10, 'int' => '1.0', 'float' => '2']],
397+
'expected' => DbHelper::replaceQuotes(
398+
<<<SQL
399+
INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp2, :qp3, :qp1, :qp0)
400+
SQL,
401+
static::$driverName,
402+
),
403+
'expectedParams' => [
404+
':qp0' => true,
405+
':qp1' => '10',
406+
':qp2' => 1,
407+
':qp3' => 2.0,
408+
],
409+
],
410+
'with shuffled indexes of values' => [
411+
'type',
412+
['int_col', 'float_col', 'char_col', 'bool_col'],
413+
'values' => [[3 => 1, 2 => 10, 0 => '1.0', 1 => '2']],
414+
'expected' => DbHelper::replaceQuotes(
415+
<<<SQL
416+
INSERT INTO [[type]] ([[int_col]], [[float_col]], [[char_col]], [[bool_col]]) VALUES (:qp2, :qp3, :qp1, :qp0)
417+
SQL,
418+
static::$driverName,
419+
),
420+
'expectedParams' => [
421+
':qp0' => true,
422+
':qp1' => '10',
423+
':qp2' => 1,
424+
':qp3' => 2.0,
425+
],
426+
],
359427
];
360428
}
361429

0 commit comments

Comments
 (0)