How to avoid Maximum execution time of 60 seconds exceeded Laravel without change php.ini max_execution_time [duplicate]

Question

I have a problem with my code where I will export to CSV which has more than 100,000 data, here I have used chunk 2 times, the first is the getData variable, where this variable takes the AssetRepository function in another class, and the other is my use when foreach, if I load 1000 data using a limit, the data can be exported. Is it possible to load the data without changing max_execute_time on php.ini and only using chunk? if you can, how can I optimize my code ?

in this case, I'm using PostgreSQL.

here is the code for AssetRepository.php

class AssetRepository
{
    public $query = null;
    private $trashed = false;

    public static function query()
    {
        $repo = new AssetRepository();
        $repo->query = DB::table('assets as a')
        ->select(
            DB::raw("distinct a.id"),
            "a.id",
            "a.duration as duration",
            DB::raw("COALESCE( NULLIF(a.qr_code,'') , 'QR Code Not Set' ) as qr_code"),
            "a.material_no",
            DB::raw("COALESCE( NULLIF(a.serial_no,'') , 'Serial No Not Set' ) as serial_no"),
            "a.sbu_id",
            "a.pop_id",
            "a.building_id",
            "a.type_id",
            "asset_to_sid.cust_id",
            "a.category_id",
            "a.brand_id",
            "a.model_id",
            "a.id as id",
            "b.name as model",
            "b2.name as brand",
            "p.name as pop",
            "p2.name as sbu",
            "q.name as building",
            "a.updated_at",
            "a.created_at",
            "a.deleted_at",
            'a.eos',
            'a.eol',
            "s.name as sts",
            "c.name as category",
            "a.app_code",
            "a.name",
            "a.status_approval as status_approval",
            "a.approval_notes",
            "a.approval_activities",
            "a.habis_masa_garansi as habis_masa_garansi",
            "permission_approval.action as action_approval",
            DB::raw("CONCAT(u.first_name, ' ', u.last_name) as username"),
            DB::raw("CONCAT(u2.first_name, ' ', u2.last_name) as username2"),
            DB::raw("CONCAT(u3.first_name, ' ', u3.last_name) as approved_by"),
            DB::raw("CASE WHEN q2.name is null THEN 'Not Set' ELSE q2.name END as room"),
            DB::raw("CASE WHEN cast(a.installation_year as text) is null THEN 'Not Set' ELSE cast(a.installation_year as text) END as installation_year"),
            DB::raw("CASE WHEN cast(b.mpls_hierarchy as text) is null THEN 'Not Set' ELSE cast(b.mpls_hierarchy as text) END as mpls_hierarchy"),
            DB::raw("CASE WHEN cast(a2.name as text) is null THEN 'Not Set' ELSE cast(a2.name as text) END as rack"),
            DB::raw("CASE WHEN cast(a.remark1 as text) is null THEN 'No Data' ELSE cast(a.remark1 as text) END as remark1"),
            DB::raw("CASE WHEN cast(a.remark2 as text) is null THEN 'No Data' ELSE cast(a.remark2 as text) END as remark2"),
            DB::raw("CASE WHEN cast(a.remark3 as text) is null THEN 'No Data' ELSE cast(a.remark3 as text) END as remark3"),
            DB::raw("CASE WHEN cast(a.remark4 as text) is null THEN 'No Data' ELSE cast(a.remark4 as text) END as remark4"),
            DB::raw("CASE WHEN cast(a.remark5 as text) is null THEN 'No Data' ELSE cast(a.remark5 as text) END as remark5"),
            DB::raw("CASE WHEN cast(a.desc as text) is null THEN 'No Data' ELSE cast(a.desc as text) END as notes"),
            DB::raw("CASE WHEN a.c_status = 1 THEN 'Complete' ELSE 'Not Complete' END AS complete"),
            DB::raw("CASE WHEN a.c_status = 1 THEN 'btn-primary' ELSE 'btn-warning' END AS btn"),
            DB::raw("CASE WHEN p.offline_sts = 1 THEN 'Offline' ELSE 'Online' END AS offline_sts"),
            DB::raw("CASE WHEN p.offline_sts = 1 THEN 'btn-default' ELSE 'btn-info' END AS offline_btn"),
            DB::raw("CASE WHEN p.offline_sts = 1 THEN 'disabled' ELSE 'enabled' END AS disableds")
        )
        ->leftJoin('assets as a2', 'a.rack', '=', 'a2.id')
        ->join('kategoris as c', 'a.asset_category', '=', 'c.id')
        ->join('users as u', 'a.updated_by', 'u.id')
        ->join('users as u2', 'a.created_by', 'u2.id')
        ->leftJoin('users as u3', 'a.role_approval', 'u3.id')
        ->join('sbus as p', 'p.id', '=', 'a.pop_id')
        ->join('sbus as p2', 'p2.id', '=', 'a.sbu_id')
        ->leftJoin('pops as q', 'a.building_id', '=', 'q.id')
        ->leftJoin('pops as q2', 'a.room_id', '=', 'q2.id')
        ->leftJoin('brands as b', 'a.model_id', '=', 'b.id')
        ->leftJoin('permission_approval', 'a.permission_approval_id', '=', 'permission_approval.id')
        ->leftJoin('asset_to_sid', 'a.id', '=', 'asset_to_sid.asset_id')
        ->join('brands as b2', 'a.brand_id', '=', 'b2.id')
        ->join('statuses as s', 's.id', '=', 'a.status')
        ->leftJoin('statuses as ss', 'p.type', '=', 'ss.id')
        ->orderBy('a.updated_at', 'desc');

        return $repo;
    }

