Issue with N+1 queries with accessors when using Lighthouse GraphQL and Laravel

Question

I'm having a bit of an issue with the old N+1 problem. I'm running Lighthouse 4.8, Laravel 5.8, and have batched_queries and batchload_relations set to true in my config. I have the hasMany and hasOne set in my schema (anonymised a little):

type Team {
    id: Int!
    Y_resource_id: Int
    X_resource_id: Int
    name: String
    description: String
    start_date: Date
    cost: Float
    Y: Resource @hasOne
    X: Resource @hasOne
    members(
        where: _ @whereConditions
        hasDepartmentMember: _ @whereHasConditions(columns: ["z_id","start_date"])
        orderBy: [OrderByClause!] @orderBy
    ): [TeamMember] @hasMany @is_logged_in
    options: [TeamResourceOption] @hasMany
    optionIds: [Int!]
}

I'm using multiple accessor functions which utilise these relationships, as well as some custom resolvers and local scopes. These seem to be the ones that are causing the N+1 issues, for example when I am loading in a Team, one of the php queries that is having the N+1 issue is this (on the Team model after trying to load a team in and populate information):

public function getHasTeamLeaderAttribute(): bool
{
    return $this->members()->teamLeaders()->count() > 0;
}

this is the members() function:

public function members(): HasMany

{
    return $this->hasMany(TeamMember::class);
}

and the on the TeamMember class:

public function scopeTeamLeaders($query)
{
    return $query->where('team_members.group_role', '=', TeamMember::ROLE_TEAM_LEADER);
}

Clockwork is showing this as 2 N+1 issues (I'm calling it twice):

N+1 queries: App\Models\Teams\Team::App\Models\Teams\TeamMember loaded 4 times.
Team.php:254
N+1 queries: App\Models\Teams\Team::App\Models\Teams\TeamMember loaded 2 times.
Team.php:254

I have tried using loadMissing in the accessor to load in the teamLeaders but this seems to increase the N+1 queries, and I have also tried loading the model with $with the relationships I need, however this makes the entire application slower (I assume because it's now autoloading it in where it's not needed).

This is an inherited project, and I'm not completely au fait with GraphQL so any pointers in the right direction would be great. Thanks!

score 1 · Answer 1 · answered May 22 '21 at 19:17

I am not sure what this has to do with GraphQL or Lighthouse since the type you are showing does not seem to have a direct has_team_leader property... but:

No amount of eager loading is going to fix the N+1 problem here.

The issue is this code:

public function getHasTeamLeaderAttribute(): bool
{
    return $this->members()->teamLeaders()->count() > 0;
}

Since it uses the relation methods instead of accessors it will always execute a query, $this->members->count() > 0 could helper here (and eager loading members) but this is also not ideal and also does not use the teamLeaders() scope.

Laravel has a way to deal with this: Counting Related Models.

You can use this to eager load the count of team leaders and use that in your getter to check. It could look something like this:

// where the query is executed:

$teams->loadCount(['members as team_leaders_count' => function ($query) {
    $query->teamLeaders();
}])

// and in your getter:

public function getHasTeamLeaderAttribute(): bool
{
    // Use `team_leaders_count` if loaded, if not fallback to the non-optimal query
    return ($this->team_leaders_count ?? $this->members()->teamLeaders()->count()) > 0;
}

You can also instruct Lighthouse to do the eager loading for you with @withCount but since I'm not seeing where the has_team_leaders was actually called in the schema I have no clue where to add it, but it might be useful to know it's possible.

Issue with N+1 queries with accessors when using Lighthouse GraphQL and Laravel

1 Answers1