Downloadble excel form API with use Spatie\QueryBuilder for filter and sort

[abkrim]

I am working with an API that, due to the client's wishes, has led me to use Spatie/LaravelQueryBuilder

The client has asked me that in the frontend they have in the list views, the possibility of downloading, in Excel, CSV and PDF.

But of course, he wants it with the same abilities that the query build offers me:
sorted fields
filters,
And others.

That is, the client wants the export to be as it is on the screen, except for the pagination, of course.

Ok, QueryBuilder returns me an Illuminate\Database\Eloquent\Collection.

And with that I don't know how to apply it or if it is possible to an Export class for said model.

It's possible?

Because the only way I can see is to build back into the Export class, the complete query, which is hard.

$commandCenters = QueryBuilder::for(CommandCenter::class)
                ->allowedFields($allowedFields)
                ->allowedFilters($allowedFilters)
                ->allowedSorts($allowedSorts)
                ->with(['townHall:id,name', 'manufacturer:id,name'])->get();
// Illuminate\Database\Eloquent\Collection {#3211 ▼
 // #items: array:947 [▼                

Apreciate help

[patrickbrouwers]

If you drop the ->get() you can just use FromQuery concern. Allowedfields, filters and sorts could be passed via the constructor of the export class.

[abkrim]

Thank you very much for the quick answer.

Still, I hit something.

It is probably the issue that I still have something pending in the api issue, but before continuing with that, I wanted to know if the Laravel-Excel question is correct.

From postman it returns me a code that aims to be the binary, which encourages me to think that on the QueryBuild+Laravel-Excel side it is already solved and I can continue working to see how the donwload issue in an api call.

$commandCenters = QueryBuilder::for(CommandCenter::class)
                ->allowedFields($allowedFields)
                ->allowedFilters($allowedFilters)
                ->allowedSorts($allowedSorts)
                ->with(['townHall:id,name', 'manufacturer:id,name']);
                
return (new CommandCentersExport($commandCenters))->download('CommandCenters.xlsx');                

CommandCentersExport

<?php
namespace App\Exports;

use App\Models\CommandCenter;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\FromQuery;

class CommandCentersExport implements FromQuery
{
  use Exportable;

  public function __construct($collection)
  {
      $this->query = $collection;
  }

  public function query()
  {
      return CommandCenter::query();
  }
}

[abkrim]

Oh my god....

Despite for me. Postman has a button "Send and Download"

A lot of thanks. Work fine.

[abkrim]

Hi @patrickbrouwers

I have some comprehension errors because the query does not pass me correctly instead it returns all the elements.

$download = 'xlsx';

$commandCenters = QueryBuilder::for(CommandCenter::search($request->get('globalSearch')));

// If check $comandCenters->get() has 1 result

 return (new CommandCentersExport($commandCenters))
                    ->download(
                        "CommandCenters-".Carbon::now()->format('YmdHis').".$download",
                    );
                    

if filter this with ray I get a 1 result for the query.

<?php

namespace App\Exports;

use App\Models\CommandCenter;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithHeadings;

class CommandCentersExport implements FromQuery, WithHeadings
{
   use Exportable;

   public function query()
   {
       return CommandCenter::query();
   }

   public function headings(): array
   {
       return array_keys($this->query()->first()->toArray());
   }
}

This return all CommandCenter instead one CommandCenter filtered by the query.

Check code using ray()

class CommandCentersExport implements FromQuery, WithHeadings
{
    use Exportable;

    public function __construct($query)
    {
        $this->query = $query;
    }

    public function query()
    {
        ray(CommandCenter::query())->die();
        return CommandCenter::query();
    }

    public function headings(): array
    {
        return array_keys($this->query()->first()->toArray());
    }
}

Difference is that I send a Spatie\QueryBuilder\QueryBuilder instead Illuminate\Database\Eloquent\Builder

That's is the problem?

[abkrim]

Well I'll leave it here.

I lost the solution. Saptie's QueryBuilder is not the same as Laravel's QueryBuilder.

The result is that with the concern FromQuery doesn't work because it doesn't do the query. Instead I have tested and it works, the concern FromArray

This allows me to work seamlessly with Spatie's QueryBuild

$commandCenters = Spatie\QueryBuilder\QueryBuilder::for(CommandCenter::search($request->get('globalSearch')));

return (new CommandCentersExport($commandCenters->get()->toArray()))
                    ->download(
                        "CommandCenters-".Carbon::now()->format('YmdHis').".$download",
                    );

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithHeadings;

class CommandCentersExport implements FromArray, WithHeadings
{
    use Exportable;

    protected array $commandCenters;

    public function __construct(array $commandCenters)
    {
        $this->commandCenters = $commandCenters;
    }

    public function headings(): array
    {
        if (count($this->commandCenters) > 0) {
            return array_keys($this->commandCenters[0]);
        } else {
            return [];
        }
    }

    public function array(): array
    {
        return $this->commandCenters;
    }
}

[patrickbrouwers]

This should work as well:

class CommandCentersExport implements FromCollection
{
    use Exportable;

    public function collection()
    {
        return Spatie\QueryBuilder\QueryBuilder::for(CommandCenter::search(request('globalSearch')))->get();
    }
}