Блог

Я достигаю пределов памяти Heroku PHP (512 МБ) при попытке извлечь более 25 тыс. записей из моей базы данных MySQL с помощью Eloquent chunking. В двух словах, я определяю свой SQL-запрос, разбиваю его на фрагменты, а затем загружаю результаты в PhpSpreadsheet. Согласно документам, фрагментация должна уменьшить использование моей памяти, но на практике, похоже, этого не происходит.

Я делаю что-то явно неправильное в приведенном ниже коде? (Я думаю, что большая часть специфики моего запроса совершенно не имеет значения, поэтому извините за дополнительную прокрутку, но хотел включить ее на случай, если я делаю что-то не так в этом.)

         $query = Participant::query()
        ->join('events', 'participants.event_id', '=', 'events.id')
        ->where('events.is_archived', '=', 'false')
        ->leftJoin('users as cu', 'participants.created_by_id', '=', 'cu.id')
        ->leftJoin('users as su', 'participants.selected_by_id', '=', 'su.id')
        ->select(DB::raw(
            'events.title as event_title,' . // A
            'events.store_number as event_store_number,' . // B
            'events.workfront_id as event_workfront_id,' . // C
            'events.company as event_company,' . // D
            'events.business as event_business,' . // E
            'DATE_FORMAT(events.start_date, "%m/%d/%Y") as event_start_date,' . // F
            'DATE_FORMAT(events.end_date, "%m/%d/%Y") as event_end_date,' . // G
            'events.region as event_region,' . // H
            'events.state as event_state,' . // I
            'events.default_language as event_language,' . // J
            'events.description as event_description,' . // K
            'participants.first_name as participant_first_name,' . // L
            'participants.last_name as participant_last_name,' . // M
            'CASE WHEN participants.is_contact <> 0 THEN participants.email ELSE "NO CONTACT" END as participant_email,' . // N
            'CASE WHEN participants.is_contact <> 0 THEN participants.phone ELSE "NO CONTACT" END as participant_phone,' . // O
            'participants.zip_code as participant_zip_code,' . // P
            'participants.language as participant_language,' . // Q
            'CASE WHEN participants.is_customer <> 0 THEN "yes" ELSE "no" END as participant_is_customer,' . // R
            'participants.current_carrier as participant_current_carrier,' . // S
            'CASE WHEN participants.is_contact <> 0 THEN "yes" ELSE "no" END as participant_is_contact,' . // T
            'DATE_FORMAT(participants.created_at, "%m/%d/%Y") as participant_created_date,' . // U
            'cu.first_name as creating_user_first_name,' . // V
            'cu.last_name as creating_user_last_name,' . // W
            'cu.email as creating_user_email,' . // X
            'DATE_FORMAT(participants.selected_date, "%m/%d/%Y") as participant_selected_date,' . // Y
            'su.first_name as selecting_user_first_name,' . // Z
            'su.last_name as selecting_user_last_name,' . // AA
            'su.email as selecting_user_email,' . // AB
            'CASE WHEN events.has_surveys_enabled <> 0 THEN "yes" ELSE "no" END as has_surveys_enabled,' . // AC
            'CASE WHEN events.has_surveys_enabled <> 0 THEN "N/A" ELSE CONCAT("' . request()->getHost() . '/surveys/create?event_id=", events.id) END as survey_link' // AD
        ))
        ->orderBy('events.title')
        ->orderBy('participants.created_at')
    ;

    $spreadsheet = new Spreadsheet();

    $sheetRowIndex = 1;

    $sheet = $spreadsheet->getActiveSheet();

    $query->chunk(1000, function ($eventRecords) use (amp;$sheet, amp;$sheetRowIndex) {
        foreach ($eventRecords as $eventRecord) {
            $sheet->fromArray([
                $eventRecord->event_title,
                $eventRecord->event_store_number,
                $eventRecord->event_workfront_id,
                $eventRecord->event_company,
                $eventRecord->event_business,
                $eventRecord->event_start_date,
                $eventRecord->event_end_date,
                $eventRecord->event_region,
                $eventRecord->event_state,
                $eventRecord->event_language,
                $eventRecord->event_description,
                $eventRecord->participant_first_name,
                $eventRecord->participant_last_name,
                $eventRecord->participant_email,
                $eventRecord->participant_phone,
                $eventRecord->participant_zip_code,
                $eventRecord->participant_language,
                $eventRecord->participant_is_customer,
                $eventRecord->participant_current_carrier,
                $eventRecord->participant_is_contact,
                $eventRecord->participant_created_date,
                $eventRecord->creating_user_first_name,
                $eventRecord->creating_user_last_name,
                $eventRecord->creating_user_email,
                $eventRecord->participant_selected_date,
                $eventRecord->selecting_user_first_name,
                $eventRecord->selecting_user_last_name,
                $eventRecord->selecting_user_email
            ], null, 'A' . $sheetRowIndex);
            $sheetRowIndex  ;
        }
    });