Exporting Eloquent relations without N+1 in a streaming export

Streaming export with exportModel() keeps memory flat — that is the whole point of the cursor() under the hood. But as soon as the export needs data from a related model — the customer’s name on an order, the category name on a product — everything suddenly slows down. For 10,000 rows the export sends 10,001 queries to the database instead of a couple.

This is classic N+1, disguised by streaming writes. Let’s see why it appears precisely in a streaming export, why the familiar with() does not help here, and how to export relations without losing either speed or low memory usage. Everything below uses avadim/fast-excel-laravel and was verified on version 4.2.0.

Why cursor() does not get along with relations#

exportModel(Order::class) walks the model with Order::cursor() internally. An Eloquent cursor keeps exactly one model in memory at a time — that is why memory does not grow with the number of rows.

The price is that cursor() does not support eager loading of relations. The logic is simple: eager loading fetches relations for the whole result set in one query, while a cursor sees records one at a time and has no “whole result set”. As a result, every access to a relation inside the loop runs a separate query:

// Trap #1: N+1 disguised as a streaming export
$excel->sheet()
    ->mapping(function ($order) {
        return [
            'id'       => $order->id,
            'customer' => $order->customer->name, // ← a separate SELECT for every row
            'total'    => $order->total,
        ];
    })
    ->exportModel(Order::class);

Adding Order::with('customer') will not save you either: exportModel() takes a class name, not a ready query, and internally still uses a plain cursor(). And with('customer')->cursor() does not perform eager loading in Eloquent anyway — the relation is loaded lazily, one query per row. N+1 stays.

Why you can’t just use get()#

The obvious workaround is to load everything at once with eager loading:

// Trap #2: no N+1, but the whole result set is in memory
$orders = Order::with('customer')->get();

$excel->sheet()
    ->mapping(function ($order) {
        return [
            'id'       => $order->id,
            'customer' => $order->customer->name,
            'total'    => $order->total,
        ];
    })
    ->writeData($orders);

Here with('customer') honestly removes N+1: the relations are fetched with one extra query. But get() materializes the whole result set at once — every order and every customer as objects in memory. We are back to exactly the problem we adopted a streaming library to escape: on large volumes it will hit memory_limit.

By the way, mapping() is not decorative here. Without it, the whole model goes into the row via toArray(), where the loaded relation becomes a nested array — and writing stops with the error Value for cell … must be scalar or callable.

So the two traps pull in opposite directions: cursor() saves memory but gives N+1; get() removes N+1 but eats memory. We need a way to get both properties at once.

The solution: writeData() over lazy()#

There is such a way — the lazy() method. It reads the result set in chunks (1,000 records by default) and returns them as a lazy collection, handing out one model at a time. The key difference from cursor(): lazy() works in batches, which means eager loading works inside each chunk — with() fetches the relations with one query per chunk.

We get both: only the current chunk lives in memory at any moment, and the number of queries is not “one per row” but “a couple per chunk”. We feed this to writeData() through a generator:

$excel = \Excel::create('Orders');

$excel->sheet()
    // Explicit headings in column order — their values go into the first row
    ->withHeadings(['id' => 'Order #', 'customer' => 'Customer', 'email' => 'Email', 'total' => 'Total'])
    ->mapping(function ($order) {
        return [
            'id'       => $order->id,
            'customer' => $order->customer->name, // the relation is already loaded — no extra queries
            'email'    => $order->customer->email,
            'total'    => $order->total,
        ];
    })
    ->writeData(function () {
        // with() + lazy(): eager loading inside a chunk, memory bounded by one chunk
        foreach (Order::with('customer')->lazy() as $order) {
            yield $order;
        }
    });

$excel->store('local', 'exports/orders.xlsx'); // the local disk; Storage creates missing directories itself

Everything fits together here: lazy() reads the database in batches, with('customer') removes N+1 inside each batch, the generator hands rows to writeData() one at a time, and the library writes them to the file as a stream. Memory stays flat at any volume.

A small note on headings: set them explicitly, keyed by the mapping keys, as in the example. Called without arguments, withHeadings() takes the column names from the model’s own attributes (id, customer_id, total, …) — before the mapping runs. The row returned by mapping() is laid out by those keys, so some columns stay empty, and fields that are not among the attributes (email here) do not make it into the file at all.

Checking with numbers#

Let’s run every variant on SQLite with 10,000 and 100,000 orders, each with a customer. We count database queries, the growth of peak memory, and the export time (Laravel 13, fast-excel-laravel 4.2.0).

The picture is exactly as described above. The cursor is frugal with memory but pays one query per row. get() gets by with two queries, but memory grows with the result set: ten times the data means ten times the memory. lazy() keeps both bounded: two queries per chunk (plus one final query that returns an empty batch) and memory for one chunk — it did not change when the data grew tenfold.

The times here are on a local SQLite, where a query has no network latency, and even so N+1 is almost three times slower. With the database on another server, each of the 10,000 queries also adds a network round trip, and the gap only widens.

“Many” relations and aggregates#

If an order has several lines (hasMany) and the report needs a single cell, collapse the relation in the mapping instead of exporting it row by row:

->mapping(function ($order) {
    return [
        'id'       => $order->id,
        'customer' => $order->customer->name,
        'items'    => $order->items->count(),          // number of lines
        'total'    => $order->items->sum('price'),      // sum over the lines
    ];
})
->writeData(function () {
    foreach (Order::with(['customer', 'items'])->lazy() as $order) {
        yield $order;
    }
});

Such an export sends three queries per chunk: the orders, their customers, and their lines.

If all you need from a relation is the count, it is cheaper to compute it in the database with withCount() than to load the records themselves:

Order::with('customer')->withCount('items')->lazy()
// inside the mapping: 'items' => $order->items_count

Honest boundaries#

• exportModel() does not do eager loading — by design. It takes a class name and calls cursor() on it itself; you cannot pass a ready query such as Order::with('customer') — you will get an error. For exports with relations, use writeData() with your own lazy() query, and keep exportModel() for simple “model as is” exports without relations.
• The query is your responsibility. The library does not guess which relations you need. Keep the with() list minimal: every extra relation means memory for objects in the current chunk.
• Deep nesting costs money. with('customer.company.country') will noticeably weigh down every chunk on large volumes. If you need a single field from the chain, it is sometimes cheaper to add it to the base query with join/select than to pull a chain of models.
• The chunk size is configurable. By default lazy() reads 1,000 records at a time; with heavy relations, lower it (lazy(200)) to reduce the peak memory per chunk — at the cost of more queries.

Summary#

• Streaming exportModel() saves memory, but accessing relations in the mapping gives N+1: cursor() does not support eager loading.
• get() with relations removes N+1 but materializes the whole result set in memory — not an option at volume.
• The sweet spot is writeData() with a generator over Model::with(...)->lazy(): eager loading inside a chunk removes N+1, and chunking keeps memory bounded.
• Collapse “many” relations and counters in the mapping or count them in the database (withCount), and keep the with() list minimal.

composer require avadim/fast-excel-laravel

The repository is github.com/aVadim483/fast-excel-laravel.