updateOrInsert without updating the "created_at" column

@Nakov Yes, but since I have to insert it manually, something doesn't work right. Because if I do the following:

DB::table('some_table')
    ->where('last_update', '>=', now()->subMinutes(5))
    ->updateOrCreate([
        'user_id' => 11,
        'comment_type' => 4
    ], [
        'created_at' => now(), // will always update it to now() (obviously 😅)
        'last_update' => now()
    ]);

It will always update created_at. but if I then add a check in the query before like:

DB::table('some_table')
    ->whereNotNull('created_at')
    ->where('last_update', '>=', now()->subMinutes(5))

it will not work as intended, it will always created a new row because the next time created_at is not null, so I just couldn't find the right place to place it

@tykus thanks, I ended up doing the following then (Because I am not using Eloquent in this case):

$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' => $user_id,     
        'created_at' => $user_id,     
        'last_update' => $user_id,     
    ]);
} else {
     DB::table('some_table')
          ->where('id', '=', $row->id)
          ->update(['last_update' => now()]);     
}

Is it good, or I can improve that performance wise maybe?

@Ligonsker i would update the record, then if it does not exist, create it

note that your code is not atomic so you could have strange effects if two users are doing this at the same time

Do you have eny unique index to prevent duplicate records from being created?

@Snapey thank you, I know that the $row should be unique because the select query combination:

    ->where('last_update', '>=', now()->subMinutes(5))
    ->where('user_id', '=', $user_id)
    ->where('comment_type', '=', $comment_type)

should only return one record, but perhaps you meant another part that I misunderstood?

Also, by first updating then inserting, you mean that I try to update, then check if I got 0 or 1 from the update query and then insert:

$update_result = DB::table('some_table')
    ->where('last_update', '>=', now()->subMinutes(5))
    ->where('user_id', '=', $user_id)
    ->where('comment_type', '=', $comment_type)
    ->update(['last_update' => now()]);    

if (!$update_result) {
     DB::table('some_table')->insert([
        'user_id' => $user_id,        
        'comment_type' => $user_id,     
        'created_at' => $user_id,     
        'last_update' => $user_id,     
    ]);
}

@Ligonsker

1st point, two threads could simultaneously think the row does not exist, and both create it.

2nd point yes, something like that. trying to exit early so that most times only one query executes

Have you considered upserts? https://laravel.com/docs/10.x/queries#upserts (but note warning about unique keys)

@Snapey got it, I need to create a more thread-safe version of that. But, I am not sure that even my second version (the update then insert) is thread safe. Is there any way to make it thread safe like Laravel's updateOrCreate or updateOrInsert? How does Laravel achieve that? Is it using transactions behind the scenes?

About upserts, isn't it the same as updateOrInsert for multiple records, thus leaving me with the initial problem where I need to create the created_at column at first?

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

Because I still need to create the created_at column if it's the first time. But not sure where to place it here

@Ligonsker unfortunately, laravels methods are not thread safe either.

upsert works by trying to create a new record and if that fails because of a conflict with the unique-ness of the record then it does an update.

You could do the same but with discrete statements

try
	DB create record
catch database error
   update existing record

if two threads try to update the same record at the same second then the result will be the same since they will both write identical data to the same record (in this case)

(still relies on a unique database index)

@Snapey Oh I just got it, and in this case I do not have unique indexing. I was wrong earlier. these columns:

    ->where('user_id', '=', $user_id)
    ->where('comment_type', '=', $comment_type)

aren't unique, and it's only "unique" if the last_update is within a timeframe, but not DB unique-ness as you meant, my mistake. Is there anything I can do (modify the db to be unique somehow, or something else) so that it can make it safer?

By the way, was I correct about what I said earlier that upsert won't work in my case anyway?

And also, I thought that, I could use Eloquent's updateOrCreate now, and leave the query with last_update as it is above. Since I need to update the last_update column, not updated_at (I have a few columns that are updated based on certain events, and it's not the same as the row's updated_at)