Lazy Collections in Laravel Excel

In Laravel 6 a new type of collections has been added: Lazy Collections. They are a great if you have to work with very large data sets (thousands or millions of rows) without running into memory limits.

I recently have been tasked to refactor an Excel export in a project at work. The problem was, that the export couldn't be created any more as the data set was too big for Laravel to handle. The database query returned around 300.000 results! The app would run into a timeout or memory limit all the time.

A naive approach would be to increase timeout or memory limit and hope, that the next time the problem occurs, another person will deal with the problem.
But that's not how I work. I don't like band aid fixes.
I like concrete, long-term solutions.

The Laravel Excel-package is already quite flexible. It does a great job of reducing the load for the database by using chunks when using the FromQuery-concern. However, our export still struggled to deal with the big dataset.

I debated with my colleagues, if we should rewrite the feature entirely: Push the export to the queue and send a notification to the user, when the export finishes. However, the feature is a tiny little thing in this app. It didn't make sense to us to add so much overhead for a tiny export.

Later that day, I had a little "eureka"-moment, as I remembered LazyCollections were a thing in Laravel 6.

I rewrote the export: Instead of FromQuery it now uses the FromCollection-concern. The only change I had to make in the collection()-method, is to replace the get()-method at the end of my query builder-chain to cursor().

Below is a simplified version of our export. The Request-object is passed through the constructor, so that we can make adjustments to the query, based on what a user has selected in the UI.

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Illuminate\Http\Request;

class UsersExport implements FromCollection
{
    use Exportable;

    protected Request $request;

    public function __construct(Request $request)
    {
        $this->request = $request;
    }

    public function collection()
    {
        return User::query()
            ->when($this->request->get('include_subscribed'), function ($q) {
                return $q->where('is_subscribed', true);
            })
            ->cursor(); // ← This is the important bit
    }
}

I'm sure you ran into memory issues your self in your projects. You increased the memory limit and hoped that the problem was fixed (I've done this myself countless times).

If it was in a Laravel project, I hope, I could animate you to revisit that code and rewrite it to use LazyCollections.

I had so much fun fixing this problem, that I did a little benchmark: Our app can now export millions of rows without running into a memory limit. Pretty cool! 😎