Export Query to compressed CSV file in laravel
Tags: laravel, csv
Published: 2022-02-28 15:23:02

Exporting your database records or a calculated array of data to a CSV file is a very common use case. Here in this article, I'm trying to give you a utility class for achieving this.

## What concerns this article covers:

- Get data from both chunked queries and arrays
- Reduce the size of the output file by compressing it
- Be ready for concurrent multiple requests
- Delete old files in the folder

Now, let's get our hands dirty.

## A utility class for exporting
I going to define a new `Utilities` namespace in my app folder and add a class of `ExportToCSV` into it. I expect this class to do two main jobs, One is exporting CSV from a query, and two is from an array of data. I also mentioned that it chunks the query to make it optimized in favor of our memory usage. You can learn more about chunking from [this link](https://laravel.com/docs/9.x/queries#chunking-results).

So, our `ExportToCSV` class is something like below with two public functions:
```php
<?php

namespace App\Utilities;

use Illuminate\Contracts\Database\Query\Builder;

class ExportToCSV
{
    public function exportFromQuery(Builder $query)
    {
        //
    }

    public function exportFromArrayData(array $data)
    {
        //
    }
}
```

Okay, first things first, let's create a method for query export.

### Exporting to CSV file
After creating `exportFromQuery()` with one `$query` parameter, let's constrain it to be an instance of `Illuminate\Contracts\Database\Query\Builder`, this helps us to make sure that it has the `chunk()` method in it.
So, we need to open a CSV file and start iterating over our data for filling up the file. But where do we open it? Storage is needed and I will add a property for it in our class.

Also, we must specify the header for the CSV file. So, there is another property for our headers.

```php
use Illuminate\Contracts\Filesystem\Filesystem;

class ExportToCSV
{
    protected Filesystem $storage;
    protected mixed $file;
    public array $headers;
    // ...

    public function exportFromQuery(Builder $query, array $headers)
    {
        $this->query = $query;
        $this->headers = $headers;

        $this->storage = $this->storage ?? app('filesystem')->disk(config('filesystems.default'));

        // Write to csv file in append mode(a+)
        $this->file = fopen($this->storage->path($this->fileName), 'a+');
        fputcsv($this->file, $this->header);

        $this->query->chunk($this->chunkSize, function($iterableData){
            foreach ($iterableData as $row) {
                $toArray = json_decode(json_encode($row), true);
                $res     = [];
                foreach ($this->headers as $key => $value) {
                    $res[$key] = data_get($toArray, $key);
                }
                $res = array_filter($res, fn ($item) => !is_array($item));
    
                fputcsv($this->file, $res);
            }
        });

        fclose($this->file);

        return $this->compressFilesAndGetDownloadStream();
    }
}
```

The same strategy is applicable for exporting from an array of data, we need to just change the query with a `foreach` over the requested array.

```php
class ExportToCSV
{
    protected Filesystem $storage;
    protected mixed $file;
    public array $headers;
    // ...

    public function exportFromArrayData(?array $data = [], array $headers)
    {
        $this->data = $data;
        $this->headers = $headers;

        $this->storage = $this->storage ?? app('filesystem')->disk('default');

        // Write to csv file in append mode(a+)
        $this->file = fopen($this->storage->path($this->fileName), 'a+');
        fputcsv($this->file, $this->header);

        foreach ($this->data as $row) {
            $toArray = json_decode(json_encode($row), true);
            $res     = [];
            foreach ($this->headers as $key => $value) {
                $res[$key] = data_get($toArray, $key);
            }
            $res = array_filter($res, fn ($item) => !is_array($item));
    
            fputcsv($this->file, $res);
        }

        fclose($this->file);

        return $this->compressFilesAndGetDownloadStream();
    }
}
```
### Compressing the output file(s)
Well, by now we have our CSV file with all of the records in it, next step is to compress it which refers to the `compressFilesAndGetDownloadStream()` method at the end of the above codes. For compressing, we can use the PHP `ZipArchive` class which you can learn about it from [here](https://www.php.net/manual/en/class.ziparchive.php).

Below we add `compressFilesAndGetDownloadStream` method to our class.

```php
use Symfony\Component\HttpFoundation\StreamedResponse;
use ZipArchive;
// ...

class ExportToCSV
{
    // ...

    public function compressFilesAndGetDownloadStream()
    {
        $zip = new ZipArchive();
        $zip->open($this->storage->path($this->zipFileName), ZipArchive::CREATE | ZipArchive::OVERWRITE);
        foreach ($this->storage->allFiles() as $filePath) {
            if (strpos('.zip', $filePath)) continue;
            $zip->addFile($this->storage->path($filePath), $filePath);
        }
        $zip->close();

        return $this->storage->download($this->zipFileName);
    }
}
```
### Being ready for multiple requests
Until now, we have covered our two main concerns of exporting from query or data array and compressing the result. It's time to take care of multiple concurrent requests for exporting data and deleting old generated files.

When it comes to speaking of multiple requests, there is a need for separating generated files. We can separate files of each request to a folder with a random name, I call it *process ID* and after compressing that folder, we can delete it. As we are speaking of concurrency, we should be careful about picking different names at the exactly same time. Using  `uuid()` helps us to prevent duplication in naming files/folders. Refer to [here](https://en.wikipedia.org/wiki/Universally_unique_identifier) and [here](https://laravel.com/docs/helpers#method-str-uuid)

So, let's add a `processId` property to our class and initialize it in the constructor.
```php
use Illuminate\Support\Str;

class ExportToCSV
{
    protected string $processId;

    public function compressFilesAndGetDownloadStream()
    {
        $this->processId = Str::uuid()->toString();
    }
}
```

And create a directory for this process before opening the CSV file.
```php
// ...

$this->storage->makeDirectory($this->processId);
// Write to csv file in append mode(a+)
$this->file = fopen($this->storage->path("$this->processId/$this->fileName"), 'a+');

// ...
```
We also need to make sure that only files in this process's folder are going to be compressed. So, let's update compressing section by adding folder name and removing folder name from the file name by `substr($filePath, strlen($this->processId) + 1)`.

```php
$zip = new ZipArchive();
$zip->open($this->storage->path($this->zipFileName), ZipArchive::CREATE | ZipArchive::OVERWRITE);
foreach ($this->storage->allFiles($this->processId) as $filePath) {
    if (strpos('.zip', $filePath)) continue;
    $zip->addFile($this->storage->path($filePath), substr($filePath, strlen($this->processId) + 1));
}
$zip->close();
```

After compressing, we need to delete the folder by `$this->storage->deleteDirectory($this->processId);`

### Deleting old files
Well, now it's time to delete old files in our target storage. For this purpose, I added a method that calculates the last modified time of each file and compares it to now. After that, if the modified time is greater than a specified day, it will delete it from the storage. 

The `oldFilesDueDays` property can be configured according to the customer's need and we also have an option here for skipping deletion if the `oldFilesDueDays` was set to `-1`.

```php
protected function deleteOldZipFiles()
{
    // Delete old files that longer than x day
    // For not deleting old files, -1 can be set to $oldFilesDueDays
    if ($this->oldFilesDueDays === -1) return;

    foreach ($this->storage->allFiles() as $old_file) {
        $fileCreatedAtDaysDiff = Carbon::now()->diffInDays(Carbon::parse($this->storage->lastModified($old_file)));
        if ($fileCreatedAtDaysDiff > abs($this->oldFilesDueDays)) {
            $this->storage->delete($old_file);
        }
    }
}
```
The method of `deleteOldZipFiles()` can be called before or after file compression.

## Wrap up
We have covered exporting from a chunked query or a data array into a CSV file. Plus compressing it and deleting the process folder and old files.

The final code of this attempt is as below. I've glued different parts and refactored it to be more readable and customizable.

{% embed https://gist.github.com/babak271/fa177920b0327bf4bbc34488232c71e6.js %}