Upsert query to only change the updated_at column if a value has changed

[bretto36]

I have a large dataset that gets updated often and am trying to improve performance of the code.

I am wanting to switch to using the upsert method which has an immense improvement in performance. The only issue is it ALWAYS updates the updated_at value even when nothing has actually changed.

In MySQL there is the ability to add logic to the on duplicate key clause

INSERT INTO t1 (a,b, updated_at) VALUES (1,2, '2022-01-01 00:00:00')
ON DUPLICATE KEY UPDATE a = values(a), b= values(b), CASE WHEN (a <> values(a) OR b <> values(b)) THEN updated_at = values(updated_at) ELSE updated_at = updated_at END;

This checks whether any of the fields we are attempting to update has actually changed before changing the updated_at. If nothing has changed it shouldn't update it.

Would this be something worthwhile for others?

I've managed to achieve it with a QueryBuilder override but it is not a clean solution at this stage and is very targetted to my code base, but thought it might be worthwhile putting here for discussion

[henzeb]

You can disable timestamps and let your database handle this? https://laravel.com/docs/9.x/eloquent#timestamps

[bretto36]

I want updated at to change IF one of the other values has changed. The whole point of upsert is that you don’t read the data from the DB initially, set the values then call ->save(). So I don’t think disabling time stamps and doing it manually will be anymore performant. By changing the upsert query I am letting the database handle it Maybe I’m missing something though. Could you show a piece of code that would use upsert and update time stamps only when a value has changed please? On 12 Jul 2022, at 10:35 pm, henzeb ***@***.***> wrote:  You can disable timestamps and let your database handle this? https://laravel.com/docs/9.x/eloquent#timestamps — Reply to this email directly, view it on GitHub<#43136 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ABPOCCUPIYGZ74FBOU2SY5LVTVRBZANCNFSM53JDZRNA>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[henzeb]

framework/src/Illuminate/Database/Eloquent/Builder.php

Line 1010 in 904bf56

public function upsert(array $values, $uniqueBy, $update = null)

This is the upsert method, it adds the timestamps when the model says it allows it. It does so by calling:

framework/src/Illuminate/Database/Eloquent/Builder.php

Line 1098 in 904bf56

protected function addTimestampsToUpsertValues(array $values)

and

framework/src/Illuminate/Database/Eloquent/Builder.php

Line 1126 in 904bf56

protected function addUpdatedAtToUpsertColumns(array $update)

[bretto36]

Yes I understand how that all works. I’m not sure you are understanding what I’m saying. I want the upsert to do 2 things for rows that already exist. If any of the values I’m passing are different from what’s in the database then I want it to update the updated at column. If none of the columns have changed then it should skip the updated at column Currently it will overwrite the updated at value every time you call upsert for any existing rows that you pass I want the method to be content aware so it updates updated at like the model would if you called save() On 13 Jul 2022, at 6:28 am, henzeb ***@***.***> wrote:  https://github.com/laravel/framework/blob/904bf5630a95ecf1d6623eb502ba7b751875faf4/src/Illuminate/Database/Eloquent/Builder.php#L1010 This is the upsert method, it adds the timestamps when the model says it allows it. It does so by calling: https://github.com/laravel/framework/blob/904bf5630a95ecf1d6623eb502ba7b751875faf4/src/Illuminate/Database/Eloquent/Builder.php#L1098 and https://github.com/laravel/framework/blob/904bf5630a95ecf1d6623eb502ba7b751875faf4/src/Illuminate/Database/Eloquent/Builder.php#L1126 — Reply to this email directly, view it on GitHub<#43136 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ABPOCCVMBIQSIUN6H3OSY6LVTXIOBANCNFSM53JDZRNA>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[bretto36]

@lechatthecat I ended up creating my own upsertWithoutUpdatedAt method using a custom Eloquent Builder. It's terrible code but it works quite nicely

EloquentQueryBuilder

