Am I doing eager loading correctly? (Eloquent)

Question

I have a method that needs to pull in information from three related models. I have a solution that works but I'm afraid that I'm still running into the N+1 query problem (also looking for solutions on how I can check if I'm eager loading correctly).

The three models are Challenge, Entrant, User.

Challenge Model contains:

 /**
 * Retrieves the Entrants object associated to the Challenge
 * @return \Illuminate\Database\Eloquent\Relations\HasMany
 */
public function entrants()
{
    return $this->hasMany('App\Entrant');
}

Entrant Model contains:

     /**
     * Retrieves the Challenge object associated to the Entrant
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function challenge()
    {
        return $this->belongsTo('App\Challenge', 'challenge_id');
    }

    /**
     * Retrieves the User object associated to the Entrant
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function user()
    {
        return $this->belongsTo('App\User', 'user_id');
    }

and User model contains:

 /**
 * Retrieves the Entrants object associated to the User
 * @return \Illuminate\Database\Eloquent\Relations\HasMany
 */
public function entrants()
{
    return $this->hasMany('App\Entrant');
}

The method I am trying to use eager loading looks like this:

/**
 * Returns an array of currently running challenges
 * with associated entrants and associated users
 * @return array
 */
public function liveChallenges()
{
    $currentDate = Carbon::now();
    $challenges = Challenge::where('end_date', '>', $currentDate)
        ->with('entrants.user')
        ->where('start_date', '<', $currentDate)
        ->where('active', '1')
        ->get();

    $challengesObject = [];
    foreach ($challenges as $challenge) {
        $entrants = $challenge->entrants->load('user')->sortByDesc('current_total_amount')->all();
        $entrantsObject = [];
        foreach ($entrants as $entrant) {
            $user = $entrant->user;
            $entrantsObject[] = [
                'entrant' => $entrant,
                'user' => $user
            ];
        }

        $challengesObject[] = [
            'challenge' => $challenge,
            'entrants' => $entrantsObject
        ];
    }

    return $challengesObject;
}

I feel like I followed what the documentation recommended: https://laravel.com/docs/5.5/eloquent-relationships#eager-loading

but not to sure how to check to make sure I'm not making N+1 queries opposed to just 2. Any tips or suggestions to the code are welcome, along with methods to check that eager loading is working correctly.

Answer 1

Use Laravel Debugbar to check queries your Laravel application is creating for each request.

Your Eloquent query should generate just 3 raw SQL queries and you need to make sure this line doesn't generate N additional queries:

$entrants = $challenge->entrants->load('user')->sortByDesc('current_total_amount')->all()

Answer 2

when you do ->with('entrants.user') it loads both the entrants and the user once you get to ->get(). When you do ->load('user') it runs another query to get the user. but you don't need to do this since you already pulled it when you ran ->with('entrants.user').

If you use ->loadMissing('user') instead of ->load('user') it should prevent the redundant call.

But, if you leverage Collection methods you can get away with just running the 1 query at the beginning where you declared $challenges:

foreach ($challenges as $challenge) {
    // at this point, $challenge->entrants is a Collection because you already eager-loaded it
    $entrants = $challenge->entrants->sortByDesc('current_total_amount'); 
    // etc...

You don't need to use ->load('user') because $challenge->entrants is already populated with entrants and the related users. so you can just leverage the Collection method ->sortByDesc() to sort the list in php.

also, You don't need to run ->all() because that would convert it into an array of models (you can keep it as a collection of models and still foreach it).