[QUESTION] or [BUG] Export only includes the first batch/chunk

[maxacarvalho]

Prerequisites

• [X ] Have read and understood: https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/.github/SUPPORT.md
• [ X] Checked if your Laravel Excel version is still supported: https://docs.laravel-excel.com/3.1/getting-started/support.html#supported-versions
• [ X] Checked that your question isn't already asked before.
• [ X] Filled in the entire issue template

Versions

• PHP version: 7.4
• Laravel version: 7.27
• Package version: 3.1.21

Description

I have an export that relies on a query, the export works fine except that it only includes the first chunk, or first batch.
For example, if the Laravel-Excel chunk_size configuration is set to 1000 the generated file will be 1000, if I set to 2000 it will be 2000 and so on.
I already tried generating an Excel file and a CSV file, the issue is the same.

I searched on the GitHub issues a lot and I could not find anything that points me to a solution.

Additional Information

Here's my export:

<?php

namespace App\Exports;

use App\Models\PatientSchedule\PatientScheduleModel;
use Carbon\CarbonInterface;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;

class PatientScheduleExport implements FromQuery, WithHeadings, WithMapping, ShouldQueue
{
    use Exportable;

    public const EXPORT_FILE_NAME = 'agenda_pacientes.xlsx';

    private Collection $filters;

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

    public function headings(): array
    {
        return [
            'Data',
            'Horário',
            'Procedimento',
            'Especialidade',
            'CRA',
            'Paciente',
            'Profissional',
            'Status',
            'Check-In',
            'Triagem',
            'Atendimento',
            'Retorno',
            'Médico Retorno',
            'Quantidade',
            'Alteração',
            'Notificação',
        ];
    }

    public function map($row): array
    {
        return [
            $this->formatDate($row->appointment_date),
            $row->appointment_time,
            $row->procedure,
            $row->speciality_type,
            $row->external_id,
            $row->patient_name,
            $row->doctor_name,
            $row->status,
            $this->formatDate($row->received, 'H:i:s'),
            $this->formatDate($row->screened, 'H:i:s'),
            $this->formatDate($row->attended, 'H:i:s'),
            $this->checkStatus($row->mr_schedule_appointment, $row->procedure, $row->status),
            $row->mr_doctor_return_name,
            $row->mr_quantity,
            $this->checkStatus($row->mr_abnormal, $row->procedure, $row->status),
            $this->checkStatus($row->mr_notification, $row->procedure, $row->status),
        ];
    }

    public function query()
    {
        return PatientScheduleModel::query()->toExport($this->filters);
    }

    private function checkStatus($status, $procedure, $appointment_status)
    {
        if ('EXAME' === strtoupper($procedure) && 'ATTENDED' === strtoupper($appointment_status)) {
            if (! $status) {
                return 'não';
            }

            return 'sim';
        }

        return null;
    }

    private function formatDate($date, string $format = 'd/m/Y')
    {
        if ($date instanceof CarbonInterface) {
            return $date->format($format);
        }

        return $date;
    }
}

