How I can pipeline the incomming results in Laravel so I can process them whilst I read them?-CodePudding

I need to run a query in a console command:


use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

use App\Jobs\SendVaccationEmail;

class ProcessDbResult extends Command
{
  protected $signature = "process:entries";
  protected $description = "Bulk Process of results";

   public function handle() 
   {
     $sql = "
        SELECT DISTINCT 
             user_id 
        from 
         (Select user_id from travels where destination = "Bahamas") as bahamas_vac
         LEFT JOIN (Select user_id from travels where destination <> "Bahamas") as non_bahamas_vac ON bahamas_vac.user_id = non_bahamas_vac.user_id
        WHERE
          non_bahamas_vac.user_id = NULL
 ";

     $results = DB:select($sql);
     foreach($results as $result){
       SendVaccationEmail::dispatch($result);
     }
   }
}

But expect the results to be rather large ~ 100.000 records, therefore in order to save memory consumption, I want somehow the database results to be streamed instead being fetched on one go.

What I actually want to do is:

Meaning I do not want to wait for results to be returned but once I have the first result I want to begin to process it.

Is somehow feasible Using laravel? I'm stuck with laravel 5.8.

I tried to refactor it using

CodePudding user response：

In case that you can wait (eg. running the command via cron at background) or into a screen session.

You can use PDO's fetch and bypass the laravel at database fetching in this command only:

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

use App\Jobs\SendVaccationEmail;

class ProcessDbResult extends Command
{
  protected $signature = "process:entries";
  protected $description = "Bulk Process of results";

   public function handle() 
   {
     $sql = "
        SELECT DISTINCT 
             user_id 
        from 
         (Select user_id from travels where destination = "Bahamas") as bahamas_vac
         LEFT JOIN (Select user_id from travels where destination <> "Bahamas") as non_bahamas_vac ON bahamas_vac.user_id = non_bahamas_vac.user_id
        WHERE
          non_bahamas_vac.user_id = NULL
 ";
     $pdo = DB::getPdo();
     $stmt = $pdo->prepare($sql);
     $stmt->execute();
     while($result =  $stmt->fetch(PDO::FETCH_LAZY)){
        // Perhaps you may need to pass $result->user_id instead
        SendVaccationEmail::dispatch($result);
     }
   }
}

What you see is that I use the underlying PDO instead of laravel. So I can use the fetch and read the result one-by-one instead of waiting forlaravel to return the whole results.

So you avoid using unessesary memory and pipeline the results into the Job. Keep in mind you'll need to manipulate a bit the result.

Sometimes you may need to pass a instance of a Eloquent model to a job then use (Assuming that SendVaccationEmail needs an instance of App\Models\MyModel) :

while($result =  $stmt->fetch(PDO::FETCH_LAZY)){
  $model = \App\Models\MyModel::hydrate($result);
  $model->refresh();
  SendVaccationEmail::dispatch($result);
}

In case you want this command to run via cron I would reccomend using laravel's runInBackground method: https://laravel.com/docs/5.8/scheduling#background-tasks