How to insert multiple rows with multiple different columns in Laravel

Question

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?

Could you please let us know what is the version of your laravel? — Tahir Afridi
– Tahir Afridi, Commented Jun 26, 2018 at 12:15
@TahirAfridi The version is tagged under the question - Laravel 5.6. Yes, I am using the facade. I am not getting an error, just undesired behaviour — BritishWerewolf
– BritishWerewolf, Commented Jun 26, 2018 at 12:52

BritishWerewolf · Accepted Answer · 2018-06-28 09:10:24Z

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']
]);

Collectives™ on Stack Overflow

How to insert multiple rows with multiple different columns in Laravel

tl;dr

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

tl;dr

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related