kfirba/import-query-generator

An efficient query generator for mass resource import, distinguishing between new records and records to update.

Maintainers

Details

github.com/kfirba/import-query-generator

Source

Issues

Installs: 56 307

Dependents: 0

Suggesters: 0

Security: 0

Stars: 35

Watchers: 2

Forks: 9

Open Issues: 1

v1.0 2018-01-12 10:38 UTC

Requires (Dev)

MIT 52ecdddba0e8e3839dc4320aae2083b771f1c7a4

  • Kfir Bens-Ami <kfirba2.woop@gmail.com>

This package is auto-updated.

Last update: 2024-05-08 14:19:05 UTC

README

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.

Preface

I highly recommend you at least skim through my blog about this library to get a better understanding of this library.

Installation

You can add this library as a local, per-project dependency to your project using Composer:

composer require kfirba/import-query-generator

Usage

use Kfirba\QueryGenerator;

$table = 'users';
$data = [
    ['name' => 'John', 'email' => 'john@example.com', 'password' => 'hashed_password', 'created_at' => date('Y-m-d'), 'updated_at' => date('Y-m-d')],
    ['name' => 'Jane', 'email' => 'jane@example.com', 'password' => 'hashed_password', 'created_at' => date('Y-m-d'), 'updated_at' => date('Y-m-d')],
    ['name' => 'Susy', 'email' => 'susy@example.com', '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', 'john@example.com', 'hashed_password', '2018-01-12', '2018-01-12', 'Jane', 'jane@example.com', 'hashed_password', '2018-01-12', '2018-01-12', 'Susy', 'Susy@example.com', '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 🤓.

License

This package is open-sourced software licensed under the MIT license.