Is it possible to use UpdateOrInsert where date column is not older than certain date?

@tisuchi thank you, I will try

@tykus Thanks, I will try that as well (together with the other suggestion)

@tykus @tisuchi Thank you. The syntax that worked is @tykus one, moving the condition to outside the updateOrInsert (tykus used updateOrCreate of Eloquent but that's the same).

When I used tisuchi's syntax it gave some errors. For example, when I used:

DB::table('some_table')->updateOrInsert(['user_id' => 111, 'comment_type' => 4, ['last_update', '>=', now()->subMinutes(5)->toDateTimeString()]],['last_update' => now()]);

It would only work as long as it found a record within the time frame and only needed to update. But as soon as insert was needed (after 5 minutes pass), it would throw this error:

[SQL Server]Invalid column name '0'. SQL: insert into [some_table] ([user_id], [comment_type], [0], [last_update]) values (111, 4, Array, 2023-06-20 08:15:42.664)).

Converting the condition into an array and attempting to insert [0] to it

@tykus I have an update: Your query does not work I just found out - because it first executes the select query that's inside updateOrCreate. Which means, if it finds a match in the updateOrCreate it will immediately execute it, and then execute the above where query.

So I just gave a try to the following syntax and it seem to work:

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

The entire condition is in an array

@Ligonsker well updateOrCreate is not the correct syntax for Query Builder as described previously. Otherwise, the query I provided (using updateOrInsert instead) should work including the date constraint. How did you check the select query that was executed?

@tykus OH!!! You are correct, I changed it to updateOrCreate eventually and forgot that you accidentally wrote updateOrCreate instead of updateOrInsert.

I didn't really check it with toSql() or anything, but I just noticed that the record that is being returned by the updateOrCreate matches the first one and disregarding the date completely.

Then I changed to ['last_update', '=>', now()->subMinutes(5)] and it worked, but again, this post has a mix between updateOrCreate and updateOrInsert

Regarding this syntax: ['last_update', '=>', now()->subMinutes(5)], since it was a trial and error - and there is no mention of it in the docs, do you know whether it really is the correct one? I just don't want to use it now thinking that it works but then in the future I will find out that it only worked because I had a few records to test with