Laravel paginate 'Allowed memory exhausted'

0
votes

I got the problem when i was trying to paginate results of big query with several joins and groupBy inside. Part of my code here:

$lots = Lot::select('lots.id', 'lots.name', 'lots.slug', 'lots.description', 'lots.customer as lot_customer',
            'lots.created_at', 'lots.measure', 'lots.delivery_place', 'lots.amount as real_amount', 'lots.amount')
            ->leftJoin('tenders', 'tenders.id', '=', 'lots.tender_id')
            ->leftJoin('customers', 'lots.customer_id', '=', 'customers.id')
            ->groupBy('lots.id')
            ->paginate(25);

I had 420650 - total records in database, but for pagination i wanted to show only 25 records per page. I got message:

Allowed memory size exhausted (tried to allocate 16777224 bytes)...

If you have way to resolve such problem, please share. Thank you!

3
phpmysqllaravelpagination
Why are you grouping by ID? Isn't it a unique field in ur DB? – user3718908
Not sure what you're thinking is unexpected behavior. You've exhausted the resources by a large, intensive query. – user12121025
join multiplied my records of lots. – Dmitriy Semenov
I meant its like difference between: select count() as aggregate from table; select count() as aggregate from table GROUP BY id; – Dmitriy Semenov
Can you share the full and exact error message? Also, show what you've tried to resolve the error – Nico Haase

3 Answers

1
votes

This problem seems to be fixed now. I had this problem in Laravel 7.0. Updated to 7.20 and I dont get the memory exhausted anymore, even down to 32M of memory limit.

0
votes

You can increase the memory limit php.ini

ini_set('memory_limit','160M'); or memory_limit = 2024M
0
votes

I found that problem was where laravel was getting total count of records for pagination results:

public function paginate($perPage = null, $columns = ['*'], $pageName = 'page', $page = null)
{
 ...
 $total = $this->toBase()->getCountForPagination()
 ...
}

protected function runPaginationCountQuery($columns = ['*'])
{
 return $this->cloneWithout($without)->cloneWithoutBindings($this->unions ? ['order'] : ['select', 'order'])->setAggregate('count', $this->withoutSelectAliases($columns))->get()->all();
}

In general last method runPaginationCountQuery in order to get count of records creates raw query to DB like:

select count(*) as aggregate from lots left join tenders on tenders.id = lots.tender_id left join customers on lots.customer_id.....

But with groupBy in query instead of one row with total count i got 420650 rows by one per row! It was enough big array which was created only to get count of rows, and in my case it was the reason of finished memory.

I understand that groupBy for big data isn't good decision and i made refactoring of my code to undo groupBy from my query. But in fact it seems to me that it wasn't normal behavoir of laravel that you probably expected.