An efficient query generator for mass resource import, distinguishing between new records and records to update.
An efficient query generator for mass resource import, distinguishing between new records and records to update. This library uses MySQL's ON DUPLICATE KEY UPDATE
feature.
I highly recommend you at least skim through my blog about this library to get a better understanding of this library.
You can add this library as a local, per-project dependency to your project using Composer:
composer require kfirba/import-query-generator
use Kfirba\QueryGenerator;
$table = 'users';
$data = [
['name' => 'John', 'email' => '[email protected]', 'password' => 'hashed_password', 'created_at' => date('Y-m-d'), 'updated_at' => date('Y-m-d')],
['name' => 'Jane', 'email' => '[email protected]', 'password' => 'hashed_password', 'created_at' => date('Y-m-d'), 'updated_at' => date('Y-m-d')],
['name' => 'Susy', 'email' => '[email protected]', 'password' => 'hashed_password', 'created_at' => date('Y-m-d'), 'updated_at' => date('Y-m-d')],
];
$excludedColumnsFromUpdate = ['password', 'created_at'];
$queryObject = (new QueryGenerator)->generate($table, $data, $excludedColumnsFromUpdate);
$queryObject->getQuery();
// -> "insert into `users` (`name`,`email`,`password`,`created_at`,`updated_at`) values (?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?) on duplicate key update `name`=VALUES(`name`),`email`=VALUES(`email`),`updated_at`=VALUES(`updated_at`)"
$queryObject->getBindings();
// -> ['John', '[email protected]', 'hashed_password', '2018-01-12', '2018-01-12', 'Jane', '[email protected]', 'hashed_password', '2018-01-12', '2018-01-12', 'Susy', '[email protected]', 'hashed_password', '2018-01-12', '2018-01-12']
As you may have noticed, the generator defaults to column=VALUES(column)
since this is usually what we use when we attempt to bulk import some data.
Need another behavior? You can submit a PR or just open an issue and we can talk about it 🤓.
This package is open-sourced software licensed under the MIT license.