Let's say i have a collection of user model,
$users = [
[
'id' => 1,
'email' => '[email protected]',
'total_reward_received' => 1000
],
[
'id' => 2,
'email' => '[email protected]',
'total_reward_received' => 1500
]
[
'id' => 3,
'email' => '[email protected]',
'total_reward_received' => 2500
]
]
Here, I want to update the value of total_reward_recieved by incrementing it's value with the new reward the user got. Let's say I have an array of new reward values,
[
[
'user_id' => 1,
'new_reward_amount' => 500
]
[
'user_id' => 3,
'new_reward_amount' => 200
]
]
I want the total_reward_received value of user one to be updated to 1500 and that of user three to be 2700. Like this,
[
[
'id' => 1,
'email' => '[email protected]',
'total_reward_received' => 1500
],
[
'id' => 3,
'email' => '[email protected]',
'total_reward_received' => 2700
]
]
Currently this is how I am doing it,
foreach ($users as $key => $user) {
$new_reward = calculateNewRewardAmount();
User::where('id', $user->id)->increment('total_reward_received', $new_reward);
}
Since I will have thousands of users to deal with, I think this way of creating a User instance within the loop is not the right way to go about. If I can prepare an array with the user_id and new_reward_amount within the loop, is there anyway I can perform the increment option with a single query.
What I have tried
I have tried using https://github.com/mavinoo/laravelBatch library for a batch update. But since I want the column to be incremented instead of updating with a new value I can't see a way to do it. I have used DB::raw() to increment the value, but it doesn't seems to be working, If I try with a static value (a variable) instead, it seems to be working. Have a look,
$newRewardsArray = [];
foreach ($users as $key => $user) {
$newRewardsArray[$key] = [
'id' => $user->id,
'total_reward_received' => $newReward + $existingReward
];
}
Batch::update(new User, $newRewardsArray, 'id');
This one seems to be working. where i have to call the user model to get the existing reward.
$newRewardsArray = [];
foreach ($users as $key => $user) {
$newRewardsArray[$key] = [
'id' => $user->id,
'total_reward_received' => DB::raw('total_reward_received+' . $newReward)
];
}
Batch::update(new User, $newRewardsArray, 'id');
But this one not. (But would've been great ;-)) Here I am trying to increment the value by new_reward_amount using DB::raw(). But the value of the column is getting updated as '0'.
Do you guys have any idea on how to achieve this. Even without using this library. (I just included that code example from the library to explain how I tried it.)