QUESTION: how to include columns and extra rows from related resources

[Synchro]

Prerequisites

• [x] Able to reproduce the behaviour outside of your code, the problem is isolated to Laravel Excel.
• [x] Checked that your issue isn't already filed.
• [x] Checked if no PR was submitted that fixes this problem.

Versions

• PHP version: 8.0.17
• Laravel version: 8.83
• Nova version: 3.31
• Package version: 1.2.10, 3.1.38

Description

I have an Excel download action attached to a Nova resource. When I run it, I want to include data from related models. At present I receive the selected resources as a collection, i.e. it's already been looked up in the DB. I can get hold of the related resources by accessing them through each instance in the collection, but this is a bad N+1 situation. Then there's the matter of how to add them as extra rows in the output. Say I have a list of users and I select some for the action, but want to include columns from related posts records. So whereas in the default single-resource situation I'd get:

user_id, email
1, user@example.com

I'd like to expand it to:

user_id, email, post_id, post_name
1, user@example.com, 1, story
1, user@example.com, 2, article
2, user2@example.com, 5, story

I have tried an ugly hack returning multiple rows from the map method, which sort-of works, but is very inefficient, and ends up naming each column using a row number instead of a name, like this:

0,1
1, user@example.com, 1, story
2, user2@example.com, 5, story

What I'd really like is to get hold of the query before it's resolved so that I can add some with() clauses to load related models before it resolves, but I've not found how to do that. I have seen the query method, but that starts a whole new query, losing any selection that might have come from Nova.

How can I get at the query before it gets resolved?

[patrickbrouwers]

Best to create a custom action like: https://docs.laravel-excel.com/nova/1.x/exports/customizations.html#full-control you alter the query in there

[Synchro]

I saw that, but I have no idea how the selection comes in, so all I could do is start from scratch. I don't know how Nova delivers selection info to the action – is there an array of resource IDs somewhere, or a stub query builder with whereIn or something? I've seen the options on handle in Nova, but that delivers a collection of models, so it's too late by that point.

[patrickbrouwers]

@Synchro if you extend the download action there's no handle method with a collection of models. The query itself is already built based on the selection.

class YourExportAction extends DownloadExcel {

    public function query()
    {
        return $this->query->with(...)->select(...);
    }

}

[Synchro]

Ah, thank you. I was expecting to see the builder as a method param.

[Synchro]

I've got this working, with one difference. It seems I can't use with to fetch relations because they end up as nested entities in the resulting collection, and thus don't translate to exportable rows, so I have to link to the related records using a regular join and make sure I add all columns with "flat" names (e.g. user_name instead of user.name). Ultimately they all end up as properties on the resource when it gets to map(), and I get multiple rows enumerating the linked records.

[kidereo]

@Synchro Hi, can you please provide a working example of your final action class? I have the same export need but struggling to implement your solution. Many thanks in advance!

[Synchro]

Here you go:

<?php

namespace App\Nova\Actions;

use Illuminate\Bus\Queueable;
use Illuminate\Queue\InteractsWithQueue;
use Maatwebsite\Excel\Excel;
use Maatwebsite\LaravelNovaExcel\Actions\DownloadExcel;

class ExportScansExtended extends DownloadExcel
{
    use InteractsWithQueue;
    use Queueable;

    public $name = 'Download Extended CSV';

    public $withoutConfirmation = true;

    public function __construct()
    {
        $this->withHeadings();
        $this->withWriterType(Excel::CSV);
    }

    public function query()
    {
        return $this->query
            ->join('locations', 'locations.id', '=', 'scans.location_id')
            ->leftJoin('customers', 'locations.customer_id', '=', 'customers.id')
            ->join('users', 'users.id', '=', 'scans.user_id')
            ->select(
                [
                    'scans.uuid',
                    'scans.status',
                    'scans.created_at',
                    'scans.external_id',
                    'users.email as user_email',
                    'customers.name as customer_name',
                    'locations.name as location_name',
                    'locations.uuid as location_uuid',
                ]
            );
    }

    /**
     * @param mixed $row
     */
    public function map($row): array
    {
        return [
            'Scan_uuid'          => $row->uuid,
            'Scan_status'        => $row->status?->value,
            'Scan_externalid'    => $row->external_id,
            'Created_at'         => $row->created_at->format('Y-m-d H:i:s'),
            'User_email'         => $row->user_email,
            'Customer_name'      => $row->customer_name,
            'Location_name'      => $row->location_name,
            'Location_uuid'      => $row->location_uuid,
        ];
    }
}

This action is attached to the Scan resource, so I don't need to mention that in my query because it's already done by Nova, I just add on the extra fields from the related rows.

[kidereo]

@Synchro Great, many thanks! Much appreciated. This is fantastic!

[Synchro]

Happy to help – I just wish I could get some help with this issue in Laravel-Excel.

[kidereo]

I wish I could help but you are way ahead of me here! I am afraid I am just starting out with this Nova Excel package. Usually I would implement FromView and simply pass collections to an html built table.