Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Mass (bulk) insert or update on duplicate for Laravel 4/5
/**
* Mass (bulk) insert or update on duplicate for Laravel 4/5
*
* insertOrUpdate([
* ['id'=>1,'value'=>10],
* ['id'=>2,'value'=>60]
* ]);
*
*
* @param array $rows
*/
function insertOrUpdate(array $rows){
$table = \DB::getTablePrefix().with(new self)->getTable();
$first = reset($rows);
$columns = implode( ',',
array_map( function( $value ) { return "$value"; } , array_keys($first) )
);
$values = implode( ',', array_map( function( $row ) {
return '('.implode( ',',
array_map( function( $value ) { return '"'.str_replace('"', '""', $value).'"'; } , $row )
).')';
} , $rows )
);
$updates = implode( ',',
array_map( function( $value ) { return "$value = VALUES($value)"; } , array_keys($first) )
);
$sql = "INSERT INTO {$table}({$columns}) VALUES {$values} ON DUPLICATE KEY UPDATE {$updates}";
return \DB::statement( $sql );
}
@itsazzad

This comment has been minimized.

Copy link

@itsazzad itsazzad commented May 2, 2016

👍 💯

@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Jun 29, 2016

Why is this not in Laravel as default?! 👍 💯
Works perfectly, love it. Saved me many queries!

@nicolas-t

This comment has been minimized.

Copy link

@nicolas-t nicolas-t commented Oct 10, 2016

🔥

@raphadko

This comment has been minimized.

Copy link

@raphadko raphadko commented Oct 20, 2016

+1 this should be in laravel by default

@omitobi

This comment has been minimized.

Copy link

@omitobi omitobi commented Apr 25, 2017

This still is working till today.. super thanks 👍 💯

@Netsel

This comment has been minimized.

Copy link

@Netsel Netsel commented Nov 7, 2017

Work fine, I used this to migrate millions rows.
I just add this to handle null value (which should not be replace by empty string) :
return ($value !== '' && $value !== null) ? '"'.str_replace('"', '""', $value).'"' : 'null';

@walkerdigital

This comment has been minimized.

Copy link

@walkerdigital walkerdigital commented May 9, 2018

This was really helpful. I'll add for anyone needing it - if you want to omit a "created_at" timestamp you can inject this right before the $updates variable:

if(isset($first['created_at'])) { unset($first['created_at']); }

This will keep the created date from being updated.

@hidekuro

This comment has been minimized.

Copy link

@hidekuro hidekuro commented Jul 5, 2018

This is not firing model events. It's a bad code when using model events and observers.

@KieronWiltshire

This comment has been minimized.

Copy link

@KieronWiltshire KieronWiltshire commented Jul 20, 2018

It also doesn't protect against SQL injection

@SalientAnimal

This comment has been minimized.

Copy link

@SalientAnimal SalientAnimal commented Jan 10, 2019

I need help creating something similar for a PHP form. I have tried a few ways but I am not getting it to work. Would appreciate it if anyone here would be willing to help me out.

@ab-kily

This comment has been minimized.

Copy link

@ab-kily ab-kily commented Jul 28, 2019

It is better to use \DB::connection()->getPdo()->quote($value) instead of str_replace because of the problem when values contain slashes. Just replace the corresponding code with this:

...
        $values = implode( ',', array_map( function( $row ) {
            return '('.implode( ',',
                array_map( function( $value ) { return \DB::connection()->getPdo()->quote($value); } , $row )
            ).')';
        } , $rows )
        );
...
@rassemdev

This comment has been minimized.

Copy link

@rassemdev rassemdev commented Oct 9, 2019

where are you guys putting this code! In controller or method!

@francoisauclair911

This comment has been minimized.

Copy link

@francoisauclair911 francoisauclair911 commented Oct 30, 2019

@rassemdev it would be in your specific model file. I would make it a trait to be able to reuse it on other models or implement it on Model.php (all models extends this one)

@fernando-ayon

This comment has been minimized.

Copy link

@fernando-ayon fernando-ayon commented Mar 12, 2020

@rassemdev it would be in your specific model file. I would make it a trait to be able to reuse it on other models or implement it on Model.php (all models extends this one)

Yeah, but the class Model is part of a vendor package. Do not update dependencies that are not your own because they might change in the future. Create a trait and use it in the models that need it, it is quite possible that not all of them will use this functionality.

@icanary

This comment has been minimized.

Copy link

@icanary icanary commented Apr 11, 2020

@rassemdev it would be in your specific model file. I would make it a trait to be able to reuse it on other models or implement it on Model.php (all models extends this one)

you can't create it as a trait to your models, because it can work only with single object. But must work with bunch of them. So in this case trait a good idea for repositories, but not for models.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.