0

I would like to be able to somehow mass update an eloquent model without n+1 solutions, lets assume I have a million records here.

First Approach: (bad design)

$users = Users::all(); //1 million record
foreach($users as $user){
   $user->age = rand(5, 15);
   $user->save();
}

Second Approach: (better a little, still is a n+1 solution)

DB::beginTransaction();
$users = Users::all(); //1 million record
foreach($users as $user){
    DB::table('users')
       ->where('id', '=', $user->id)
       ->update([
           'age' => rand(5, 15),
    ]);
}
DB::commit();

I personally wish there was a simplified way to do the following:

$users = Users::all(); //1 million record
foreach($users as $user){
   $user->age = rand(5, 15);
}
$users->saveAll();

What do you recommend is a better solution that wont end up with 1 million queries? My current solution would be to chunk the records into smaller pieces and throw them in a QUEUE where it will process smaller chunks but, eh.. not sure if that is the proper way to do it either!

Khalil Ghanem
  • 166
  • 13
  • Perhaps using raw SQL and use MySQL's `rand()` - https://stackoverflow.com/questions/6550155/mysql-get-a-random-value-between-two-values – Nigel Ren Jan 25 '22 at 07:20
  • Does this answer your question? [MySQL get a random value between two values](https://stackoverflow.com/questions/6550155/mysql-get-a-random-value-between-two-values) – N69S Jan 25 '22 at 07:33
  • The issue isn't with the mysql rand or php rand, the issue is with laravel lacking proper functionality to perform a mass update, but you are right @NigelRen I'm starting to think that the only way to properly accomplish this is by using raw SQL – Khalil Ghanem Jan 25 '22 at 07:39
  • Laravel is not lacking the functionality to save in bulk, It is not possible in MySQL to save multiple models with different values in a single query. If Laravel would provide that functionality, it will do the same thing as you in the background. A raw query would be the correct solution. – Gert B. Jan 25 '22 at 08:06

1 Answers1

0
DB::raw('update users set age = FLOOR(1 + rand() * 5) where id !='.$user->id);
Zakirsoft
  • 1
  • 2