    public function getQuery()
    {
        return $this->query ?? self::query();
    }

    public function get()
    {
        if (!$this->trashed) {
            return $this->getQuery()->whereNull('a.deleted_at')->get();
        }

        return $this->getQuery()->get();
    }
}

dan ini untuk export pada AssetController.php

public function exportAll(Request $request)
{
    $data = AssetRepository::query(); //From AssetRepository Function

    $headers = array(
        'Content-Type'        => 'text/csv',
        'Cache-Control'       => 'must-revalidate, post-check=0, pre-check=0',
        'Content-Disposition' => 'attachment; filename=export.csv',
        'Expires'             => '0',
        'Pragma'              => 'public',
    );

    $response = new StreamedResponse(function () use ($data) {
        $handle = fopen('php://output', 'w');
        $getData = $data->get();
        $remark = Remark::all(['id','label','type']);
        $remarkAsset = RemarkAsset::all(['asset_id','value','remark_id']);
        $getHeader = array_keys((array)$getData[0]);
        $newArray = array();
        $setHeader = array();

        foreach ($getHeader as $header) {
            $setHeader[$header] = $header;
        }

        $remarkHeader = []; //result

        foreach ($remark as $headerRemark) {
            $remarkHeader[] = array(
                'id'    => $headerRemark['id'],
                'label' => $headerRemark['label'],
                'type'  => $headerRemark['type']
            );

            $setHeader[$headerRemark['type']] = $headerRemark['type'];
        }

        $remarkAssets = [];
        foreach ($remarkAsset as $assetRemark) {
            $remarkAssets[] = (array)array(
                'asset_id' => $assetRemark['asset_id'],
                'value' => $assetRemark['value'],
                'remark_id' => $assetRemark['remark_id']
            );
        }

        array_push($newArray, (object)$setHeader);
        // $coountData = count($getData) / 4;
        $chunk = collect($getData);
        $chunk->chunk(500);

        foreach ($chunk as $data) {
            $theKey=array_keys(array_combine(array_keys($remarkAssets), array_column($remarkAssets, 'asset_id')),$data->id);

            foreach ($remarkHeader as $head) {
                $countKey = count($theKey);
                if ($countKey > 0) {
                    $valueRemark = '';

                    foreach ($theKey as $key) {
                        if ($remarkAssets[$key]['remark_id'] == $head['id']) {
                            $valueRemark = $remarkAssets[$key]['value'];
                        }
                    }

                    $data = (array)$data;
                    $data[$head['type']] = $valueRemark;
                    $data = (object)$data;
                } else {
                    $data = (array)$data;
                    $data[$head['type']] = '';
                    $data = (object)$data;
                }
            }
            array_push($newArray, $data);
        }
        $chunkArray = collect($newArray);
        $chunkArray->chunk(500);

        foreach ($chunkArray as $datas) {
            if (is_object($datas))
                $datas = (array)$datas;
            fputcsv($handle, $datas);
        }

        fclose($handle);
    }, 200, $headers);

    return $response->send();
}

if necessary ignore AssetController.php it is the query used in my code

Answer 1

You can call set_time_limit(0) to remove the time limit from the rest of the execution, or you can call set_time_limit(n) in each iteration of a loop (for example) to reset the timer for n more seconds.

https://www.php.net/manual/en/function.set-time-limit.php