API resource collection N+1 performance

Hey, if you are already eager loading the relation, show us the code with the problem, so we can take a better look at the problem.

thanks. see code excerpts below...

here is my controller where i lazy eager load in some of the relationships

// mycontroller.php

public function show(Role $role)
    {
        $role->load(
            'matches',
            'matches.role',
            'matches.conversation'
            'matches.person'
        );

        return new BundleResource($role);
    }

here is the resource definition for the first api resource

// BundleResource.php

    public function toArray($request)
    {
        return [
            'role_id' => $this->id,
            'role_title' => $this->title,
            'matches' => MatchResource::collection($this->matches),
        ];
    }

here is the resource definition for a second api resource used by the above

// MatchResource.php

   public function toArray($request)
    {
        return [
            'id' => $this->id,
            'role_id' => $this->role->id,
            'person' => new PersonResource($this->person),
            'status' => $this->status,
            'conversation' => $this->when($this->conversation, function(){
                return
                [
                    'id' => $this->conversation->id,
                    'match_id' => $this->conversation->match_id,
                    'latest_message' => new Message($this->conversation->messages()->latest()->first()),
                ];
            }),
            'updated_at' => $this->updated_at->format('Y-m-d H:i:s')
        ];
    }

@PAUL-A-BYFORD - Okay, watching at this i think that you have a problem here, you are using the query builder instead of a collection:

         'latest_message' => new Message($this->conversation->messages()->latest()->first()),

When you are loading the roles, you can also load the conversation messages:

        $role->load(
            'matches',
            'matches.role',
            'matches.conversation.messages'
            'matches.person'
        );

Then I think you can try something like this:

   public function toArray($request)
    {
        return [
            'id' => $this->id,
            'role_id' => $this->role->id,
            'person' => new PersonResource($this->person),
            'status' => $this->status,
            'conversation' => $this->when($this->conversation, function(){
                return
                [
                    'id' => $this->conversation->id,
                    'match_id' => $this->conversation->match_id,
                    'latest_message' => new Message($this->conversation->messages->sortByDesc('created_at')->first()), // Something like this should work
                ];
            }),
            'updated_at' => $this->updated_at->format('Y-m-d H:i:s')
        ];
    }

unfortunately this did not solve the problem of multiple database queries. to help explore the issue more easily I created a quick basic repo here to illustrate the problem https://github.com/paul-a-byford/nplus1.git

in this repo I have created 3 models & tables for user, conversation, messages. i've also created seeders and pulled in telescope

when hitting the /messages route on this repo you will see over 100 database queries, triggered by an attempt to pull in an attribute from a distant relationship

// App\Http\Resources\Message.php

   public function toArray($request)
    {
        return [
            'message' => $this->message,
            'message_is_read' => $this->is_read,
            'avatar_url' => $this->conversation->user->avatar_url,
        ];
    }

how would you recommend minimising the number of database queries. is there a way to eager load relationships for his scenario?

Use deeper eager loading:

Route::get('/messages', function () {
    $users = App\User::all();

    $users->load('conversations.messages.conversation.user');

    return UserResource::collection($users);
});

@manelgavalda @staudenmeir thank you. a combination of both your suggestions has reduced the query volume significantly.

a follow on question - if i were to add a tag model and a pivot table conversation_tag, what is the correct way to eager load pivot table relations?

eg is it;

$users->load('conversations.messages.conversation.conversation_tag');

plus would i need to do anything else, such as explicitly define a file for the pivot model?

It's not possible to eager load pivot table relationships.

You could split the BelongsToMany relationship into HasMany and BelongsTo, but I wouldn't recommend that.