How to export 100,000 rows of data? - maatwebsite/excel:^3.1

Question

I have this code and it's working but only 22,000 is the maximum rows that can be dowloaded. How to optimize this to download 50,000 rows to 100,000 rows of data?

<?php

namespace App\Exports;

use App\Models\Pvp;
use Illuminate\Contracts\Queue\ShouldQueue;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Excel;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\FromCollection;


class PvpExport implements FromQuery, WithHeadings, ShouldAutoSize, WithMapping, WithStyles, WithChunkReading

{
    use Exportable;

    private string $writerType = Excel::XLSX;

    protected $startDate;
    protected $endDate;
    protected $mode;

    public function __construct($startDate, $endDate, $mode)
    {
        $this->startDate = $startDate;
        $this->endDate = $endDate;
        $this->mode = $mode;
    }


    public function query()
    {
        $query = Pvp::query();

        if ($this->mode !== 'all') {
            $query->where('type', $this->mode);
        }

        if ($this->startDate) {
            $query->where('created_at', '>=', $this->startDate);
        }

        if ($this->endDate) {
            $query->where('created_at', '<=', $this->endDate);

        }
        return $query;
    }





    public function map($row): array
    {
        return [
            $row['id'],
            $row['match_id'],
            $row['player_id_1'],
            $row['player_id_2'],
            $row['winner_id'],
            $row['room_id'],
            $row['type'],
            $row['rake']
        ];
    }


    public function headings(): array
    {
        return [
            'ID',
            'Match ID',
            'Player ID 1',
            'Player ID 2',
            'Winner ID',
            'Room ID',
            'Type',
            'Rake'
        ];
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }

    public function chunkSize(): int
    {
        return 5000;
    }
}

I tried to used chuckSize and map to optimize the download and also change xlsx to csv but still the maximum download is 22,000 before it reached the memory limit

Answer 1

I was able to resolved it by adding ini_set('memory_limit', '1G'); in the export file construct.

I add it in the export file so that it will not affect the whole application and only if the downloaded button is clicked.

this is the whole code.

public function __construct($sortBy, $orderBy, )
    {
        $this->sortBy = $sortBy ?? 'id';
        $this->orderBy = $orderBy ?? 'desc';

        ini_set('memory_limit', '1G');

        // dd($sortBy, $orderBy);

    }

If anyone has better idea then you can share it here.

Thanks