处理 n+1 查询问题,使用 hasManyThrough 关联。

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

handle n+1 with hasManyThrough relation

问题

我的产品有一个名为“values”的关系,如下所示:

public function values()
{
    return $this->hasManyThrough(VariantValue::class, VariantProductOption::class, 'product_id', 'product_option_id');
}

一切都正常,但当我在单个页面上有多个产品时,会出现N+1问题:

select `variant_sku_values`.*, `variant_sku_product_options`.`product_id` as `laravel_through_key` from `variant_sku_values` inner join `variant_sku_product_options` on `variant_sku_product_options`.`id` = `variant_sku_values`.`product_option_id` where `variant_sku_product_options`.`product_id` = 61520 and `option_id` = 1
1.16ms
-----------------------------------------
arya-lion
select `variant_sku_values`.*, `variant_sku_product_options`.`product_id` as `laravel_through_key` from `variant_sku_values` inner join `variant_sku_product_options` on `variant_sku_product_options`.`id` = `variant_sku_values`.`product_option_id` where `variant_sku_product_options`.`product_id` = 81066 and `option_id` = 1
1.09ms
-----------------------------------------
arya-lion
select `variant_sku_values`.*, `variant_sku_product_options`.`product_id` as `laravel_through_key` from `variant_sku_values` inner join `variant_sku_product_options` on `variant_sku_product_options`.`id` = `variant_sku_values`.`product_option_id` where `variant_sku_product_options`.`product_id` = 81069 and `option_id` = 1
1.07ms
------------------------------------------
arya-lion
select `variant_sku_values`.*, `variant_sku_product_options`.`product_id` as `laravel_through_key` from `variant_sku_values` inner join `variant_sku_product_options` on `variant_sku_product_options`.`id` = `variant_sku_values`.`product_option_id` where `variant_sku_product_options`.`product_id` = 93662 and `option_id` = 1
1ms
-----------------------------------------
arya-lion
select `variant_sku_values`.*, `variant_sku_product_options`.`product_id` as `laravel_through_key` from `variant_sku_values` inner join `variant_sku_product_options` on `variant_sku_product_options`.`id` = `variant_sku_values`.`product_option_id` where `variant_sku_product_options`.`product_id` = 93724 and `option_id` = 1

我尝试预加载“values”关系,但这并不起作用。

英文:

my product has relation called values which is like this:

public function values()
{
return $this->hasManyThrough(VariantValue::class, VariantProductOption::class, 'product_id', 'product_option_id');
}

everything works fine but when i have a multiple products in a single pages n+1 problem happens:

select `variant_sku_values`.*, `variant_sku_product_options`.`product_id` as `laravel_through_key` from `variant_sku_values` inner join `variant_sku_product_options` on `variant_sku_product_options`.`id` = `variant_sku_values`.`product_option_id` where `variant_sku_product_options`.`product_id` = 61520 and `option_id` = 1
1.16ms
-----------------------------------------
arya-lion
select `variant_sku_values`.*, `variant_sku_product_options`.`product_id` as `laravel_through_key` from `variant_sku_values` inner join `variant_sku_product_options` on `variant_sku_product_options`.`id` = `variant_sku_values`.`product_option_id` where `variant_sku_product_options`.`product_id` = 81066 and `option_id` = 1
1.09ms
-----------------------------------------
arya-lion
select `variant_sku_values`.*, `variant_sku_product_options`.`product_id` as `laravel_through_key` from `variant_sku_values` inner join `variant_sku_product_options` on `variant_sku_product_options`.`id` = `variant_sku_values`.`product_option_id` where `variant_sku_product_options`.`product_id` = 81069 and `option_id` = 1
1.07ms
------------------------------------------
arya-lion
select `variant_sku_values`.*, `variant_sku_product_options`.`product_id` as `laravel_through_key` from `variant_sku_values` inner join `variant_sku_product_options` on `variant_sku_product_options`.`id` = `variant_sku_values`.`product_option_id` where `variant_sku_product_options`.`product_id` = 93662 and `option_id` = 1
1ms
-----------------------------------------
arya-lion
select `variant_sku_values`.*, `variant_sku_product_options`.`product_id` as `laravel_through_key` from `variant_sku_values` inner join `variant_sku_product_options` on `variant_sku_product_options`.`id` = `variant_sku_values`.`product_option_id` where `variant_sku_product_options`.`product_id` = 93724 and `option_id` = 1

i tried to eager load the values relation but this doesnt work.

答案1

得分: 1

通过 @Coola 的帮助,我以如下方式使用贪婪加载关系找到了解决方案:

Product::query()->with([
    'values' => ['value' => ['option']],
])
英文:

with help of @Coola i somehow find the solution by eagerloading the relation
like below:

Product::query()->with([
'values' => ['value' => ['option']],
])
</details>
# 答案2
**得分**: 0
你可以像这样使用 eager loading 的 `with` 方法:
```php
public function values()
{
return $this->hasManyThrough(VariantValue::class, VariantProductOption::class, 'product_id', 'product_option_id')
->with('variantProductOption');
}
英文:

You can use with for eager loading like this-

public function values()
{
return $this-&gt;hasManyThrough(VariantValue::class, VariantProductOption::class, &#39;product_id&#39;, &#39;product_option_id&#39;)
-&gt;with(&#39;variantProductOption&#39;);
}

huangapple
  • 本文由 发表于 2023年4月17日 17:48:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/76033806.html
匿名

发表评论

匿名网友

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

确定