Framework: [Request] Implement Insert ignore and insert on duplicate

[dirkpostma]

It would be very nice to have "upsert" functionality in Eloquent. That is: insert a record, but if key already exist, update the record.

As proposed here:
http://forums.laravel.io/viewtopic.php?id=4180

_MySQL_
MySQL, for example, supports the use of INSERT ... ON DUPLICATE KEY UPDATE syntax[1] which can be used to achieve the a similar effect with the limitation that the join between target dans source has to be made only on PRIMARY KEY or UNIQUE constraints, which is not required in the ANSI/ISO standard. It also supports REPLACE INTO syntax,[2] which first attempts an insert, and if that fails, deletes the row, if exists, and then inserts the new one. There is also an IGNORE clause for the INSERT statement,[3] which tells the server to ignore "duplicate key" errors and go on (existing rows will not be inserted or updated, but all new rows will be inserted).

_SQLite_
SQLite's INSERT OR REPLACE INTO works similarly. It also supports REPLACE INTO as an alias for compatibility with MySQL.[4]

_SQL Server_
Microsoft SQL extends with supporting guards and also with supporting Left Join via WHEN NOT MATCHED BY SOURCE clauses.

_Postgres SQL_ - taken from http://www.postgresql.org/docs/current/ … RT-EXAMPLE
This approach seems to rely on a) first attempting an UPDATE and if successful returning to the next item in the batch of records, otherwise it will attempt to do an INSERT and if it fails it is caught and just passed to the next item in the loop.

[dirkpostma]

Oh, I see it's also posted on uservoice.

http://laravel.uservoice.com/forums/175973-laravel-4/suggestions/3535821-provide-support-for-bulk-insert-with-update-such-

I'm sorry that I don't know where to issue requests, either here or on uservoice? I'm pretty new to these systems. If I shoudn't post it here, than I'm very sorry, you can close it.

[taylorotwell]

Issues and requests can be posted here. That user voice isn't really an "official" Laravel site.

[JoostK]

Note that SQLite's INSERT OR REPLACE INTO is not strictly an upsert as it has side effects and a major flaw, which makes it impractical in most cases, see http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace for details.

I've actually implemented the method described by http://stackoverflow.com/a/7511635/171436 and compared performance with first UPDATE, then INSERT if not available and the latter method was quite a bit faster. Not only for only updates (surely that's faster, only one less complex query then as well) but also for all inserts (empty database) the two-query approach was still faster.

So, the simplest and most performant approach would be the same as described for PostgreSQL.

[dirkpostma]

Some more info that could help:

Laravel might need the ID of the record that is inserted or updated. In case of an insert, mysql_insert_id() returns the last inserted ID. However, in case of an update, it normally doesn't. However, there is a way to get this done:

$query = "INSERT INTO table (column1, column2, column3) VALUES (value1, value2, value3)
    ON DUPLICATE KEY UPDATE SET column1 = value1, column2 = value2, column3 = value3, id=LAST_INSERT_ID(id)";
$my_id = mysql_insert_id();

See also http://stackoverflow.com/questions/2634152/getting-mysql-insert-id-while-using-on-duplicate-key-update-with-php

[abishekrsrikaanth]

I am waiting for this to be implemented on Laravel and I would prefer a syntax something like this..

When using Eloquent

$data = array();
$modelObj = array();
Model::CreateOrUpdate(function ($modelObj) use($data) {
           $modelObj->columnName = $data["value"];
 }, function ($modelObj) use($data) {
           $modelObj->columnName = $data["value"];
           $modelObj->where('columnName','=','value');
});

When using Query Builder

$data = array();
$modelObj = array();
DB::CreateOrUpdate(function ($query) use($data) {
       $query->columnName = $data["value"];
}, function ($query) use($data) {
       $query->columnName = $data["value"];
       $query->where('columnName','=','value');
});

CreateOrUpdate taking 2 parameters

1. Insert Object
2. Update Object ON Duplicate

I am not sure how feasible this would be to build.
I am using a RAW query to perform this and I feel this would be a great addition to Laravel.

[taylorotwell]

