Skip to content

Commit ce1ddb5

Browse files
TigrovTigrov
authored
Rename batchInsert() to insertBatch() and change paremeters order (#829)
1 parent c2deb4c commit ce1ddb5

16 files changed

Lines changed: 185 additions & 60 deletions

‎CHANGELOG.md‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,8 @@
99
- Enh #806: Build `Expression` instances inside `Expression::$params` when build a query using `QueryBuilder` (@Tigrov)
1010
- Enh #766: Allow `ColumnInterface` as column type. (@Tigrov)
1111
- Bug #828: Fix `float` type when use `AbstractCommand::getRawSql()` method (@Tigrov)
12+
- Enh #829: Rename `batchInsert()` to `insertBatch()` in `DMLQueryBuilderInterface` and `CommandInterface`
13+
and change parameters from `$table, $columns, $rows` to `$table, $rows, $columns = []` (@Tigrov)
1214

1315
## 1.3.0 March 21, 2024
1416

‎UPGRADE.md‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,3 +35,22 @@ Add support any scalar values for `$columns` parameter of these methods in your
3535
`Expression::$params` can contain:
3636
- non-unique placeholder names, they will be replaced with unique names.
3737
- `Expression` instances, they will be built when building a query using `QueryBuilder`.
38+
39+
### Rename `batchInsert()` to `insertBatch()`
40+
41+
`batchInsert()` method is renamed to `insertBatch()` in `DMLQueryBuilderInterface` and `CommandInterface`.
42+
The parameters are changed from `$table, $columns, $rows` to `$table, $rows, $columns = []`.
43+
It allows to use the method without columns, for example:
44+
45+
```php
46+
use Yiisoft\Db\Connection\ConnectionInterface;
47+
48+
$values = [
49+
['name' => 'Tom', 'age' => 30],
50+
['name' => 'Jane', 'age' => 20],
51+
['name' => 'Linda', 'age' => 25],
52+
];
53+
54+
/** @var ConnectionInterface $db */
55+
$db->createCommand()->insertBatch('user', $values)->execute();
56+
```

‎docs/guide/en/command/dml.md‎

Lines changed: 20 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -13,20 +13,36 @@ You can use the DML to perform the following operations:
1313

1414
## Batch insert
1515

16-
To insert multiple rows into a table, you can use the `Yiisoft\Db\Command\CommandInterface::batchInsert()` method:
16+
To insert multiple rows into a table, you can use the `Yiisoft\Db\Command\CommandInterface::insertBatch()` method:
1717

1818
```php
1919
use Yiisoft\Db\Connection\ConnectionInterface;
2020

2121
/** @var ConnectionInterface $db */
22-
$db->createCommand()->batchInsert(
22+
$db->createCommand()->insertBatch(
2323
'{{%customer}}',
24-
['name', 'email'],
2524
[
2625
['user1', 'email1@email.com'],
2726
['user2', 'email2@email.com'],
2827
['user3', 'email3@email.com'],
29-
]
28+
],
29+
['name', 'email'],
30+
)->execute();
31+
```
32+
33+
It is possible to insert rows as associative arrays, where the keys are column names.
34+
35+
```php
36+
use Yiisoft\Db\Connection\ConnectionInterface;
37+
38+
/** @var ConnectionInterface $db */
39+
$db->createCommand()->insertBatch(
40+
'{{%customer}}',
41+
[
42+
['name' => 'user1', 'email' => 'email1@email.com'],
43+
['name' => 'user2', 'email' => 'email2@email.com'],
44+
['name' => 'user3', 'email' => 'email3@email.com'],
45+
],
3046
)->execute();
3147
```
3248

‎docs/guide/pt-BR/command/dml.md‎

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -13,13 +13,13 @@ Você pode usar o DML para realizar as seguintes operações:
1313

1414
## Inserção em lote
1515

16-
Para inserir múltiplas linhas em uma tabela, você pode usar o método `Yiisoft\Db\Command\CommandInterface::batchInsert()`:
16+
Para inserir múltiplas linhas em uma tabela, você pode usar o método `Yiisoft\Db\Command\CommandInterface::insertBatch()`:
1717

1818
```php
1919
use Yiisoft\Db\Connection\ConnectionInterface;
2020

2121
/** @var ConnectionInterface $db */
22-
$db->createCommand()->batchInsert(
22+
$db->createCommand()->insertBatch(
2323
'{{%customer}}',
2424
['name', 'email'],
2525
[
@@ -30,6 +30,22 @@ $db->createCommand()->batchInsert(
3030
)->execute();
3131
```
3232

33+
It is possible to insert rows as associative arrays, where the keys are column names.
34+
35+
```php
36+
use Yiisoft\Db\Connection\ConnectionInterface;
37+
38+
/** @var ConnectionInterface $db */
39+
$db->createCommand()->insertBatch(
40+
'{{%customer}}',
41+
[
42+
['name' => 'user1', 'email' => 'email1@email.com'],
43+
['name' => 'user2', 'email' => 'email2@email.com'],
44+
['name' => 'user3', 'email' => 'email3@email.com'],
45+
],
46+
)->execute();
47+
```
48+
3349
## Excluir linhas
3450

3551
Para excluir linhas de uma tabela, você pode usar o método `Yiisoft\Db\Command\CommandInterface::delete()`:

‎src/Command/AbstractCommand.php‎

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,7 @@
1010
use Yiisoft\Db\Expression\Expression;
1111
use Yiisoft\Db\Query\Data\DataReaderInterface;
1212
use Yiisoft\Db\Query\QueryInterface;
13+
use Yiisoft\Db\QueryBuilder\DMLQueryBuilderInterface;
1314
use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
1415
use Yiisoft\Db\Schema\Builder\ColumnInterface;
1516
use Yiisoft\Db\Schema\SchemaInterface;
@@ -65,6 +66,8 @@
6566
* ```
6667
*
6768
* To build `SELECT` SQL statements, please use {@see QueryInterface} and its implementations instead.
69+
*
70+
* @psalm-import-type BatchValues from DMLQueryBuilderInterface
6871
*/
6972
abstract class AbstractCommand implements CommandInterface
7073
{
@@ -195,7 +198,19 @@ public function alterColumn(string $table, string $column, ColumnInterface|strin
195198
return $this->setSql($sql)->requireTableSchemaRefresh($table);
196199
}
197200

201+
/**
202+
* @param string[] $columns
203+
*
204+
* @psalm-param BatchValues $rows
205+
*
206+
* @deprecated Use {@see insertBatch()} instead. It will be removed in version 3.0.0.
207+
*/
198208
public function batchInsert(string $table, array $columns, iterable $rows): static
209+
{
210+
return $this->insertBatch($table, $rows, $columns);
211+
}
212+
213+
public function insertBatch(string $table, iterable $rows, array $columns = []): static
199214
{
200215
$table = $this->getQueryBuilder()->quoter()->quoteSql($table);
201216

@@ -207,7 +222,7 @@ public function batchInsert(string $table, array $columns, iterable $rows): stat
207222
unset($column);
208223

209224
$params = [];
210-
$sql = $this->getQueryBuilder()->batchInsert($table, $columns, $rows, $params);
225+
$sql = $this->getQueryBuilder()->insertBatch($table, $rows, $columns, $params);
211226

212227
$this->setRawSql($sql);
213228
$this->bindValues($params);

‎src/Command/CommandInterface.php‎

Lines changed: 20 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,7 @@
1515
use Yiisoft\Db\Exception\NotSupportedException;
1616
use Yiisoft\Db\Query\Data\DataReaderInterface;
1717
use Yiisoft\Db\Query\QueryInterface;
18+
use Yiisoft\Db\QueryBuilder\DMLQueryBuilderInterface;
1819
use Yiisoft\Db\Schema\Builder\ColumnInterface;
1920

2021
/**
@@ -23,6 +24,7 @@
2324
* A command instance is usually created by calling {@see ConnectionInterface::createCommand}.
2425
*
2526
* @psalm-import-type ParamsType from ConnectionInterface
27+
* @psalm-import-type BatchValues from DMLQueryBuilderInterface
2628
*/
2729
interface CommandInterface
2830
{
@@ -156,13 +158,26 @@ public function alterColumn(string $table, string $column, ColumnInterface|strin
156158
* For example,
157159
*
158160
* ```php
159-
* $connectionInterface->createCommand()->batchInsert(
161+
* $connectionInterface->createCommand()->insertBatch(
160162
* 'user',
161-
* ['name', 'age'],
162163
* [
163164
* ['Tom', 30],
164165
* ['Jane', 20],
165166
* ['Linda', 25],
167+
* ],
168+
* ['name', 'age']
169+
* )->execute();
170+
* ```
171+
*
172+
* or as associative arrays where the keys are column names
173+
*
174+
* ```php
175+
* $connectionInterface->createCommand()->insertBatch(
176+
* 'user',
177+
* [
178+
* ['name' => 'Tom', 'age' => 30],
179+
* ['name' => 'Jane', 'age' => 20],
180+
* ['name' => 'Linda', 'age' => 25],
166181
* ]
167182
* )->execute();
168183
* ```
@@ -174,17 +189,17 @@ public function alterColumn(string $table, string $column, ColumnInterface|strin
174189
* Also note that the created command isn't executed until {@see execute()} is called.
175190
*
176191
* @param string $table The name of the table to insert new rows into.
177-
* @param array $columns The column names.
178192
* @param iterable $rows The rows to be batch inserted into the table.
193+
* @param string[] $columns The column names.
179194
*
180195
* @throws Exception
181196
* @throws InvalidArgumentException
182197
*
183-
* @psalm-param iterable<array-key, array<array-key, mixed>> $rows
198+
* @psalm-param BatchValues $rows
184199
*
185200
* Note: The method will quote the `table` and `column` parameters before using them in the generated SQL.
186201
*/
187-
public function batchInsert(string $table, array $columns, iterable $rows): static;
202+
public function insertBatch(string $table, iterable $rows, array $columns = []): static;
188203

189204
/**
190205
* Binds a parameter to the SQL statement to be executed.

‎src/Debug/CommandInterfaceProxy.php‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -101,7 +101,7 @@ public function alterColumn(string $table, string $column, ColumnInterface|strin
101101
/**
102102
* @psalm-suppress MixedArgument
103103
*/
104-
public function batchInsert(string $table, array $columns, iterable $rows): static
104+
public function insertBatch(string $table, iterable $rows, array $columns = []): static
105105
{
106106
return new self($this->decorated->{__FUNCTION__}(...func_get_args()), $this->collector);
107107
}

‎src/QueryBuilder/AbstractDMLQueryBuilder.php‎

Lines changed: 20 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -32,10 +32,10 @@
3232
use function array_values;
3333
use function count;
3434
use function get_object_vars;
35+
use function gettype;
3536
use function implode;
3637
use function in_array;
3738
use function is_array;
38-
use function is_object;
3939
use function is_string;
4040
use function iterator_to_array;
4141
use function json_encode;
@@ -52,6 +52,7 @@
5252
* @link https://en.wikipedia.org/wiki/Data_manipulation_language
5353
*
5454
* @psalm-import-type ParamsType from ConnectionInterface
55+
* @psalm-import-type BatchValues from DMLQueryBuilderInterface
5556
*/
5657
abstract class AbstractDMLQueryBuilder implements DMLQueryBuilderInterface
5758
{
@@ -62,7 +63,20 @@ public function __construct(
6263
) {
6364
}
6465

66+
/**
67+
* @param string[] $columns
68+
*
69+
* @psalm-param BatchValues $rows
70+
* @psalm-param ParamsType $params
71+
*
72+
* @deprecated Use {@see insertBatch()} instead. It will be removed in version 3.0.0.
73+
*/
6574
public function batchInsert(string $table, array $columns, iterable $rows, array &$params = []): string
75+
{
76+
return $this->insertBatch($table, $rows, $columns, $params);
77+
}
78+
79+
public function insertBatch(string $table, iterable $rows, array $columns = [], array &$params = []): string
6680
{
6781
if (!is_array($rows)) {
6882
$rows = $this->prepareTraversable($rows);
@@ -226,10 +240,11 @@ protected function extractColumnNames(array|Iterator $rows, array $columns): arr
226240
$row = reset($rows);
227241
}
228242

229-
$row = match (true) {
230-
is_array($row) => $row,
231-
$row instanceof Traversable => iterator_to_array($row),
232-
is_object($row) => get_object_vars($row),
243+
$row = match (gettype($row)) {
244+
'array' => $row,
245+
'object' => $row instanceof Traversable
246+
? iterator_to_array($row)
247+
: get_object_vars($row),
233248
default => [],
234249
};
235250

‎src/QueryBuilder/AbstractQueryBuilder.php‎

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@
55
namespace Yiisoft\Db\QueryBuilder;
66

77
use Yiisoft\Db\Command\CommandInterface;
8+
use Yiisoft\Db\Connection\ConnectionInterface;
89
use Yiisoft\Db\Expression\ExpressionInterface;
910
use Yiisoft\Db\Query\QueryInterface;
1011
use Yiisoft\Db\QueryBuilder\Condition\Interface\ConditionInterface;
@@ -24,6 +25,9 @@
2425
*
2526
* AbstractQueryBuilder is also used by {@see CommandInterface} to build SQL statements such as {@see insert()},
2627
* {@see update()}, {@see delete()} and {@see createTable()}.
28+
*
29+
* @psalm-import-type ParamsType from ConnectionInterface
30+
* @psalm-import-type BatchValues from DMLQueryBuilderInterface
2731
*/
2832
abstract class AbstractQueryBuilder implements QueryBuilderInterface
2933
{
@@ -108,9 +112,22 @@ public function alterColumn(string $table, string $column, ColumnInterface|strin
108112
return $this->ddlBuilder->alterColumn($table, $column, $type);
109113
}
110114

115+
/**
116+
* @param string[] $columns
117+
*
118+
* @psalm-param BatchValues $rows
119+
* @psalm-param ParamsType $params
120+
*
121+
* @deprecated Use {@see insertBatch()} instead. It will be removed in version 3.0.0.
122+
*/
111123
public function batchInsert(string $table, array $columns, iterable $rows, array &$params = []): string
112124
{
113-
return $this->dmlBuilder->batchInsert($table, $columns, $rows, $params);
125+
return $this->dmlBuilder->insertBatch($table, $rows, $columns, $params);
126+
}
127+
128+
public function insertBatch(string $table, iterable $rows, array $columns = [], array &$params = []): string
129+
{
130+
return $this->dmlBuilder->insertBatch($table, $rows, $columns, $params);
114131
}
115132

116133
public function bindParam(mixed $value, array &$params = []): string

‎src/QueryBuilder/DMLQueryBuilderInterface.php‎

Lines changed: 15 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,7 @@
1818
* @link https://en.wikipedia.org/wiki/Data_manipulation_language
1919
*
2020
* @psalm-import-type ParamsType from ConnectionInterface
21+
* @psalm-type BatchValues = iterable<iterable<array-key, mixed>>
2122
*/
2223
interface DMLQueryBuilderInterface
2324
{
@@ -27,32 +28,41 @@ interface DMLQueryBuilderInterface
2728
* For example,
2829
*
2930
* ```php
30-
* $sql = $queryBuilder->batchInsert('user', ['name', 'age'], [
31+
* $sql = $queryBuilder->insertBatch('user', [
3132
* ['Tom', 30],
3233
* ['Jane', 20],
3334
* ['Linda', 25],
35+
* ], ['name', 'age']);
36+
* ```
37+
*
38+
* or as associative arrays where the keys are column names
39+
*
40+
* ```php
41+
* $queryBuilder->insertBatch('user', [
42+
* ['name' => 'Tom', 'age' => 30],
43+
* ['name' => 'Jane', 'age' => 20],
44+
* ['name' => 'Linda', 'age' => 25],
3445
* ]);
3546
* ```
3647
*
3748
* @param string $table The table to insert new rows into.
38-
* @param string[] $columns The column names of the table.
3949
* @param iterable $rows The rows to batch-insert into the table.
50+
* @param string[] $columns The column names of the table.
4051
* @param array $params The binding parameters. This parameter exists.
4152
*
4253
* @throws Exception
4354
* @throws InvalidArgumentException
4455
*
4556
* @return string The batch INSERT SQL statement.
4657
*
47-
* @psalm-param string[] $columns
48-
* @psalm-param iterable<iterable<array-key, mixed>> $rows
58+
* @psalm-param BatchValues $rows
4959
* @psalm-param ParamsType $params
5060
*
5161
* Note:
5262
* - That the values in each row must match the corresponding column names.
5363
* - The method will escape the column names, and quote the values to insert.
5464
*/
55-
public function batchInsert(string $table, array $columns, iterable $rows, array &$params = []): string;
65+
public function insertBatch(string $table, iterable $rows, array $columns = [], array &$params = []): string;
5666

5767
/**
5868
* Creates a `DELETE` SQL statement.

0 commit comments

Comments
 (0)