Laravel Eloquent take() is very slow or memory exhausted in large dataset

Question

I have a many-to-many relationship as:

class Game extends Model
{
    public function players()
    {
        return $this->belongsToMany('App\Player');
    }
}

class Player extends Model
{
    public function games()
    {
        return $this->belongsToMany('App\PlayerGame');
    }
}

I have 500k+ game_player entries, foreign keys are created.

It takes a long time to get the result or even memory exhausted when I run this with limit=10:

if ($request->filled('limit') && $request->limit > 0) {
    return response(Game::findOrFail($id)->players->take($request->limit), 200);
} else {
    return response(Game::findOrFail($id)->players, 200);
}

Should I use SQL limit instead? And how should I do it nicely?

Answer 1

You are asking the database for all the related records then filtering them on the PHP side which is loading them all into memory. Since you only want certain records you should be asking the database for those records:

Game::findOrFail($id)->players()->take($request->limit)->get()

This will use the relationship method to query the database and only take a limited set of records. When using the dynamic property $game->players you are loading the entire relationship into a Collection.

Answer 2

If you are retrieving a lot of data you may use 2 methods for iterating the result:

1. Chunking
2. Cursors