Summer Sale! All accounts are 50% off this week.
Hello,
Is it possible to use UpdateOrInsert (https://laravel.com/docs/10.x/queries#update-or-insert), but also where a date column is not older than current time minus defined number of minutes?
For example, the following table:
id | user_id | comment_type | comment | last_update
---|---------|--------------|-----------|------------------------
1 | 11 | 4 | "Hello" | 2023-06-18 15:55:10
2 | 11 | 4 | "World" | 2023-06-18 16:05:44
I want to update the last_update value to the current value (where the user is user_id and the comment type is some comment_type), only if the last_update was made no longer than 5 minutes ago. If not, create a new row.
DB::table('some_table')
->updateOrInsert(
['user_id' => 11, 'comment_type' => 4], // but also where `last_update` is not older than 5 minutes
['last_update' => Carbon::now()] // then update `last_update` to current time
);
Ty
@ligonsker Have you tried this?
// Get the date 5 minutes ago
$threshold = Carbon::now()->subMinutes(5);
DB::table('some_table')
->updateOrInsert(
[
'user_id' => 11,
'comment_type' => 4,
'last_update' => ['>=', $threshold],
],
[
'last_update' => Carbon::now(),
]
);
@tisuchi thank you, I will try
You need the date constraint for the read query only, so the following will work:
DB::table('some_table')
->whereDate('last_update', '>=', now()->subMinutes(5))
->updateOrCreate([
'user_id' => 11, 'comment_type' => 4
], [
'last_update' => now()
]);
@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
Please or to participate in this conversation.