toBase()->upsertWithoutUpdatedAt( $this->addTimestampsToUpsertValues($values), $uniqueBy, $this->addUpdatedAtToUpsertColumns($update) ); } } QueryBuilder insert($values); } if (!is_array(reset($values))) { $values = [$values]; } else { foreach ($values as $key => $value) { ksort($value); $values[$key] = $value; } } if (is_null($update)) { $update = array_keys(reset($values)); } if ($updatedAtKey = array_search('updated_at', $update)) { $updatedAtValue = $values[array_key_first($values)]['updated_at']; // Replace the item and then move it to the front unset($update[$updatedAtKey]); $parts = []; foreach ($update as $key => $value) { if (is_numeric($key)) { $parts[] = $this->grammar->wrap($value) . ' <> VALUES(' . $this->grammar->wrap($value) . ')'; } } $newUpdatedAtValue = DB::raw('CASE WHEN ' . implode(' OR ', $parts) . ' THEN "' . $updatedAtValue . '" ELSE ' . $this->grammar->wrap('updated_at') . ' END'); // This MUST be at the front otherwise the query performs the updates before it compares the value $update = array_merge(['updated_at' => $newUpdatedAtValue], $update); } $this->applyBeforeQueryCallbacks(); $bindings = $this->cleanBindings(array_merge( Arr::flatten($values, 1), collect($update)->reject(function ($value, $key) { return is_int($key); })->all() )); return $this->connection->affectingStatement( $this->grammar->compileUpsert($this, $values, (array)$uniqueBy, $update), $bindings ); } } Then on your model you just need to add, either through a trait or directly on the model protected function newBaseQueryBuilder() { $connection = $this->getConnection(); return new QueryBuilder( $connection, $connection->getQueryGrammar(), $connection->getPostProcessor() ); } public function newEloquentBuilder($query) { return new EloquentQueryBuilder($query); } Then you can call Invoice::upsertWithoutUpdatedAt($rows, ['unique_field'])

[bretto36]

ah jeez the formatting didn't come through

[lechatthecat]

@bretto36
Thank you too!

[bretto36]

@lechatthecat - i added my code to a gist

https://gist.github.com/bretto36/b9fd23d902d6b52715c0e9c8e6c6c38d

let me know if that works

[nvphuc0927]

@lechatthecat - i added my code to a gist

https://gist.github.com/bretto36/b9fd23d902d6b52715c0e9c8e6c6c38d

let me know if that works

Thanks a lot ! It works straight out of the box .
Do you have any ideas about upsert method returning inserted and updated ids ?

[bretto36]

Hey Thanks for the extra information.

The only issue is that laravel doesn't create timestamps like that out of the box. I could add that to the tables i need to push data into but i also utilize eloquent queries that update tables where i don't want it to trigger the updated at. This is for a calculated field.. Total debt for a debtor so it's not calculated everytime the debtor is queried. It also doesn't honour timezones, so for other people who stumble across this, be wary if your mysql server is a different timezone to your web server.

CREATE TABLE debtors (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
created_at TIMESTAMP NULL DEFAULT NULL,
updated_at TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
)

This is the default way that the tables are generated. There is an undocumented method i have found that allows the ON UPDATE current_timestamp which is ->useCurrentOnUpdate (link below)

So you would have to NOT use
$table->timestamps();
But use your own column definitions
$table->dateTime('updated_at')->useCurrent()->useCurrentOnUpdate();

I may have to use a combination of approaches with certain tables. Thanks again for the guidance and apolgoies on the back and forth

For anyone else looking for info on this here are some reference links

#41857

https://github.com/laravel/framework/blob/68e41fd3691b9aa5548e07c5caf38696c4082513/src/Illuminate/Database/Schema/ColumnDefinition.php

[knobel-dk]

Thanks for some nice thought here, @bretto36. This would be very useful to have as part of the framework.

[lechatthecat]

Another suggestion can be, add this in your model:

public function scopeWithoutTimestamps()
{
    $this->timestamps = false;
    return $this;
}

The call it before upsert:

MyCustomerModel::withoutTimestamps()->upsert($data, ['customerId'], ['email_address']);

Although withoutTimestamps() should exist out of the box in the latest laravel. If your laravel isn't the latest, this can be useful.