How to export Million records using Laravel

#laravel #queue

In this post I diccuses how to export Million(s) records with Laravel using queue job Batching

Before anything make sure to migrate batches table using:

php artisan queue:batches-table
 
php artisan migrate

And put in .env file QUEUE_CONNECTION=database

Now let's get started

Step 1: Controller file

In my controller I have this code:

public function export()
{
    $chunkSize = 10000;
    $usersCount = User::count();
    $numberOfChunks = ceil($usersCount / $chunkSize);

    $folder = now()->toDateString() . '-' . str_replace(':', '-', now()->toTimeString());

    $batches = [
        new CreateUsersExportFile($chunkSize, $folder)
    ];

    if ($usersCount > $chunkSize) {
        $numberOfChunks = $numberOfChunks - 1;
        for ($numberOfChunks; $numberOfChunks > 0; $numberOfChunks--) {
            $batches[] = new AppendMoreUsers($numberOfChunks, $chunkSize, $folder);
        }
    }

    Bus::batch($batches)
        ->name('Export Users')
        ->then(function (Batch $batch) use ($folder) {
            $path = "exports/{$folder}/users.csv";
            // upload file to s3
            $file = storage_path("app/{$folder}/users.csv");
            Storage::disk('s3')->put($path, file_get_contents($file));
            // send email to admin
        })
        ->catch(function (Batch $batch, Throwable $e) {
            // send email to admin or log error
        })
        ->finally(function (Batch $batch) use ($folder) {
            // delete local file
            Storage::disk('local')->deleteDirectory($folder);
        })
        ->dispatch();

    return redirect()->back();
}

Step 2: Creating Job files

We need two main job files, create one called CreateUsersExportFile and the other called AppendMoreUsers

In CreateUsersExportFile:

class CreateUsersExportFile implements ShouldQueue
{
    use Batchable, Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public function __construct(
        public $chunkSize,
        public $folder
    ) {
    }

    public function handle()
    {
        $users = User::query()
            ->take($this->chunkSize)
            ->get();

        Storage::disk('local')->makeDirectory($this->folder);

        (new \Rap2hpoutre\FastExcel\FastExcel($this->usersGenerator($users)))
            ->export(storage_path("app/{$this->folder}/users.csv"), function ($user) {
                return [
                    'id' => $user->id,
                    'name' => $user->id,
                    'email' => $user->id,
                    // ....
                ];
            });
    }
}

private function usersGenerator($users)
{
   foreach ($users as $user) {
      yield $user;
   }
}

Note: I'm using FastExcel pkg for export file.

In CreateUsersExportFile:

class AppendMoreUsers implements ShouldQueue
{
    use Batchable, Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public function __construct(
        public $chunkIndex,
        public $chunkSize,
        public $folder
    ) {
    }

    public function handle()
    {
        $users = User::query()
            ->skip($this->chunkIndex * $this->chunkSize)
            ->take($this->chunkSize)
            ->get()
            ->map(function ($user) {
                return [
                    $user->id,
                    $user->name,
                    $user->email,
                ];
            });

        $file = storage_path("app/{$this->folder}/users.csv");
        $open = fopen($file, 'a+');
        foreach ($users as $user) {
            fputcsv($open, $user);
        }
        fclose($open);
    }
}