Laravel多态关联导致了N+1问题。

huangapple go评论66阅读模式
英文:

Laravel polymorphic relationship causes n+1 problem

问题

I am using polymorphic relationship to create multiple types. I have a node and edge models that describe a network, and each node can be one of multiple types.

Node model has...

public function nodeable()
{
    return $this->morphTo();
}

And the Company model (one of the "types" of node) has...

public function node()
{
    return $this->morphOne(Node::class, 'nodeable');
}

When retrieving a list of Nodes with their associated "type" model, I call the following...

$nodes = Node::with('nodeable')->get();
return NodeResource::collection($nodes);

the node resource class NodeResource has...

return [
    'id' => $this->id,
    'entity_type' => $this->nodeable_type,
    'entity' => $this->when($this->nodeable, function () {
        if ($this->nodeable instanceof \App\Models\Property) {
            return new PropertyResource($this->whenLoaded('nodeable'));
        }
        if ($this->nodeable instanceof \App.Models.Individual) {
            return new IndividualResource($this->whenLoaded('nodeable'));
        }
        if ($this->nodeable instanceof \App.Models.Company) {
            return new CompanyResource($this->whenLoaded('nodeable'));
        }
        if $this->nodeable instanceof \App.Models.Trust) {
            return new TrustResource($this->whenLoaded('nodeable'));
        }
    }),
];

This does seem to work, but when I look at the SQL run during this retrieval I find that I have a n+1 issue. The following query is run for every node...

select
  *
from
  `nodes`
where
  `nodes`.`nodeable_type` = 'App\Models\Property'
  and `nodes`.`nodeable_id` = 1
  and `nodes`.`nodeable_id` is not null
limit
  1

My issue is that I don't understand why I get n+1 seemingly pointless queries when I have with('nodeable') in the query?

After n+1 of the above have run, some more queries are run that make more sense to me....

select * from `individuals` where `individuals`.`id` in (1, 2, 3, 4)

select * from `trusts` where `trusts`.`id` in (1, 2, 3, 4, 5, 6, 7, 8)

select * from `companies` where `companies`.`id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

select * from `properties` where `properties`.`id` in (1, 2, 3, 4, 5, 6)

select * from `nodes`

ADDITIONAL QUESTION: are the 5 queries above what an eager loaded polymorphic relationship looks like (if there were 4 nodeable types)? Normally, I would have expected a join, but my feeling is that this is how polymorphic eager loading would have to function

EDIT: If I do not use this resource the n+1 problem goes away. I do need to use this resource ultimately. In previous projects I have used the same mechanism and it has not resulted in n+1

EDIT2: Looking at the $nodes data before it is passed to the NodeResource, the nodeable data is present, meaning the with('nodeable') works.

EDIT3: I have tried simplifying the NodeResource to always return a PropertyResource (since all types are the same right now)...

'entity' => new PropertyResource($this->whenLoaded('nodeable')),

because it was suggested that the call to $this->nodeable in the NodeResource might be the issue. This did not resolve the n+1

EDIT4: I found the culprit. From within the nodeable resource classes I was calling 'node_id' => $this->node->id. I should have noticed that the erroneous n+1 queries were referring not to the nodeable models (i.e. the fetching of a nodeable) but the node model itself. My apologies to anyone involved with this ticket. But I learnt something of from the attempts to help so thanks.

英文:

I am using polymorphic relationship to create multiple types. I have a node and edge models that describe a network, and each node can be one of multiple types.

Node model has...

public function nodeable()
{
    return $this->morphTo();
}

And the Company model (one of the "types" of node) has...

public function node()
{
    return $this->morphOne(Node::class, 'nodeable');
}

When retrieving a list of Nodes with their associated "type" model, I call the following...

$nodes = Node::with('nodeable')->get();
return NodeResource::collection($nodes);

the node resource class NodeResource has...

return [
    'id' => $this->id,
	'entity_type' => $this->nodeable_type,
	'entity' => $this->when($this->nodeable, function () {
		if ($this->nodeable instanceof \App\Models\Property) {
			return new PropertyResource($this->whenLoaded('nodeable'));
		}
		if ($this->nodeable instanceof \App\Models\Individual) {
			return new IndividualResource($this->whenLoaded('nodeable'));
		}
		if ($this->nodeable instanceof \App\Models\Company) {
			return new CompanyResource($this->whenLoaded('nodeable'));
		}
		if ($this->nodeable instanceof \App\Models\Trust) {
			return new TrustResource($this->whenLoaded('nodeable'));
		}
	}),
];

This does seem to work, but when I look at the SQL run during this retrieval I find that I have a n+1 issue. The following query is run for every node...

select
  *
from
  `nodes`
where
  `nodes`.`nodeable_type` = 'App\Models\Property'
  and `nodes`.`nodeable_id` = 1
  and `nodes`.`nodeable_id` is not null
limit
  1

My issue is that I don't understand why I get n+1 seemingly pointless queries when I have with('nodeable') in the query?

After n+1 of the above have run, some more queries are run that make more sense to me....

select * from `individuals` where `individuals`.`id` in (1, 2, 3, 4)

select * from `trusts` where `trusts`.`id` in (1, 2, 3, 4, 5, 6, 7, 8)

select * from `companies` where `companies`.`id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

