How to count the number of products in categories, including parent ones?

Hello! I really need your help and advice. I have a category table, a product table, and a many-to-many relationship between them. The product belongs to the final category only (there may be several categories, but not parent ones). Example: There is a product: "Polo adidas", category "polo". There are categories:

Cloth
	T-shirts and polos
		polo
			long sleeve polo
			short sleeve polo
		T-shirts
			long sleeve t-shirts
			short sleeve t-shirts

With the display in the catalog, I figured out how to display products belonging to the parent category and all child categories in the parent category. But now there is a question with the menu display. From the example above, I need to display in the menu only those categories that have products (including parent ones). That is, the “T-shirts” category should not be displayed, since there are no products in it. To do this, it seemed to me correct to run the function of recalculating products in categories when adding and deleting products.

This way I get a list of related categories for products. I don’t understand what to do with this next)))

$idCategories = Product::withOnly('categories')->get()->pluck('categories');

Shivamyadav

2 years ago

Level 20

try this

use App\Models\Category;
use Illuminate\Database\Eloquent\Builder;

// Get all categories with the count of products
$categoriesWithProductCount = Category::withCount(['products' => function (Builder $query) {
    $query->selectRaw('distinct product_id'); // Ensure distinct products are counted
}])->get();

// Filter out categories with zero product count and their parents
$categoriesWithProductCount = $categoriesWithProductCount->filter(function ($category) {
    return $category->products_count > 0 || $category->parent_id == null;
});

// You can now use $categoriesWithProductCount to display the menu

Rediska

2 years ago

Level 1

@Shivamyadav I get this error

TypeError
Argument 1 passed to App\Http\Controllers\Admin\CategoryController::App\Http\Controllers\Admin\{closure}() must be an instance of PhpParser\Builder, instance of Illuminate\Database\Eloquent\Builder given, called in C:\OpenServer\domains\store\vendor\laravel\framework\src\Illuminate\Database\Eloquent\Builder.php on line 1199

RayC

2 years ago

Level 10

You could do something like:

$category->products()->count();

Assuming you have the relationships setup between the Category and Product models.

Rediska

2 years ago

Level 1

@RayC

In the “category” model I have this:

public function products()
    {
        return $this->belongsToMany(Product::class);
    }

Please or to participate in this conversation.