How to insert array data with one query to unique field of database?

Question

I have sample working code for add array of data to database:

Migration:

Schema::create('words', function (Blueprint $table) {
    $table->increments('id');
    $table->string('word')->unique();
    $table->integer('type')->default(0);
    $table->integer('is_active')->default(0);
    $table->timestamps();
});

Code:

$words = [
    ['word' => 'apple',   'type' => 1, 'is_active' => 1],
    ['word' => 'peach',   'type' => 1, 'is_active' => 1],
    ['word' => 'banana',  'type' => 1, 'is_active' => 1]
];

foreach ($words as $word) {
    Word::updateOrCreate($word);
}

In my database words will be unique and for unique inserting I use updateOrCreate() method. In my code have 3 queries to database. How I can insert unique array of data to database with one query? I seen to updateOrInsert() but with it also I can't insert words to database with one query.

Answer 1

You can't out of the box, because Eloquent does not support INSERT IGNORE or ON DUPLICATE KEY constructs.

You will probably need something like this package provides.

This will work for your usecase, but I'm not a big fan of the static approach it uses.

Also keep in mind that this produces MySQL queries and (like using DB::table('x')) it's not using Eloquent, so it will not set/update timestamps on the model, there are no events triggered, etc.