[BUG/QUESTION] Excel empty with lens and query raw

[JoseLuisRNP]

Prerequisites

• [ ] 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: 7.4.4
• Laravel version: 7.8.1
• Nova version: 3.0.2
• Package version: 3.1 / 1.2.1

Description

Download an empty excel when downloading from lens with a custom query

Steps to Reproduce

My query for the lens is like that below and the download is empty, a simply query with a count and group by

 public static function query(LensRequest $request, $query)
    {
        return $request->withOrdering($request->withFilters(
            $query->selectRaw('COUNT(id) as total, DATE(published_at) as dateonly')->from('mytable')->whereNotNull('published_at')->groupBy('dateonly')->orderBy('dateonly', 'DESC')
        ));
    }

Expected behavior:

Download the excel with your data

Actual behavior:

Download a empty excel

Additional Information

I'm try some changes and see that

$query->selectRaw( 'id, name, published_at')->from('illustrations') the excel download it's ok with the data but if you remove the id $query->selectRaw( 'name, published_at')->from('illustrations') the excel is empty again.

Do you need a id to it's work? Any chance of working with a query like the one in my case?

[patrickbrouwers]

Hm, I'm not sure. I don't think we depend on the id, but perhaps without the id no valid model is created. Probably best to try and debug (dd or xdebug) and see where the data gets missing.

[JoseLuisRNP]

It's look like looking for my model id with my dates but i can't give a id, is a group by query with count, is it possible that export with a query raw directly avoid the model?

+wheres: array:2 [
        0 => array:3 [
          "type" => "NotNull"
          "column" => "published_at"
          "boolean" => "and"
        ]
        1 => array:4 [
          "type" => "In"
          "column" => "model.id"
          "values" => array:2 [
            0 => "2020-05-01"
            1 => "2020-04-28"
          ]
          "boolean" => "and"
        ]
      ]

[patrickbrouwers]

Do you have any idea where that model.id in is coming from? Seems something Nova does itself with lens actions perhaps?

[JoseLuisRNP]

I think that the $query param from query method of lens is attach to the model belongs to.

Example of query method of lens

public static function query(LensRequest $request, $query)
    {
        return $request->withOrdering($request->withFilters(
            $query->selectRaw('COUNT(id) as total, DATE(published_at) as dateonly')->from('mytable')->whereNotNull('published_at')->groupBy('dateonly')->orderBy('dateonly', 'DESC')
        ));
    }

The problem is the query without id probably, yo can read in documentation of Nova

Lens Column Selection

When writing your lens query, you should always try to include the resource's ID as a selected column. If the ID is not included, Nova will not be able to display the "Select All Matching" option for the lens. In addition, the resource deletion menu will not be available.

But we can avoid that problem in Laravel-nova-Excel? I try making a new action extends DownloadExcel and implement the concern FromQuery but i don't get it work for now.

[eithed]

It's look like looking for my model id with my dates but i can't give a id, is a group by query with count, is it possible that export with a query raw directly avoid the model?

Afaik this is not possible - Nova itself associates lenses with specific model and then loads given models when unserializing

[matthewjumpsoffbuildings]

I am running into this same issue in a lens that is meant to show counts/totals. When I provide an ID to the lens by using ANY_VALUE i get some data in the downloaded CSV, but since as you say nova is actually hydrating a specific model, the count is now 1 for each entry, instead of the proper result of the COUNT(table.column) as totals query

This is a major issue for any lens that shows totals or aggregates

[eithed]

I am running into this same issue in a lens that is meant to show counts/totals. When I provide an ID to the lens by using ANY_VALUE i get some data in the downloaded CSV, but since as you say nova is actually hydrating a specific model, the count is now 1 for each entry, instead of the proper result of the COUNT(table.column) as totals query

This is a major issue for any lens that shows totals or aggregates

As far as I can remember, that's correct. In the end I believe I was not able to use this library altogether and wrote https://github.com/eithed/export-to-csv to get around either this or queueing issue.

[liucf]

@eithed composer require eithed/export-to-csv is not working. any updates?

[eithed]

@liucf please give a shout using issues tracker on my lib page, with what exactly is not working.

[liucf]

I am running into this same issue in a lens. any updates how to solve it?

[kevariable]

any changes?

[patrickbrouwers]

Haven't seen a PR for it, so no.

[matthewjumpsoffbuildings]

@patrickbrouwers - heres a PR. the main issue seems to be the defaulting to using withKey() here - https://github.com/Maatwebsite/Laravel-Nova-Excel/blob/1.2/src/Requests/ExportLensActionRequest.php#L26

My PR lets the end user override this and use a more flexible whereIn when using a Lens with a custom query such as COUNT() + GROUP BY

In an example where a Lens was using votes.track_id as the column to aggregate on, you would set up the Lens Action like so:

public function actions(Request $request)
{
  return [
    (new DownloadExcel)->withExportQueryKey('votes.track_id'),
  ];
}

[patrickbrouwers]

Thanks @matthewjumpsoffbuildings

[matthewjumpsoffbuildings]

Another alternative I was thinking might work, since https://github.com/Maatwebsite/Laravel-Nova-Excel/blob/1.2/src/Requests/ExportLensActionRequest.php is actually processing the query builder, is to detect if there is a GROUP BY in the Lenses query and if so, use the field from that as the key, instead of id

Im not sure if there are unintended consequences of doing that though? It seems to me, when you paginate a Lens that is using a GROUP BY to aggregate, the pagination will occur on the column specified in the group clause... Perhaps it will be weird if someone makes a Lens with multiple columns in the group clause?

At the very least, could make it so that if the Lens query has a single group column that should be automagically used as the key for a whereIn()

In the meantime, just letting the user manually specify which column should be used for the query as per my PR should suffice

[patrickbrouwers]

@matthewjumpsoffbuildings sounds like a reasonable default

[matthewjumpsoffbuildings]

@patrickbrouwers yea I think so, though I added some more thoughts to the previous comment that might be pertinent

[matthewjumpsoffbuildings]

@patrickbrouwers - created a new, simpler PR. Should work fine for most aggregate Lenses with a single groupBy clause.

Honestly, if you have an aggregate Lens with multiple groupBy clauses, you will already be hard pressed to get a meaningful id to return to Novas UI so you can actually select any individual items on the Lens index... in which case you will probably only ever be able to download all items at best. So I think this is a viable solution