嵌套EAGER加载与约束:获取来自父类别的所有产品.类别定义在三个不同的表下

Question

我在三个级别中定义了类别。初级、次级和产品类别。

我在primary_categories表下有两个类别，即女装和男装。

在secondary_categories下，我有一些类别，如传统服装(女性(、鞋类(女性(，西服(女性(和西服(男性(，鞋类(男性(和裤子(男性(等等。

最后，在product_categories下，我有裤子、t恤、kurta、凉鞋等类别。

在为产品保存类别时，我使用了category_id列中的products表。

现在我想买属于女性时尚的产品。我该怎么做？

主要类别

public function up()
{
Schema::create('primary_categories', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name');
$table->timestamps();
});
}

次要类别

public function up()
{
Schema::create('secondary_categories', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name');
$table->string('slug');
$table->unsignedBigInteger('primary_category_id')->nullable();
$table->foreign('primary_category_id')->references('id')->on('primary_categories')->onDelete('SET NULL');
$table->timestamps();
});
}

最终类别

public function up()
{
Schema::create('product_categories', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name');
$table->string('slug')->unique();
$table->unsignedBigInteger('secondary_category_id')->nullable(); 
$table->foreign('secondary_category_id')->references('id')->on('secondary_categories')->onDelete('SET NULL');
$table->timestamps();
});
}

添加产品时，product_categories的category_id进入产品表。

Answer 1

主要类别模型

public function secondaryCategories(){
return $this->hasMany(AppSecondaryCategory::class, 'primary_category_id', 'id');
}

次要类别模型

public function primaryCategory(){
return $this->belongsTo(AppPrimaryCategory::class, 'primary_category_id', 'id');
}
public function productCategories(){
return $this->hasMany(AppProductCategory::class, 'secondary_category_id', 'id');
}

产品类别型号

public function secondaryCategory(){
return $this->belongsTo(AppSecondaryCategory::class, 'secondary_category_id', 'id');
}
public function products(){
return $this->hasMany(AppProduct::class, 'category_id', 'id');
}

产品型号

public function productCategory(){
return $this->belongsTo(AppProductCategory, 'category_id', 'id');
}

控制器

获取具有给定PrimaryCategory 的所有产品

选项1:DB查询，几种收集方法。

$name = "Women's Fashion";
$pc = PrimaryCategory::with(
'secondaryCategories.productCategories.products')
->where('name', $name)->first();
$products = $pc->secondaryCategories->pluck('productCategories')
->collapse()->pluck('products')->collapse();

或

[带约束的嵌套热切加载]

选项2:DB查询

$name = "Women's Fashion";
$products = Product::whereHas('productCategory', function($query) 
use($name) {
$query->whereHas('secondaryCategory', function($query) 
use($name)  { 
$query->whereHas('primaryCategory', function($query) 
use($name){
$query->where('name', $name);
});
});
})
->with([
'productCategory' => function($query) use($name) {
$query->whereHas('secondaryCategory', function($query) use($name)
{ 
$query->whereHas('primaryCategory', function($query) 
use($name){
$query->where('name', $name);
});
});
},
'productCategory.secondaryCategory'=> function($query) use($name)
{ 
$query->whereHas('primaryCategory', function($query) 
use($name){
$query->where('name', $name);
});
},
'productCategory.secondaryCategory.primaryCategory' =>                   
function($query) use($name) {
$query->where('name', $name);
}])->get();