Problem with memory size of a giant query over a Collection

I don't know why but it worked! I've tested the Eloquent sum() method only, and it didn't take any memory to perform the operation, whereas I use the old foreach it didn't work. Thanks!

Is not a solution, due I want to sum all the values from whole query, not only e single part of it. Your solution would be valid if I split the query in chunks of 100 by 100 or 1000 by 1000 and use the accumulator for each slice of results on each chunk call, but the solution was done by @chaudigv with the Eloquent sum method. Thanks,

Dani

No, I can't do that, due this query is executed with a variable where method, and I cannot make the almost same query two times, one grouping to use the SUM() aggregator in SELECT clause and the other for the normal query to return the data thas is displayed using DataTables in the frontend. Thanks @tray2

I can almost guarantee that running the query twice is still faster and uses way less resources than doing it in php

Hello! I've done it! Now I have the 3 versions of the same code, one that conumes a lot of memory (the foreach over collection), the second that uses Eloquent's method sum() and the last that you proposes: See the beginning of the code, it's the same for 3 versions:

$dataCounter = Application::select(\DB::Raw('SUM(numinst) AS total'));
        //\DB::enableQueryLog();
        $data = Application::select(['id','numinst','nomapp','idapp','fabricante','version','carpeta','comentarios']);

        $numTotal = $numRecords = $data->count();


        //dd($totalInstallations);
        //dd($data);
//dd(\DB::getQueryLog());
        /**
         * Applying search filters over query result as it is was simple query
         */
        if(!empty($searchPhrase)) {



            // Search by a word or part of a phrase (center or user agent)
            if(preg_match("/^[a-z\s]{3,}$/i", $searchPhrase)) {
                $data->where(function($query) use ($searchPhrase) {
                    $query->orWhere('fabricante', 'like', '%' . $searchPhrase . '%');
                       // ->orWhere('osname', 'like', '%' . $searchPhrase . '%');
                });
                $dataCounter->where(function($query) use ($searchPhrase) {
                    $query->orWhere('fabricante', 'like', '%' . $searchPhrase . '%');
                    // ->orWhere('osname', 'like', '%' . $searchPhrase . '%');
                });
                $numRecords = $data->count();
                //dd($numRecords);
            }
        }

In this case, the only problema is that I repeat the same where in $data and $dataCounter builders, that is the only inconvenience I can notice.

Version 1: Foreach (bad way):

foreach($data->get() as $row) { // TODO: This row takes a lot of memory, #error message: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes)
            $totalInstallations += $row->numinst;
        }

Version 2: Eloquent sum method:

$totalInstallations = $data->sum('numinst');

In this case, seems that this call is the same as execute select sum(numinst) due it doesn't consumes resources.

Version 3: Using an alternative query:

$totalInstallations = $dataCounter->first()->total;