Not going to implement something like this at this time.

[Globegitter]

@taylorotwell Any specific reason this won't get implemented? Would be highly useful.

[briedis]

The lack of this feature is annoying.

[taylorotwell]

Then make a pull request. If it's clean and simple it will be considered
for merge.

On Saturday, November 22, 2014, Mārtiņš Briedis [email protected]
wrote:

The lack of this feature is annoying.

—
Reply to this email directly or view it on GitHub
https://github.com/laravel/framework/issues/1382#issuecomment-64084063.

[iateadonut]

taylorotwell,

I put something like this in one of my models today (comments are things I was thinking about as writing this). Would something like this be too complex? If not, I will try to modify Eloquent/Model.php.

The other question is that this doesn't function exactly like replace in MySQL; it would work like the author of this bug report wanted instead: https://bugs.mysql.com/bug.php?id=8173 - would that be acceptable?

To others possibly reading this and trying to find a solution, THIS CODE HASN'T BEEN TESTED SO REWRITE BEFORE PUTTING IN YOUR MODEL:

protected $fillable = array( unique columns );

public static function replace($array){
    $w_array = array();
//new protected property $unique_key ?
//if fillables isn't set, set them from fields in unique key?
//if array doesn't have all fillables, return exception
    foreach( $this->fillable as $c ) {
        $w_array[$c] = $array[$c];
    }
    $s = static::firstOrNew( $w_array );

    foreach($array as $col=>$value)
    {
        $s->$col = $value;
    }
    if($s->timestamps){
        $now = \Carbon\Carbon::now();
        $s->created_at = $now;
        $s->updated_at = $now;
    }
    $s->save();
}

[iateadonut]

   protected $fillable = array( unique columns );

    public static function replace($array){

            $a = new static();

            $w_array = array();
            foreach( $a->getFillable() as $c ) {
                    $w_array[$c] = $array[$c];      }

            $s = self::firstOrNew( $w_array );
            foreach($array as $col=>$value){
                    $s->$col = $value;      }

            if ( $s->exists ) {
                    //use update if it has a composite primary key
                    $b = DB::table($a->getTable());
                    foreach ( $a->getFillable() as $c ) {
                            $b->where($c, $array[$c]);      }
                    $b->update($array);
            } else {
                    $s->save();  
            }

    }

[iateadonut]

if your tables all have id's instead of composite keys, you can just do:

   $s->save()

instead of

        if ( $s->exists ) {
                //use update if it has a composite primary key
                $b = DB::table($a->getTable());
                foreach ( $a->getFillable() as $c ) {
                        $b->where($c, $array[$c]);      }
                $b->update($array);
        } else {
                $s->save();  
        }

[kfcviptel]

Argh, for once I was going to use the qery builder. But alas, back to raw queries again :(

[iateadonut]

Just put the replace function I have above in your model. Any questions?

I have a model Eloquent2 that extends Eloquent. Any model that I want to have a replace function, I just extend Eloquent2 instead of Eloquent. Alternatively, you can use that:

<?php

class Eloquent2 extends Eloquent
{

    public static function insertIgnore($array){
        $a = new static();
        if($a->timestamps){
            $now = \Carbon\Carbon::now();
            $array['created_at'] = $now;
            $array['updated_at'] = $now;
        }
        DB::insert('INSERT IGNORE INTO '.$a->table.' ('.implode(',',array_keys($array)).
                ') values (?'.str_repeat(',?',count($array) - 1).')',array_values($array));
    }

    public static function replace($array){
        $a = new static();
        if($a->timestamps){
            $now = \Carbon\Carbon::now();
            $array['created_at'] = $now;
            $array['updated_at'] = $now;
        }
        DB::insert('REPLACE INTO '.$a->table.' ('.implode(',',array_keys($array)).
                ') values (?'.str_repeat(',?',count($array) - 1).')',array_values($array));
    }

}

[ncla]

Someone created Eloquent trait for this, go check it out.
https://github.com/yadakhov/insert-on-duplicate-key

[staudenmeir]

I've created an UPSERT package for all databases: https://github.com/staudenmeir/laravel-upsert