Eagerloading before export to avoid N+1 Issues

[philsturgeon]

I am using the following formatter and due to the relationships it's lazyloading these relationships on every record (180k of them).

    public function actions(NovaRequest $request): array
    {
        return [
            QueuedExportAsCsv::make()
                ->withStorageDisk(config('filesystems.default'))
                ->withFormat(function (\Illuminate\Database\Eloquent\Model $model) {
                    return [
                        'ID' => $model->uuid,
                        'Unit Type' => $model->unit_type->value,
                        'Latitude' => $model->coordinates->latitude,
                        'Longitude' => $model->coordinates->longitude,
                        'What3Words' => $model->three_words,
                        'Site' => $model->site->name,
                        'Species' => $model->specie?->name,
                        'Photo' => $model->getPublicPhotoUrl(),
                        'Planted On' => (string) $model->planted_on->format('Y-m-d'),
                        'Created At' => (string) $model->created_at,
                        'Updated At' => (string) $model->updated_at,
                    ];
                })
        ];
    }

I only have access to the $model here which means doing a $load would not help with the next models, so what else can I do?

I tried adding $with on the Nova resource but that's not helped.

class Unit extends Resource
{
    public static $with = [
        'site',
        'specie',
    ];

With or without that $with this is how the trace looks:

Is there any way to get this Action respecting the $with property, or allowing me access to the collection so I can preload/eagerload myself somehow?

[philsturgeon]

This may be the same as laravel/nova-issues#6191 but that got converted to a discussion then never had any further input.

[philsturgeon]

I've actually taken a swing at adding this myself but I'm getitng nowhere fast.

<?php

namespace App\Nova\Actions;

use Closure;
use Illuminate\Support\Facades\Queue;
use Illuminate\Support\Str;
use Laravel\Nova\Actions\ExportAsCsv;
use Laravel\Nova\Actions\Response;
use Laravel\Nova\Fields\ActionFields;
use Laravel\Nova\Http\Requests\ActionRequest;
use Laravel\SerializableClosure\SerializableClosure;
use NovaKit\NovaQueuedExportAsCsv\Jobs\QueuedExportAsCsv as QueuedExportAsCsvJobs;

use function Laravie\SerializesQuery\serialize;

class QueuedExportAsCsv extends ExportAsCsv
{
    /**
     * Storage disk used to store the file.
     *
     * @var string|null
     */
    public $storageDisk;

    /**
     * Determine if file should be deleted after send.
     *
     * @var bool
     */
    public $deleteFileAfterSend = false;

    /**
     * Construct a new action instance.
     *
     * @param  string|null  $name
     * @param  string|null  $storageDisk
     * @return void
     */
    public function __construct($name = null, $storageDisk = null, $eagerLoads = [])
    {
        parent::__construct($name);

        $this->withEagerLoads($eagerLoads);
        $this->withStorageDisk($storageDisk);
    }

    /**
     * Set the storage disk.
     *
     * @param  string|null  $storageDisk
     * @return $this
     */
    public function withEagerLoads($eagerLoads)
    {
        $this->eagerLoads = $eagerLoads;

        return $this;
    }

    /**
     * Set the storage disk.
     *
     * @param  string|null  $storageDisk
     * @return $this
     */
    public function withStorageDisk($storageDisk)
    {
        $this->storageDisk = $storageDisk;

        return $this;
    }

