Export lens with group by, the sum filed is showing correct in index view, but after export to excel, it always show 1.

[liucf]

• [ 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.

• PHP version: 7.4.10

• Laravel version: 8.33.1

• Nova version: 3.20.0

• Package version: 1.2

Description

Export lens which have group by query, the sum filed is showing correct in index view, but after export to excel, it always show 1.

Steps to Reproduce

In the lens query method, the result is using group by

public static function query(LensRequest $request, $query)
{
  return $request->withOrdering($request->withFilters(
      $query->select(self::columns())
          ->join('products', 'productranks.product_id', '=', 'products.id')
          ->groupBy('productranks.product_id')
          ->orderBy('viewed','desc')
  ));
}

in the columns, it looks like below:

 protected static function columns()
 {
   return collect([
            'productranks.id AS id',
            'products.title',
            DB::raw('COUNT(productranks.id) AS viewed'),
            DB::raw('SUM(CASE WHEN gender = "1" THEN 1 ELSE 0 END) as Male_count'),
        ]
}

in the lens view table, the viewed and male_count fields are showing correct value.

But after export, these files are always one in the exported excel file which is wrong.

Thank you.

[liucf]

Also I noticed that if in the columns method, if it do not have "ID" field. the export excel will be empty.
So I guess that Laravel-Nova-Excel Have to have and ID filed when export data?

If Added the "ID", it can export excel, but some fields which used "COUNT" and "SUM" with "DB::raw" all showing value 1 which is not correct.

[mohamed-ibrahem]

Any updates here?

[matthewjumpsoffbuildings]

i have also noticed this bug/issue. would be good to know what was going on

[patrickbrouwers]

I have no idea, if anyone figures it out, feel free to PR it.