I have an array where keys are the id's of the records I need to edit, and values are, well, the value to change in the database for selected column.
I saw a function that did exactly this in eloquent a while ago but I can't remember what it is and I couldn't find it online too. Anyone know something that does this or should I use the spaghetti code below? I don't think that's very efficient.
public function order_photos(Request $request)
{
$data = $request->input('photo_order');
foreach ($data as $key=>$value){
$image = EstatesPhoto::find($key);
$image->photo_order = $value;
$image->save()
}
return back();
}
@motinska94 Does it work for you?
public function order_photos(Request $request)
{
$data = $request->input('photo_order');
$ids = array_keys($data);
$values = array_values($data);
EstatesPhoto::query()
->whereIn('id', $ids)
->update(['photo_order' => $values]);
return back();
}
@tisuchi Almost does, but ->update(['photo_order' => $values]) part tries to insert the whole array value into an integer column I guess, because all records come out 0 after the code runs.
Any idea how can I fix that? I think I'm gonna need that foreach loop anyway to get the value with $data['id'] unless there's a magic method for that but it doesn't seem likely...
@motinska94 Can you provide a sample what actually $data / $values provide us?
@tisuchi
Here's a sample return case for the array. As I said in the post, keys are the ids of records that needs to update and values are the values to update on photo_order column. So whereIn part works perfectly with ids being in the $ids array, but giving $values as a single value in the update breaks the logic here since every id relates to a single value.
array:8 [
5 => "1"
6 => "2"
7 => "3"
8 => "4"
9 => "5"
10 => "6"
11 => "7"
12 => "8"
]
@motinska94 Ahh.. I missed some of the points.
Probably this would be the right solution (untested) :(
$updateData = [];
foreach ($data as $id => $photo_order) {
$updateData[] = [
'id' => $id,
'photo_order' => $photo_order,
];
}
EstatesPhoto::updateBatch($updateData);
You cannot update multiple rows with different values without doing a case statement. You can use a package for that https://packagist.org/packages/mavinoo/laravel-batch
You can also try upserts, but I'm unsure how they work on primary keys https://laravel.com/docs/9.x/eloquent#upserts
@Sinnbeck Yeah I was looking up that mavinoo package just now. Although, there'll be a maximum of 40 records that'll be updated this way. Would it really be a problem to use foreach? I'm using a shared hosting nothing powerful but still..
I also found something like this ;
#To get a performance improve, you can use a transaction. This way database wont commit after every single update, wich makes the process considerably faster.
DB::beginTransaction();
// your loop and updates;
if( !$user )
{
rollbackTransaction();
} else {
// Else commit the queries
commitTransaction();
}
DB::beginTransaction(); actually worked pretty good!
Please or to participate in this conversation.