NESTED EAGER LOADING WITH CONSTRAINTS: Getting products all products from the parent category. Category are defined under three different table

Question

I have defined categories in three level. Primary, Secondary, and Product Category.

I have Two categories under primary_categories table which is Women's Fashion and Men's Fashion.

Under secondary_categories I have categories like traditional wear(for women), Footwear(for women), western(for women), western wear(for men), footwear(for men), pants(for men) and so on.

And finally under product_categories I have categories like pants, t-shirts, kurta, sandals and so on.

While saving category for the product, I have used products table in the column category_id.

Now I want to get products that comes under Women's fashion. How can I do that?

Primary Category

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

Secondary Category

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();
    });
}

Final Category

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();
    });
}

While adding product, category_id of product_categories goes inside the product table.

Answer 1

PrimaryCategory Model

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

SecondaryCategory Model

public function primaryCategory(){
    return $this->belongsTo(App\PrimaryCategory::class, 'primary_category_id', 'id');
}

public function productCategories(){
   return $this->hasMany(App\ProductCategory::class, 'secondary_category_id', 'id');
}

ProductCategory Model

public function secondaryCategory(){
   return $this->belongsTo(App\SecondaryCategory::class, 'secondary_category_id', 'id');
}

public function products(){
    return $this->hasMany(App\Product::class, 'category_id', 'id');
}

Product Model

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

Controller

To get all products with a given PrimaryCategory

Option 1: DB query, several collection methods.

$name = "Women's Fashion";

$pc = PrimaryCategory::with(
        'secondaryCategories.productCategories.products')
        ->where('name', $name)->first();

$products = $pc->secondaryCategories->pluck('productCategories')
               ->collapse()->pluck('products')->collapse();

 

OR

[NESTED EAGER LOADING WITH CONSTRAINTS]

Option 2: DB query

$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();