CODEWITHSUNDEEP
phpmysqllaravellaravel-5.6
BritishWerewolf
BritishWerewolf

Reputation: 3968

How to insert multiple rows with multiple different columns in Laravel

I just tried the following command and received undesired results.

DB::table('locations')->insert([
    ['code' => 'YC', 'name' => 'York Clifton'],
    ['code' => 'YK', 'name' => 'York'],

    ['postcode' => 'DR1',  'name' => 'Jason'],
    ['postcode' => 'DLR',  'name' => 'Beckton']
]);

The above would insert data in the table as such:

                Expected                                         Actual
+-----+--------------+------+----------+        +----+--------------+------+----------+
| id  |     name     | code | postcode |        | id |     name     | code | postcode |
+-----+--------------+------+----------+        +----+--------------+------+----------+
|   1 | York Clifton | YC   | NULL     |        |  1 | York Clifton | YC   | NULL     |
|   2 | York         | YK   | NULL     |        |  2 | York         | YK   | NULL     |
|   3 | Jason        | NULL | DR1      |        |  3 | DR1          | Jaso | NULL     |
|   4 | Beckton      | NULL | DLR      |        |  4 | DLR          | Beck | NULL     |
+-----+--------------+------+----------+        +----+--------------+------+----------+

The locations table is built using the following snippet:

$table->string('name', 100);
$table->string('code', 4)->nullable();
$table->string('postcode', 10)->nullable();

Naturally, my desired outcome is to insert four rows into the database; the first two will fill the code and name fields, whereas the second two inserts will fill postcode and name.

I have looked at the documentation which says:

The query builder also provides an insert method for inserting records into the database table. The insert method accepts an array of column names and values:

You may even insert several records into the table with a single call to insert by passing an array of arrays. Each array represents a row to be inserted into the table.

I'm not entirely sure what Laravel is doing under the hood, but it seems that it prebuilds the insert statements and then inserts the data, ignoring the column name keys.
To avoid the problem I just separated the insert statements with different column names.
This leads me to think, why bother having column keys for all records if they are superfluous (with the exception of the keys in the first array)? Why not have two parameters for the insert method; one with an array of column names, and one with the data.

The documentation doesn't say that array keys must all be the same, so if I am missing something then I'd be grateful if someone could provide some insight into why this doesn't work.

tl;dr

How do I insert multiple rows into a table, when using different column names?

Upvotes: 4

Views: 3775

Answers (1)

Mihai Matei
Mihai Matei

Reputation: 24276

Looking at the Laravel's code just seen the reason of this behaviour. Apparently Laravel compiles the insert query as a batch insert, and not as separate insert queries for each passed array.

In the insert method you can see how the query is generated:

$sql = $this->grammar->compileInsert($this, $values);

If you go further in the compileInsert method you will notice that the columns of the query are generated from the first array passed only:

$columns = $this->columnize(array_keys(reset($values)));

// We need to build a list of parameter place-holders of values that are bound
// to the query. Each insert should have the exact same amount of parameter
// bindings so we will loop through the record and parameterize them all.
$parameters = [];

foreach ($values as $record) {
    $parameters[] = '('.$this->parameterize($record).')';
}

$parameters = implode(', ', $parameters);

return "insert into $table ($columns) values $parameters";

So basically, your insert call will execute the query:

INSERT INTO `locations` (`code`, `name`) 
VALUES ('YC', 'York Clifton'), 
       ('YK', 'York'),
       ('DR1', '...')

However, you can insert all the entries by a single call, by giving all the columns in the locations table:

DB::table('locations')->insert([
    ['code' => 'YC', 'name' => 'York Clifton', 'postcode' => null],
    ['code' => 'YK', 'name' => 'York', 'postcode' => null],

    ['code' => null, 'name' => 'Jason', 'postcode' => 'DR1'],
    ['code' => null, 'name' => 'Beckton', 'postcode' => 'DLR']
]);

Upvotes: 5

Related Questions