    /**
     * Perform the action request using custom dispatch handler.
     *
     * @return \Laravel\Nova\Actions\Response
     */
    #[\Override]
    protected function dispatchRequestUsing(ActionRequest $request, Response $response, ActionFields $fields)
    {
        $query = $request->toSelectedResourceQuery();

        if ($this->eagerLoads !== []) {
            $query->with($this->eagerLoads);
        }

        $query->when($this->withQueryCallback instanceof Closure, function ($query) use ($fields) {
            return call_user_func($this->withQueryCallback, $query, $fields);
        });

        $filename = $fields->get('filename') ?? sprintf('%s-%d.csv', $this->uriKey(), now()->format('YmdHis'));

        $extension = 'csv';

        if (Str::contains($filename, '.')) {
            [$filename, $extension] = explode('.', $filename);
        }

        $exportFilename = sprintf(
            '%s.%s',
            $filename,
            $fields->get('writerType') ?? $extension
        );

        $job = new QueuedExportAsCsvJobs(
            serialize($query),
            $request->user()->getKey(),
            ! is_null($this->withFormatCallback) ? \serialize(new SerializableClosure($this->withFormatCallback)) : null,
            /* @var array{exportFilename: string, storageDisk: string|null, notify: string} */
            [
                'filename' => $exportFilename,
                'extension' => $extension,
                'storageDisk' => $this->storageDisk,
                'notify' => 'email',
            ],
        );

        $connection = property_exists($this, 'connection') ? $this->connection : null;
        $queue = property_exists($this, 'queue') ? $this->queue : null;

        Queue::connection($connection)->pushOn($queue, $job);

        return $response->successful([
            response()->json(
                static::message(__('The action was executed successfully.'))
            ),
        ]);
    }
}

This replacement is giving exactly the same squery breakdown (N+2) so clearly I'm getting something wrong.

[edgrosvenor]

@philsturgeon would it be detrimental to always have these relationships loaded? If not, you could use with on the model itself. Basically the same thing you tried to do on the resource, but in the model. If there are only a couple of places where always loading the relationships is a problem, you can use withoutRelations() in those queries.

[philsturgeon]

@edgrosvenor I wondered about that but I'm trying to avoid ever using those after countless problems. It's the sort of thing thats hard to test, works on my machine, works for one install, then another install of the same software explodes in unexpected ways. I know I'll end up regretting it if I do that, but... at least I now have Sentry so it can catch problems.

I'd like to try and do this properly if at all possible, is this not something people ever have to face?

[edgrosvenor]

@philsturgeon What about creating a second model that is used just for this purpose? Call it Loaded{OriginalModelName}, point it at the same table, and eager load the relationships there. Only use it for this one piece of the application and leave the rest of your code alone.

[edgrosvenor]

Unfortunately, I don't use Nova. I tried it once on a project and found that it got in my way more than it helped. So I can't really point you to any Nova-specific solutions.

[crynobone]

Eager loading doesn't happen here because of this line:

nova-queued-export-as-csv/src/Jobs/QueuedExportAsCsv.php

Line 82 in 488d3ea

foreach ($query->cursor() as $model) {

Refer to https://laravel.com/docs/11.x/eloquent#cursors for detail.

[edgrosvenor]

Don't use cursor. Use chunk instead. I missed that you were using cursor. Then what you already have in place should fix the problem.

[edgrosvenor]

Basically the problem you are solving with cursor is that you have too many records to read them all into memory and iterate through them. But the same problem can be addressed by using chunk which will load and process the records in chunks while allowing the eager loading to work.

[philsturgeon]

Aha! I wonder, @crynobone could that be an option somehow? Allowing the user to chose cursor or chunk depending on which they prefer for the type of data they're working with? Cursor for LOTS of non related data, chunk if relationships.

If I try and send a PR to QueuedExportAsCsv and could get a pointer or two that would help me get it done quickly. I'm on charity time for this one so need to get it done quick.

[crynobone]

Can you test "nova-kit/nova-queued-export-as-csv": "dev-next as 2.0.0" if that solved your problem.

[philsturgeon]

This is amazing, it worked!

Before 30,000 records took 7 minutes to export, so 180,000 would have been ~42 minutes, a long way over the 15min limit for AQWS Lambda.

Now 180,000 takes 14m52s which... phew, snuck in.

Client has the data they need, but I wonder if there are further improvements that could be made.

Here's the SQL pattern, its doing this on repeat:

I guess thats two extra queries for each chunk of the models, so perhaps that is the best we can do, and I need to look into redis caching or some other sort of in memory hack.