Laravel技术实现：仅显示包含有效商品的分类（含子分类）

Got it, let's tackle this problem. The core requirement is to hide any category (including all its subcategories) that doesn't have active products (status=1) and isn't part of a hierarchy that leads to an active product.

First, let's break down what we need to do:

• Identify all categories directly linked to active products
• Include all parent categories of those product-linked categories (since parents should still show even if they don't have their own products)
• Exclude any category that isn't in this filtered set, along with its subcategories

Step 1: Collect All Required Category IDs #

We need to gather two sets of categories:

1. Categories that have active products
2. All parent categories of those product-linked categories

Option 1: Using Eloquent Recursive Relationships (Works with All Databases) #

First, add parent relationships to your Category model to traverse upward:

// Category.php
public function parent()
{
    return $this->belongsTo(Category::class, 'parent_id');
}

public function allParents()
{
    return $this->parent()->with('allParents');
}

Then, create a method to collect all required category IDs:

private function getRequiredCategoryIds()
{
    // Get all categories linked to active products
    $activeProductCategoryIds = Product::where('status', 1)
        ->distinct()
        ->pluck('p_cat_id')
        ->toArray();

    $requiredIds = $activeProductCategoryIds;

    // Recursively add all parent categories of these IDs
    $productCategories = Category::whereIn('id', $activeProductCategoryIds)->get();
    foreach ($productCategories as $category) {
        $this->collectParentIds($category->allParents, $requiredIds);
    }

    // Remove duplicates and return
    return array_unique($requiredIds);
}

// Helper function to recursively collect parent IDs
private function collectParentIds($parents, &$ids)
{
    if (empty($parents)) return;
    foreach ($parents as $parent) {
        $ids[] = $parent->id;
        $this->collectParentIds($parent->allParents, $ids);
    }
}

Option 2: Using Database CTE Recursive Query (More Efficient for Large Datasets) #

If your database supports Common Table Expressions (like MySQL 8+, PostgreSQL), you can fetch all required categories in one go:

private function getRequiredCategoryIds()
{
    $results = DB::select("
        WITH RECURSIVE category_hierarchy AS (
            -- Start with categories linked to active products
            SELECT id, parent_id
            FROM categories
            WHERE id IN (SELECT DISTINCT p_cat_id FROM products WHERE status = 1)
            UNION ALL
            -- Recursively add parent categories
            SELECT c.id, c.parent_id
            FROM categories c
            JOIN category_hierarchy ch ON c.id = ch.parent_id
        )
        SELECT DISTINCT id FROM category_hierarchy;
    ");

    // Convert results to an array of IDs
    return array_column($results, 'id');
}

Step 2: Modify the Category Query to Filter Only Required Categories #

Update your getCategory method to fetch only the filtered categories, and load their valid subcategories:

First, add a constrained subcategory relationship and scope to your Category model:

// Category.php
public function activeFilteredSubCategories($allowedIds)
{
    return $this->hasMany(Category::class, 'parent_id')
        ->where('status', 1)
        ->whereIn('id', $allowedIds)
        ->with(['activeFilteredSubCategories' => fn($q) => $q->withAllowedIds($allowedIds)]);
}

// Reusable scope for allowed IDs constraint
public function scopeWithAllowedIds($query, $allowedIds)
{
    return $query->whereIn('id', $allowedIds);
}

Then update your getCategory function:

function getCategory() {
    $requiredIds = $this->getRequiredCategoryIds();

    return Category::where([
        ['parent_id', '=', null],
        ['status', '=', 1],
    ])
    ->whereIn('id', $requiredIds) // Only keep valid top-level categories
    ->orderBy("name", "ASC")
    ->with(['activeFilteredSubCategories' => fn($q) => $q->withAllowedIds($requiredIds)])
    ->get();
}

Verify the Result #

After implementing this, your output will match the desired structure:

Electronics - media - speaker - Computers
Clothing - Mens - Shirt

All categories without active products (and their hierarchies) like DVDs, Women, and Craft will be excluded.

内容的提问来源于stack exchange，提问作者Ram Chander