yadakhov / insert-on-duplicate-key Goto Github PK

First, it is a very useful function for laravel. Just save my life.
But, when i using this function, i found that there are no handling for timestamps.
For example: created_at,updated_at
So, if we have the improved version. i would say this is one of crucial part for further development.

Noticed a few people forking this repo and adding a table prefix.

I'll incorporate this feature to the repo to save people from forking.

Here's a test to fetch 500 rows which has 20 columns from a table, then insert them back without any changes:

$datas = App\SomeModel->limit(500)->get()->toArray();
App\SomeModel::insertOnDuplicateKey($datas);

The laravel-debugbar's timeline shows that inserting data spend ~250ms to excute while the insert sql just excute for ~30ms

It's seems that the ~200ms overheads comes from excuting Connection->affectingStatement(): https://github.com/illuminate/database/blob/0e126944cf0fb3da8fa456fefc34dcf65e80181d/Connection.php#L469 and it's just doing PDO value binding then to excute them.

So does this overheads produced by PDO or the Eloquent itself?

The table contains 'create_time' and 'update_time' fields, how to automatically fill the data of these two fields?

Copied from: #7

@yadakhov Thank you for creating this plugin.

Too bad the non default connection is not read from the instance of the model.

\App\Model::on('otherconnection')->getModel()->insertOnDuplicateKey([[....]]); // will still use the default connection

when rewriting the function to a non static, the instance settings are available.

public function insertOnDuplicateKey(array $data, array $updateColumns = null)
    {
        if (empty($data)) {
            return false;
        }

        // Case where $data is not an array of arrays.
        if (!isset($data[0])) {
            $data = [$data];
        }

        $sql = self::buildInsertOnDuplicateSql($data, $updateColumns);

        $data = self::inLineArray($data);

        return $this->getConnection()->affectingStatement($sql, $data);
    }

The insertOnDuplicateKey function works great, except when trying to pass strings in the $updateColumns parameter, as the resulting SQL doesn't enclose the value in quotes to treat them as strings:

SponsoredCallWallet::insertOnDuplicateKey( [ 'msisdn' => $msisdn->toStrict(), 'client_id' => ServiceRequest::client()->id, 'sponsoredcall_campaign_id' => $campaign->id, 'balance' => $duration, 'created_at' => Carbon::now()->format( 'Y-m-d H:i:s' ), 'updated_at' => Carbon::now()->format( 'Y-m-d H:i:s' ), ], [ 'updated_at' => Carbon::now()->format( 'Y-m-d H:i:s' ), 'balance' => 'balance + ' . $duration, ] );

Is there something I'm doing wrong?

Hi, sometimes I need to perform some "complex" logic instead of a simple update in a ON DUPLICATE KEY query, for example

ON DUPLICATE KEY UPDATE
`counter` = `counter` + VALUES(`counter`)

How can I achieve that?

Something like this will be a good solution imho:

Model::insertOnDuplicateKey($data, [
    'counter' => 'sum',
    'last_name' => 'replace,
]); @

Maybe it would be better to use Model class property $connection instead of Facade.

