Lens CSV export empty when query ID is not the same as resource model ID

[moisessepulveda]

• Laravel Version: 9.11
• Nova Version: 4.15.1
• PHP Version: 8.1
• Database Driver & Version: Postgres 14
• Operating System and Version: MAC OS 12.4
• Browser type and version: Safari 15.5
• Reproduction Repository: https://github.com/moisessepulveda/nova-lens-bug-report

Description:

When you we have a lens that contains a group by and you try to export data as CSV, it doesn't work when you select individual record. It only works when "Select all" is selected.

This issue only happens when primary key obtained from query does not belongs to the same model as resource.

Detailed steps to reproduce the issue on a fresh Nova installation:

First keep in mind and create the next two models:

Employee:

column	type
id	integer
name	string

Payment:

column	type
id	integer
amount	integer
employee_id	integer

• Create Resource for every model.
• Create a lens that contains a group by. The primary key obtained from the query should come from another table different than resource one.
• We will create a Payment Lens that will obtain how many payments every employee has. Something like this:

employee id	name	payments
1	jack sparrow	5
2	jane doe	10
3	jhon doe	20

Consider a query like this:

/**
     * Get the query builder / paginator for the lens.
     *
     * @param  \Laravel\Nova\Http\Requests\LensRequest  $request
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @return mixed
     */
    public static function query(LensRequest $request, $query)
    {
        return $request->withOrdering($request->withFilters(
            $query->select('employees.id', 'employees.name', DB::raw('count(payments.id) as count'))
            ->join('employees', 'payments.employee_id', '=', 'employees.id')
            ->groupBy('employees.id', 'employees.name')
        ));
    }

fields:

public function fields(NovaRequest $request)
    {
        return [
            ID::make(__('ID'), 'id')->sortable(),
            Text::make('Name', 'name'),
            Number::make('Count', 'count')
        ];
    }

actions:

public function actions(NovaRequest $request)
    {
        return [
            ExportAsCsv::make(__('Exportar a CSV'))->withFormat(function ($model) {
                return [
                    'id' => $model->id,
                    'Name' => $model->name,
                    'Count' => $model->count,
                ];
            })
        ];
    }

• IMPORTANT Add this LENS to Payment Resource.
• Use exportAsCSV Facade exporting the related data.
• Open Nova admin panel, then go to payment resource and open the lens just created.
• Try to export individual record using Export as CSV action.
• You will note that CSV actually is downloaded, but it completely empty.
• If you try "Select all" and then try to export is works like a charm.

[crynobone]

At the moment there's no reliable way to identify resourceIds when Lens query doesn't use the resource default ID https://github.com/laravel/nova/blob/96d4481cfd5bd4406212487e17838fd6bc261f6e/src/Http/Requests/ActionRequest.php#L118-L121 and this wouldn't be an easy fix.

If you can accept to export CSV for "select all" until we can come up with a solution then you can use the following:

    public function lenses(Request $request): array
    {
        return [
            PaymentsPerEmployee::make()->canSee(function ($request) {
                return $request->allResourcesSelected();
            }),
        ];
    }

[moisessepulveda]

Thanks for the answer, I hope you can find a solution soon. I'll stick with the workaround proposal for the moment

[matthewjumpsoffbuildings]

I dont understand why this isnt an easy fix. I have tried something that should work in theory, but still isnt.

I added an ID field by simply adding an additional select in the lens query with an as clause, eg

$query->select('employees.id', 'employees.name', DB::raw('count(payments.id) as count'), 'employees.id as id');

And of course in the lenses fields() definition added an ID::make() to go with it.

Now, when I select individual resources and attempt to run the export csv action, i see in the request payload the resources param contains a string separated list of valid and correct ids. i assumed that this would be sufficient for Nova to apply to the lens query but still, no dice. The export is empty.

So i manually checked for the resources param in the lens query, and updated the query if it was present, like so:

$selectedIds = $request->input('resources');
if ($selectedIds && $selectedIds != 'all') {
	$selectedIds = explode(',', $selectedIds);
	$query->whereIn('id', $selectedIds);
}

Still no dice, the export is still an empty file.

Now i am at a loss. If I specify a field in the query to be the id, why on earth is this not working? The 'there's no reliable way to identify resourceIds when Lens query doesn't use the resource default ID' thing doesnt seem to make sense?

[matthewjumpsoffbuildings]

Turns out if I add the following to the action definition in my lens

ExportAsCsv::make()
->withQuery(function($query, $fields) use ($request) {
	if($request->input('resources') != 'all') {
		array_pop($query->getQuery()->wheres);
		array_pop($query->getQuery()->orders);
	}
	return $query;
})

it works perfectly with the above code where i use an other_table.id as id select and an ID::make() field, then look for the resources request param myself in the lenses query function...

The issue is that Novas ActionRequest->toSelectedResourceQuery() function is adding incorrect table_one.id in () and order by table_one.id clauses, even though my lens query was specifying its own id field and order clause

I feel like if an id column is present already, as part of the lens queries select, the ActionRequest->toSelectedResourceQuery() method should notice that and us that field for the whereIn id in (1, 2, 3) clause, and also not add a pointless order clause

[crynobone]

The issue is that Novas ActionRequest->toSelectedResourceQuery() function is adding incorrect table_one.id in () and order by table_one.id clauses, even though my lens query was specifying its own id field and order clause

The issue here is that the current behavior is Lens belongs to Resource (e.g: resources/users/lens/active-users) and the original poster wants to override this logic by making a lens refer to different resources.

To change this logic we have to look at everything involved in the original behavior, otherwise, it just becomes a mess and harder to maintain.

[matthewjumpsoffbuildings]

I understand that the original poster wants to attach the lens to a different resource than the one that will be used in the IN and ORDER BY clauses by default. While this may not be standard use case, there are a million reasons why (especially with client requirements etc) a dev might be required to put the Lens elsewhere.

Also at no point does Nova stop you from doing this. You can build custom queries in your Lenses and attach them to any resource you want, and it will render fine. You can sort, filter, etc, no worries. Its only when it comes to selecting items and running Actions on them that it suddenly blows up. If you are not meant to put Lenses on anything other than the Resource that will be in the FROM clause of the query, that should be made clearer, or enforced more strictly.

Im not suggesting a significant change in Novas Lens behaviour anyway. What would make this much more flexible is - when resolving the query in the ActionRequest->toSelectedResourceQuery(), or in one of the sub functions that is called within that - if a 'FROM' clause, and an 'id' column, is already specified in the Lenses static query() function, respect it - use that when making the IN query to select items for an Action