Query with Eloquent exhausts the allowed memory limit

Question

I have a really, really simple query

select * 
from `users` 
where (`active` = 1 and `newsletter` = 1) and (`terminated` = 0 or (`terminated` = 1 and `newsletter_terminated` = 1));

I'm building it with eloquent like this (The above query gets output when I make it with toSql()

$recipients = User::where([
    'active' => 1,
    'newsletter' => 1
])->where(function ($query) {
    $query->where('terminated', 0)
        ->orWhere(function ($query){
            $query->where('terminated', 1)
                ->where('newsletter_terminated', 1);
        });
})->get();

But when I execute this script I'm getting the error

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes) in /xxx/eloquent/vendor/illuminate/database/Illuminate/Database/Connection.php on line 303

When I write specific columns in the get method, like get(['id']); it works without problems. But I still don't understand why it doesn't work otherwise. It seems that there is a memory leak, but where?

When I execute the query in my SQL client, the query gets finished withing 3 ms, so it doesn't look like there is too much data going on. Unfortunetely, I don't have xdebug on the vagrant box installed, so I can't get the stacktrace currently.

Answer 1

Something similar happened to me. Check the $with property in your model. Maybe you have too many relationships loaded automatically. Try not to do that. Each object allocates memory. If you have 15000 rows with many relations, and you eager load each one, memory blows away. I've ended writing a raw mysql query and problem solved.