n+1 query problem with nested JsonResources

Hello,

i have a user and setting model, in my case a user and setting have a 1:1 relation. For each model there is a CRUD API endpoint, e.g. for better understanding /api/users and /api/users/{user}/settings

This endpoints returns a JsonResource. The user resource includes the setting resource on index and show GET request. (See code snippets below)

The problem i figured out was, that i trigger the n+1 problem in the SettingResource with $this->user->id. If i replace this with the foreign key $this->user_id everything is working fine and i have no n+1 problem.

Sound's logically and for now i have fixed my problem. But i want to understand how can i avoid this n+1 Problem in this $this->user->* case? Maybe in a next feature the SettingResource should return the user's email and $this->user->email triggers the error again.

maybe someone can help me

class UserResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            'id'         => $this->id,
            'email'      => $this->email,
            'locale'     => $this->locale,
            'settings'   => $this->whenLoaded('setting', SettingResource::make($this->setting)),
        ];
    }
}

class SettingResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            'id'      => $this->id,
            'user_id' => $this->user->id,
           'foo' => 'bar',
    }
}

class User extends Model 
{
    protected $with = [
        'setting',
    ];

    public function setting(): HasOne
    {
        return $this->hasOne(Setting::class)->withDefault();
    }
}

class Setting extends Model
{
    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }
}

/** parts of the migration */
Schema::create('settings', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->uuid('user_id');

    // some other fields

    $table->foreign('user_id')->references('id')->on('users')->cascadeOnDelete();

    $table->timestamps();
});

Schema::create('users', function (Blueprint $table) {
    $table->uuid('id')->primary();
    
    // some other fields

    $table->timestamps();
    $table->softDeletes();
});

public function index(): ResourceCollection
    {
        return UserResource::collection(
            User::query()->paginate($user)
        );
    }

tykus

5 years ago

Level 104

In order to load the relation, there must be a query for that relation. For a hasOne relationship, N will be 1, you will have two queries.

In the SettingResource, you do not check if the relation is loaded before using $this->user->id, so it will make a query if the associated User has not already been loaded. However, once loaded (eager- or lazy-) the User instance (and its properties are available without needing further queries.

Whenever you are retrieving multiple parent records, and you need the related record, remember to eager-load the nested relation. So, the following would return the paginated User records, each with its associated settingrequiring only two queries:

public function index(): ResourceCollection
{
	return UserResource::collection(
		User::with('setting')->paginate($user)
	);
}

JanPe

5 years ago

Level 4

Thank you for your explanation. In my user model I already indicate the loading of setting with

    protected $with = [
        'setting',
    ];

Does that make any difference to that User::with('setting')->paginate($user) ?

I've already tried to check whether the relationship has been loaded, but it doesn't make any difference

class SettingResource extends JsonResource
{
    public function toArray($request)
    {
        return [
           'id' => $this->id,
           'user_id' => $this->whenLoaded('user', $this->user->id),
           'foo' => 'bar',
    }
}

JanPe

5 years ago

Level 4

Okay, i checked it again.

This solution does not work:

class User ...
{
    protected $with = [
        'setting',
        'setting.user',
    ];
}

public function index(): ResourceCollection
{
	return UserResource::collection(
		User::query()->paginate()
	);
}

class SettingResource extends JsonResource
{
    public function toArray($request)
    {
        return [
           'id' => $this->id,
           'user_id' => $this->whenLoaded('user', fn() => $this->user->id, null),
           'foo' => 'bar',
    }
}

And this solution does it

class User ...
{
    protected $with = [

    ];
}

public function index(): ResourceCollection
{
	return UserResource::collection(
		User::with(['setting', 'setting.user'])->paginate()
	);
}

But I don't understand why one thing works and the other doesn't: D When I debug the eloquent model, $ with is correctly set by the model and transferred to the QueryBuilder

Please or to participate in this conversation.