CODEWITHSUNDEEP
laraveleloquentlaravel-5
Schwesi
Schwesi

Reputation: 4904

ON DUPLICATE KEY UPDATE in Eloquent

I just started laravel and all I want to do is get following query working in Eloquent:

INSERT INTO geschichte (geschichte_id, 
                        geschichte_text1, 
                        geschichte_text2, 
                        geschichte_text3) 
VALUES (:geschichte_id, 
        :geschichte_text1, 
        :geschichte_text2, 
        :geschichte_text3) 
ON DUPLICATE KEY 
UPDATE geschichte_id = :geschichte_id, 
       geschichte_text1 = :geschichte_text1, 
       geschichte_text2 = :geschichte_text2, 
       geschichte_text3 = :geschichte_text3;

Controller function

public function alterGeschichte(Request $request)
{
    $geschichte1 = new Geschichte;
    $geschichte2 = new Geschichte;
    $geschichte3 = new Geschichte;

    $geschichte1 = Geschichte::updateOrCreate(
       ['id' => 1],
       ['geschichte_text1' => $request->geschichte_text1]
    );
    $geschichte2 = Geschichte::updateOrCreate(
       ['id' => 2],
       ['geschichte_text2' => $request->geschichte_text2]
    );
    $geschichte3 = Geschichte::updateOrCreate(
       ['id' => 3],
       ['geschichte_text3' => $request->geschichte_text3]
    );

    $geschichte1->save();
    $geschichte2->save();
    $geschichte3->save();

    return redirect('/geschichte');
}

The problem in more detail

I cannot get the 'on duplicate key update' part to work.
There always is a new entry created for every time I update. I would like the id always to be the same for every entry and just overwrite the older entry with that id.

I would be very thankful for any kind of help. I am struggling with this from hours...

UPDATE

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateGeschichteTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('geschichte', function (Blueprint $table) {
            $table->increments('id');
            $table->text('geschichte_text1');
            $table->text('geschichte_text2');
            $table->text('geschichte_text3');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('geschichte');
    }
}

Upvotes: 12

Views: 31586

Answers (4)

Ilyich
Ilyich

Reputation: 5796

Since Laravel 8 there is upsert method that mimics SQL INSERT ON DUPLICATE KEY UPDATE functionality.

This query builder example:

DB::table('flights')->upsert(
    [
        ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
    ], 
    ['departure', 'destination'], 
    ['price']
);

is equal to the following SQL query:

INSERT INTO flights (departure, destination, price) 
    VALUES ('Chicago', 'New York', 150)
    ON DUPLICATE KEY UPDATE price = 150;

The upsert method has 3 arguments.

  1. Values to insert or update
  2. Column(s) that uniquely identify records within the associated table
  3. Array of columns that should be updated if a matching record already exists in the database

The columns in the second argument MUST have a "primary" or "unique" index.

Full documentation

Upvotes: 16

hamza saktan
hamza saktan

Reputation: 31

updateOrCreate not work like ON DUPLICATE UPDATE you can use upsert

    $data = [
        'user_id' => $id,
        'profile' => $profile
];
use DB::table('tablename')->upsert($data, ['user_id'], ['profile']);

user_id: column must have unique index

Upvotes: 2

user1669496
user1669496

Reputation: 33148

Looks like you don't have a unique key setup on the columns you wish to be unique. In order to use on duplicate key update, you will need that so your database server will know if it needs to insert or update.

Unfortunately, Laravel doesn't have support for on duplicate key update syntax. This would be useful because it tries to insert and if it's already in the table, then it will update the columns accordingly in one query.

Using Laravel's updateOrCreate method, it first queries the table to see if it needs to generate an insert or update statement and then proceeds accordingly. You don't get the advantage of running just one query but at the same time, it's Laravel which is handling all the extra logic so it's a slight trade-off.

Upvotes: 7

Alex Harris
Alex Harris

Reputation: 6412

Most of what is in your controller should not be necessary. I am also a little concerned about the database structure you are using as to why you would perform a task like shown in your controller. The following should be all you need:

public function alterGeschichte(Request $request)
{
    Geschichte::updateOrCreate(
       ['id' => 1],
       ['id' => 1, 'geschichte_text1' => $request->geschichte_text1]
    );

    Geschichte::updateOrCreate(
       ['id' => 2],
       ['id' => 2, 'geschichte_text2' => $request->geschichte_text2]
    );

    Geschichte::updateOrCreate(
       ['id' => 3],
       ['id' => 3, 'geschichte_text3' => $request->geschichte_text3]
    );

    return redirect('/geschichte');
}

If these are creating new records it is most likely because there is no record with those ID's.

Upvotes: 4

Related Questions