For example I am using Eloquent with Slim framework and instead of using Illuminate\Support\Facades\DB I have Illuminate\Database\Capsule\Manager as DB (http://stackoverflow.com/a/29500701) but if this trait tries to use Support\Facades\DB i'm in a trouble.

this all is not necessary as Model itself is aware of its connection ($connection) so it would be better to use it instead of having direct dependency to Illuminate\Support\Facades package

This trait doesn't support multiple scheme, i change it but i can't open pull req. ,

I change

$sql = 'REPLACE INTO `' . static::getTablePrefix()....

To :

protected static function buildReplaceSql(array $data,$scheme=null)
     {

    $first = static::getFirstRow($data);
        if($scheme == null)
             $scheme = env('DB_DATABASE');

  $sql  = "REPLACE INTO `".$scheme.'`.`' . static::getTablePrefix()...

so i forked HERE

I think the documentation should mention that the order of key value pairs is important. If it's different for one record, insert/update will yield unexpected results. For example:

    $users = [
        ['id' => 1, 'email' => '[email protected]', 'name' => 'User One'],
        ['email' => '[email protected]', 'id' => 2, 'name' => 'User Two'],
        ['email' => '[email protected]', 'name' => 'User Three', 'id' => 3],
    ];

Each array has the same keys but in different order. After array_values runs, the keys turn into integer indexes, which disregard which value was attached to which key. I think this should be either noted in the docs or dealt with in the code (now I run ksort on each array).

SQLite uses the syntax

INSERT OR IGNORE instead of INSERT IGNORE

Hi,
I noticed that in some particular cases the insertOnDuplicateKey method creates a wrong record in the table. This is an example of input with the table schema, at the very bottom.
Simply inserting the data, starting from an empty table,

AwnRecord::insertOnDuplicateKey($records);

results in the insertion of only 50 records (out of 92 as input) one of which is totally wrong and even invalid, i.e. the is_valid_solarRad field, defined as boolean, is equal to 127 and tstamp, a datetime field, equals 0000-00-00 00:00:00 (which I believe it's not a valid datetime value)

What's wrong with the code/data?
How can the database even allow for creating an invalid type field?
If of any relevance, I noticed the not inserted records are mostly empty, although null is a valid value for those columns (and relevant data in my case).

Source: https://github.com/yadakhov/insert-on-duplicate-key/blob/master/src/InsertOnDuplicateKey.php#L207

Example: insertOnDuplicateKey(..., [ 'col' => 'value' ])

This just sticks 'value' straight into the SQL without sanitisation:
ON DUPLICATE KEY UPDATE col = value

@yadakhov

Hello. I had the need to work with json in mysql and in the case of the insert I needed to use json_merge. I removed the array validation from the $ updateColumns variable and changed the buildInsertOnDuplicateSql method to:

protected static function buildInsertOnDuplicateSql(array $data, $updateColumns = null)
{
$first = static::getFirstRow($data);

$sql = 'INSERT INTO `' . static::getTablePrefix() . static::getTableName() . '`(' . static::getColumnList($first) . ') VALUES' . PHP_EOL;

$sql .= static::buildQuestionMarks($data) . PHP_EOL;

$sql .= 'ON DUPLICATE KEY UPDATE ';

if (empty($updateColumns)) {
$sql .= static::buildValuesList($first);
} else if(is_array($updateColumns)) {
$sql .= static::buildValuesList(array_combine($updateColumns, $updateColumns));
} else if(is_string($updateColumns)) {
$sql .= $updateColumns;
}

return $sql;
}

Thanks for code. I love you hahahah

Thanks for a great library - does exactly what I need!

I have an interesting issue: when I insertIgnore, insertReplace or insertOnDuplicateKey for more than approximately 7200 records with 9 columns, then it suddenly starts timing out.

Less than that number of rows or columns completes very quickly (<5 seconds). Above that number suddenly starts timing out - and increasing the PHP max execution time still had no effect when set to 10 minutes (still timing out); then I figured something else must be wrong.

I solved it in this way:

$videoLogArrayChunks = array_chunk($videoLogArray, 3000);
foreach ($videoLogArrayChunks as $chunk) {
   VideoLog::insertIgnore($chunk);
}

Not sure if it's an issue or something else that I did incorrectly.

I'd like to be able to use this with DB::table('my-table')->insertOnDuplicateKey($data);, is that possible? I'm not sure how to make the DB::class use a trait.

Hello! Thanks for this package. I wait it a long time)
If I pass associative array to insertIgnore method

            App\Models\Page::insertIgnore([
                'page1' => [
                    'search_request_id' => 1,
                    'url' => 'url1',
                    'position' => 0,
                    'hash' => 'hash1',
                ],
                'page2' => [
                    'search_request_id' => 1,
                    'url' => 'url2',
                    'position' => 0,
                    'hash' => 'hash3',
                ],
            ]);

or such array

            App\Models\Page::insertIgnore([
                4 => [
                    'search_request_id' => 1,
                    'url' => 'url1',
                    'position' => 0,
                    'hash' => 'hash1',
                ],
                5 => [
                    'search_request_id' => 1,
                    'url' => 'url2',
                    'position' => 0,
                    'hash' => 'hash3',
                ],
            ]);

it throws me exception pointed in the issue title. I don't know about associative array: if it bug or not. But when I do such loop

                    foreach ($pages->chunk(100) as $chunk) {
                        Page::insertIgnore($chunk->toArray());
                    }

on second iteration $chunk->toArray() pass next array to insertIgnore

    [
        101 => [...],
        102 => [...],
    ]

If I use standart Eloquent insert() instead of insertIgnore() exception is not thrown. I think it is a bug in insertIgnore() and other methods.

Since we have INSERT IGNORE and INSERT.. ODKU and we might as well have the trifecta and support REPLACE

http://dev.mysql.com/doc/refman/5.7/en/replace.html

There are some cases where you wouldn't want to update all columns.

Please, tag version 1.x
composer does not want to download the new version in tags 0.x.x

Hi there,

What would be really amazing would be to allow incremental values on update, something like this INSERT:

INSERT INTO table 
  (a, counter_elem) 
VALUES 
  (1, 1),
  (2, 1)
ON DUPLICATE KEY UPDATE counter_elem = counter_elem + 1;

In this example, with counter_elem incrementing by one on each duplicate. In my project, I have an aggregation table that aggregates the values of a transaction table. If your function were able to indicate that a value being passed for update was potentially incrementable, that would be amazing !

Hey @yadakhov I guess I pushed too hard insert-on-duplicate-key, cause apparently I hit the limit of MySQL. 😅
I'm just posting this here mainly for maybe somebody else who could get carried away with fast bulk insert/updates like me.

I tried to insert ~5,800 rows of 32 columns at once and I got the above error. It seems it's due to the place holders limit of MySQL.

Don't know if you want to, but basically I believe it's possible to fix this limiting the length of the inline $data array generating the SQL statement. You could split the array and iterate through it in slots of less than ~65,000 placeholders, or maybe simply raise an Exception for "too much data". It seems in fact Eloquent (v5.5 in my case) somehow fails to raise an appropriate Exception and keeps trying forever.

BTW I fixed the issue limiting my data to ~2,000 rows and it works great, super fast! 🚀

The INSERT ... ON DUPLICATE KEY syntax also works with unique keys (instead of primary keys) but your code doesn't because of the primary key check (checkPrimaryKeyExists).

Would it be possible to somehow grab the number of rows affected? At the moment all the methods return a bool whereas I'd like to know if an INSERT was actually done or ignored. It would be easy to use Laravel's affectingStatement() method, but that would change the API of your code.

Would it be possible to make this breaking change and bump the version number?

Or possibly save the number of affected rows and add a method to grab that value.

I should be able to do the work if you like.

Since we have INSERT ON DUPLICATE KEY we might as well support INSERT IGNORE.

http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update

INSERT IGNORE will simply ignore the rows where the primary key already exists.

Hi @yadakhov!
Thanks for your great work!

I'm trying to store some strings to a database but, in some cases, I need a punctuated version of the same string to be inserted to a table.

An example could be a string like 'cascos para niños' and 'cascos para ninos'. I need both of them to be inserted in a table but, currently, they are considered to be equal preventing them to be inserted twice as different values.

Is there a way I could store them both?

Thanks!

While mass insert i need to generate uuid for each row. as in my database i doesn't have autoincrement id