Manually inserting or updating a row with timestamps, without Eloquent

Question

I need to insert or update a row that has created_at and last_update timestamps.

created_at should only be set when the record is created while last_update is updated whenever the record is updated.

I am not using Eloquent in this case, so I can't use methods like updateOrCreate(). Also, using Query Builder's updateOrInsert() won't work here as I must not change the value of created_at.

This is how I ended up doing it:

$row = DB::table('some_table')
 ->where('last_update', '>=', now()->subMinutes(5))
 ->where('user_id', '=', $user_id)
 ->where('comment_type', '=', $comment_type)
 ->first();
if ($row === null) {
 DB::table('some_table')->insert([
 'user_id' => $user_id, 
 'comment_type' => $comment_type, 
 'created_at' => $created_at, 
 'last_update' => $last_update, 
 ]);
} else {
 DB::table('some_table')
 ->where('id', '=', $row->id)
 ->update(['last_update' => now()]); 
}

Is it the right way to do it, or I can improve it to make it more performant?

Edit: Not only for performance, I just understood that my above code is not thread-safe and may cause some bad effects in certain cases. I wonder how can I change it to be thread-safe like Laravel's updateOrCreate()/updateOrInsert().

• Tpojka: Maybe you can test some combination from these answers.

Answer 1

Main question

Is it the right way to do it, or I can improve it to make it more performant?

If the query to find the record to update utilized a primary key instead of timestamp condition then the DB::upsert() method could likely be used. However the condition to get a record within a range of minutes makes that particularly difficult.

Depending on the database engine used a MERGE statement could be used but there is not much support from Eloquent for that currently so it would involve running a raw SQL statement - likely with DB::select(). For example: with mysql: INSERT ... ON DUPLICATE KEY UPDATE Statement , with MSSQL: MERGE, with PostGreSQL: MERGE, etc.

Review

The code is very readable and easy to understand. There is only really one simplification I could suggest.

where conditions can be simplified slightly

From the Laravel documentation for Query Builder:

For convenience, if you want to verify that a column is = to a given value, you may pass the value as the second argument to the where method. Laravel will assume you would like to use the = operator:

$users = DB::table('users')->where('votes', 100)->get();

¹

Thus blocks like this

$row = DB::table('some_table')
 ->where('last_update', '>=', now()->subMinutes(5))
 ->where('user_id', '=', $user_id)
 ->where('comment_type', '=', $comment_type)
 ->first();

Can be updated like this:

$row = DB::table('some_table')
 ->where('last_update', '>=', now()->subMinutes(5))
 ->where('user_id', $user_id)
 ->where('comment_type', $comment_type)
 ->first();