And here's the toExport query you see I'm calling above (I omitted some query details cause I don't think they are necessary):

    public function toExport(\Illuminate\Support\Collection $filters)
    {
        return $this
            ->select([
                'patient_schedules.appointment_date',
                'patient_schedules.appointment_time',
                'schedule_procedures.description as procedure',
                'speciality_types.type as speciality_type',
                'patients.external_id',
                'patients.name as patient_name',
                'doctors.name as doctor_name',
                'patient_schedule_status.label as status',
                'medical_reports.qty as mr_quantity',
                'medical_reports.schedule_appointment as mr_schedule_appointment',
                'medical_reports.abnormal as mr_abnormal',
                'medical_reports.notification as mr_notification',
            ])
            ->addSelect([
                'received' => PatientScheduleStatusEventModel::query()->select('created_at')
                    ->whereColumn('patient_schedule_status_events.patient_schedule_id', '=', 'patient_schedules.id')
                    ->where('patient_schedule_status_events.new_status', '=', PatientScheduleStatusModel::RECEIVED)
                    ->limit(1),
            ])
            ->addSelect([
                'screened' => PatientScheduleStatusEventModel::query()->select('created_at')
                    ->whereColumn('patient_schedule_status_events.patient_schedule_id', '=', 'patient_schedules.id')
                    ->where('patient_schedule_status_events.new_status', '=', PatientScheduleStatusModel::SCREENED)
                    ->limit(1),
            ])
            ->addSelect([
                'attended' => PatientScheduleStatusEventModel::query()->select('created_at')
                    ->whereColumn('patient_schedule_status_events.patient_schedule_id', '=', 'patient_schedules.id')
                    ->where('patient_schedule_status_events.new_status', '=', PatientScheduleStatusModel::ATTENDED)
                    ->limit(1),
            ])
            ->addSelect([
                'mr_doctor_return_name' => DB::table('doctors as d')->select('name')
                    ->whereColumn('medical_reports.appointment_doctor_id', 'd.id')
                    ->where('medical_reports.schedule_appointment', '=', 1)
                    ->limit(1),
            ])
            ->withCasts([
                'received' => 'datetime',
                'screened' => 'datetime',
                'attended' => 'datetime',
            ])
            ->joinDoctors()
            ->joinPatients()
            ->joinSpecialityTypes()
            ->joinScheduleProcedures()
            ->joinPatientScheduleStatus()
            ->joinMedicalReports()
            ->when($filters->has('appointment_date'), static function (PatientScheduleQueryBuilder $query) use ($filters) {
                $filter = json_decode($filters->get('appointment_date'), true);

                return $query->whereBetween('patient_schedules.appointment_date', [$filter['from'], $filter['to']]);
            })
            ->when($filters->has('procedure_id'), static function (PatientScheduleQueryBuilder $query) use ($filters) {
                return $query->where('patient_schedules.procedure_id', '=', $filters->get('procedure_id'));
            })
            ->when($filters->has('speciality_type_id'), static function (PatientScheduleQueryBuilder $query) use ($filters) {
                return $query->where('patient_schedules.speciality_type_id', '=', $filters->get('speciality_type_id'));
            })
            ->when($filters->has('doctor_id'), static function (PatientScheduleQueryBuilder $query) use ($filters) {
                return $query->where('patient_schedules.doctor_id', '=', $filters->get('doctor_id'));
            })
            ->when($filters->has('appointment_status'), static function (PatientScheduleQueryBuilder $query) use ($filters) {
                $status = $filters->get('appointment_status');

                return $query->{$status}();
            })
            ->groupBy('patient_schedules.id', 'schedule_procedures.description', 'patient_schedule_status.label')
            ->orderByRaw('doctors.name')
            ->orderBy(
                PatientScheduleStatusEventModel::query()->select('created_at')
                    ->whereColumn('patient_schedule_status_events.patient_schedule_id', '=', 'patient_schedules.id')
                    ->where('patient_schedule_status_events.new_status', '=', PatientScheduleStatusModel::RECEIVED)
                    ->limit(1)
            );
    }

Thanks!

[maxacarvalho]

Any help here @patrickbrouwers ? Ps.: Please forgive me to mention you directly but I'm a bit lost with the problem.

[patrickbrouwers]

Have you checked the queue:work output? Does it call multiple jobs?

[maxacarvalho]

Hello @patrickbrouwers thanks for your reply.

I'm using Horizon, and I can confirm that it calls only one job. That's weird, I expect to see multiple so it process each batch at a time.

[patrickbrouwers]

Does the one job not have a chain attached to it?

[maxacarvalho]

Hi @patrickbrouwers

I'm not sure I understood your question, I simply call the exporter like:

(new PatientScheduleExport($filters))->store('export.xlsx');

But it does not trigger the necessary next chunks. If I increase the chunk size it exports that number, so only the first chunk is included. I understand that the exporter should create as many jobs as necessary to finish all the chunks no?

[patrickbrouwers]

I meant if you open Horizon job details, can you see a chain in the payload?

[maxacarvalho]

I'm testing locally now. Once I trigger the job with (new PatientScheduleExport(collect([])))->store('export.xlsx');

I can see that it creates 4 jobs on Redis:

1. horizon:job:Maatwebsite\Excel\Jobs\AppendQueryToSheet
2. horizon:job:Maatwebsite\Excel\Jobs\CloseSheet
3. horizon:job:Maatwebsite\Excel\Jobs\QueueExport
4. horizon:job:Maatwebsite\Excel\Jobs\StoreQueuedExport

So it seems that's not sending the correct number of jobs?

The weird thing is that I recall this was working before, I could export all my records without a problem.

[patrickbrouwers]

It should have more AppendQueryToSheet jobs I think. Not sure why it doesn't work for you anymore. You might have to dive into the code that builds up the job chain. Maybe the count query that determines the amount of jobs is wrong for your query.

[maxacarvalho]

Hummm yeah, that count issue rings a bell. I will have a look on that. Thanks for your help so far @patrickbrouwers !!!

[patrickbrouwers]

Have a look at this documentation section: https://docs.laravel-excel.com/3.1/exports/queued.html#custom-query-size

[maxacarvalho]

@patrickbrouwers that's it!
My colleague changed the query and it now includes a group by, so that's probably messing with the count.
I will fix it by following your advice and use the WithCustomQuerySize concern.

Thank you very much!!!

[patrickbrouwers]

You're welcome, glad we figured it out :)

[odaiderawi]

Hello guys, I'm facing the same issue here and I did custom query size and using WithCustomQuerySize concern but still got only the first chuck!

[arturasfrontit]

Hello guys, I'm facing the same issue here and I did custom query size and using WithCustomQuerySize concern but still got only the first chuck!

maybe your chunk size is too small?

[arturasfrontit]

I have same problem with Maatwebsite\LaravelNovaExcel