Laravel Eloquent: SUM category products using SQL query (Morph to Many)

Question

Brief

Sum parent category subcategories related products and add a custom column (e.g products_count) to the parent category.

Migrations

Categories

Schema::create('categories', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->unsignedBigInteger('category_id')->nullable();
    $table->foreign('category_id')
        ->references('id')
        ->on('categories')
        ->onDelete('cascade');
    $table->timestamps();
});

Products

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('title');
    $table->string('price');
    $table->timestamps();
});

Categorizables

Schema::create('categorizables', function (Blueprint $table) {
    $table->id();
    $table->integer('category_id');
    $table->uuidMorphs('categorizable');
    $table->timestamps();
});

Models

Category

public function subcategories()
{
    return $this->hasMany(Category::class);
}

public function parent()
{
    return $this->belongsTo(Category::class, 'category_id', 'id');
}

public function products()
{
    return $this->morphedByMany(Product::class, 'categorizable');
}

Product

public function category()
{
    return $this->morphToMany(Category::class, 'categorizable');
}

What I tried?

Category Model

public function getCountProductsAttribute()
{
    if(!$this->category_id) {
        $sum = 0;

        collect($this->subcategories->loadCount('products'))->each(function($category) use(&$sum) {
            $sum+=$category->products_count;
        });

        return $sum;
    }
}

This will be count parent category subcategories products and return the sum to the category which is the parent category.

How to sum parent category subcategories products on the query process with eloquent without using mutators as me?

Can you try to explain "Each products can be attached only to child categories. How I can get sum of parent category products by there child category products?" in more detail? I am a little confused. — Kurt Friars
My point is that each product can only belong to child categories. And I need to count the number of products of a certain parent category using its child categories @KurtFriars — Andreas Hunter
I am still unclear what "each product can only belong to child categories" and "I need to count the number of products of a certain parent category using its child categories" means. Can you please show us the tables involved and the relevant keys/columns on those tables please. :) — Kurt Friars

Kurt Friars Kurt Friars · Accepted Answer · 2020-07-18T09:37:09

Ok, based on the requirement that subcategories can have subcategories we need a recursive solution. You could do as follows:

public function getCountProductsAttribute()
{
    if (! $this->relationLoaded('subcategories.products') {
        $this->load('subcategories.products');
    }
    
    if ($this->subcategories->isEmpty()) {
        return $this->products->count();
    }

    return $this->subcategories->reduce(function ($sum, $sub) {
        $sum += $sub->countProducts;
        return $sum;
    }, $this->products->count());
}

Now to get the "products_count" added on the result of a query, we can append it at runtime like so:

    $category = Category::find($id)->append('countProducts');

The append will of course only be present when the results are serialized (ie. toArray or toJson).

Laravel Eloquent: SUM category products using SQL query (Morph to Many)

1 Answers