select * from `properties` where `properties`.`id` in (1, 2, 3, 4, 5, 6)

select * from `nodes`

ADDITIONAL QUESTION: are the 5 queries above what an eager loaded polymorphic relationship looks like (if there were 4 nodeable types)? Normally, I would have expected a join, but my feeling is that this is how polymorphic eager loading would have to function

EDIT: If I do not use this resource the n+1 problem goes away. I do need to use this resource ultimately. In previous projects I have used the same mechanism and it has not resulted in n+1

EDIT2: Looking at the $nodes data before it is passed to the NodeResource, the nodeable data is present, meaning the with('nodeable') works.

EDIT3: I have tried simplifying the NodeResource to always return a PropertyResource (since all types are the same right now)...

'entity' => new PropertyResource($this->whenLoaded('nodeable')),

because it was suggested that the call to $this->nodeable in the NodeResource might be the issue. This did not resolve the n+1

EDIT4: I found the culprit. From within the nodeable resource classes I was calling 'node_id' => $this->node->id. I should have noticed that the erroneous n+1 queries were referring not to the nodeable models (i.e. the fetching of a nodeable) but the node model itself. My apologies to anyone involved with this ticket. But I learnt something of from the attempts to help so thanks.

答案1

得分: 1

当你调用$this->nodeable时,如果资源尚未加载,则会检索该资源。因此,你的whenLoaded调用并没有做太多事情,因为你目前尝试首先检索它,然后再检查是否已加载。

因此,你可以改进你的代码,类似于以下内容:

return [
    'id' => $this->id,
    'entity_type' => $this->nodeable_type,
    'entity' => $this->when($this->resource->relationLoaded('nodeable'), function () {
        if ($this->nodeable instanceof \App\Models\Property) {
            return new PropertyResource($this->nodeable);
        }
        if ($this->nodeable instanceof \App.Models.Individual) {
            return new IndividualResource($this->nodeable);
        }
        if ($this->nodeable instanceof \App.Models.Company) {
            return new CompanyResource($this->nodeable);
        }
        if $this->nodeable instanceof \App.Models.Trust) {
            return new TrustResource($this->nodeable);
        }
    }),
];

如果这没有解决你的n+1问题,那么可能发生了其他情况。

英文:

When you call $this->nodeable, the resource will be retrieved if it is not loaded on the resource. So your whenLoaded call doesn't do that much since you currently try to retrieve it first and then you check if it has been loaded.

So you could improve your code to something like this:

return [
    'id' => $this->id,
    'entity_type' => $this->nodeable_type,
    'entity' => $this->when($this->resource->relationLoaded('nodeable'), function () {
        if ($this->nodeable instanceof \App\Models\Property) {
            return new PropertyResource($this->nodeable);
        }
        if ($this->nodeable instanceof \App\Models\Individual) {
            return new IndividualResource($this->nodeable);
        }
        if ($this->nodeable instanceof \App\Models\Company) {
            return new CompanyResource($this->nodeable);
        }
        if ($this->nodeable instanceof \App\Models\Trust) {
            return new TrustResource($this->nodeable);
        }
    }),
];

If this doesn't fix your n+1 issue then something else is happening.

答案2

得分: 0

我找到了罪魁祸首。在可节点资源类内部,我调用了 'node_id' => $this->node->id。我应该注意到错误的n+1查询不是指向可节点模型(即获取可节点),而是节点模型本身。对于与此工单有关的任何人,我深感抱歉。但通过尝试帮助,我学到了一些东西,所以谢谢。

英文:

I found the culprit. From within the nodeable resource classes I was calling 'node_id' => $this->node->id. I should have noticed that the erroneous n+1 queries were referring not to the nodeable models (i.e. the fetching of a nodeable) but the node model itself. My apologies to anyone involved with this ticket. But I learnt something of from the attempts to help so thanks.

huangapple
  • 本文由 发表于 2023年2月24日 02:46:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/75549080.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定