Count the number of data for the main parent

[CrazyTapok-bit]

We have the following plates:

Categories

id | name | parent_id |
---+------+-----------+

Products

id | name | category_id |
---+------+-------------+

category_id - contains a link to the categories table but can contain any id from this tree.

The question is, how to count the number of products in all parent categories? Of course, taking into account that category_id will most likely point to some child category, i.e. have an indirect relationship.

Thanks

[staudenmeir]

Hi @CrazyTapok-bit,
You can achieve this with a deep relationship:
https://github.com/staudenmeir/laravel-adjacency-list?tab=readme-ov-file#deep-relationship-concatenation

class Category extends Model
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

    public function ancestorProducts(): \Staudenmeir\EloquentHasManyDeep\HasManyDeep
    {
        return $this->hasManyDeepFromRelations(
            $this->ancestors(),
            (new static)->products()
        );
    }
    
    public function products()
    {
        return $this->hasMany(Product::class);
    }
}

class Product extends Model
{
    public function category()
    {
        return $this->belongsTo(Category::class);
    }
}

$count = Product::find($id)->category->ancestorProducts()->count();

[CrazyTapok-bit]

Thanks @staudenmeir, it works when I need to count the quantity of a specific product, although I changed ancestors to ancestorsAndSelf to get a more correct value.

However, I'm trying to do the opposite, that is, count the quantity of products for all their parent categories, that is, for those whose parent_id === null, while also including the products of the subcategories in the quantity. That is, if I have two products from the subcategories IPhone and Samsung, then for the Phone category I need to count the quantity of these products

Tell me how I can achieve this using your packages?

I like them, but I don't quite understand the principle of their work yet

[CrazyTapok-bit]

Oh, I managed to achieve what I wanted with the help of descendants)

I'm posting the solution, maybe it will be useful to someone

use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;

use Staudenmeir\EloquentHasManyDeep\HasManyDeep;
use Staudenmeir\EloquentHasManyDeep\HasRelationships;
use Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

class Category extends Model
{
    use HasRelationships;
    use HasRecursiveRelationships;

    public function descendantProducts(): HasManyDeep
    {
        return $this->hasManyDeepFromRelations(
            $this->descendants(),
            (new static)->products()
        );
    }
    
    public function products(): HasMany
    {
        return $this->hasMany(Product::class);
    }
}

class Product extends Model
{
    public function category(): BelongsTo
    {
        return $this->belongsTo(Category::class);
    }
}

dd(Category::query()->whereNull('parent_id')->withCount('descendantProducts')->get()->toArray())
// or
dd(Category::isRoot()->withCount('descendantProducts')->get()->toArray())