Laravel-Nova-Excel: [BUG/QUESTION] Excel empty with lens and query raw

Prerequisites

Able to reproduce the behaviour outside of your code, the problem is isolated to Laravel Excel.
Checked that your issue isn’t already filed.
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?

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 18 (10 by maintainers)

Most upvoted comments

@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'),
  ];
}

matthewjumpsoffbuildings on Oct 11, 2021

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

patrickbrouwers on Aug 16, 2021