C# MVC中实现类别子类别检测与展示功能的技术咨询

Hey there! Adding subcategories to your existing setup is totally doable—let's break this down step by step so you can implement it smoothly.

First, you’ll need to modify your Category table to support hierarchical relationships. Add a new nullable field called ParentCategoryId that references the CategoryId of the parent category:

• CategoryId (PK, int, auto-increment)
• CategoryName (varchar, required)
• ParentCategoryId (int, nullable, foreign key to Category.CategoryId)

This setup lets you:

• Mark top-level categories with ParentCategoryId = NULL
• Link subcategories to their parent by setting ParentCategoryId to the parent’s CategoryId

To show all posts under a category and all its subcategories, you’ll need a recursive query to fetch all descendant category IDs. Here’s how to do it with SQL Common Table Expressions (CTE), supported in MySQL 8+, SQL Server, PostgreSQL, etc.:

WITH RecursiveCategories AS (
    -- Start with the target category
    SELECT CategoryId
    FROM Category
    WHERE CategoryId = @SelectedCategoryId
    UNION ALL
    -- Recursively fetch all child categories
    SELECT c.CategoryId
    FROM Category c
    JOIN RecursiveCategories rc ON c.ParentCategoryId = rc.CategoryId
)
-- Get all posts linked to any of the fetched category IDs
SELECT p.PostId, p.PostName, p.PostBody, p.CategoryId
FROM Post p
JOIN RecursiveCategories rc ON p.CategoryId = rc.CategoryId;

If you’re using an ORM (like Entity Framework, Django ORM), you can write a recursive function to collect all descendant category IDs. For example, in C# with EF Core:

// Recursively get all category IDs including the parent and children
public List<int> GetAllDescendantCategoryIds(int parentId)
{
    var categoryIds = new List<int> { parentId };
    var childCategories = _dbContext.Categories
        .Where(c => c.ParentCategoryId == parentId)
        .ToList();

    foreach (var child in childCategories)
    {
        categoryIds.AddRange(GetAllDescendantCategoryIds(child.CategoryId));
    }

    return categoryIds;
}

// Fetch posts using the collected IDs
public List<Post> GetPostsForCategoryAndSubcategories(int categoryId)
{
    var allCategoryIds = GetAllDescendantCategoryIds(categoryId);
    return _dbContext.Posts
        .Where(p => allCategoryIds.Contains(p.CategoryId))
        .ToList();
}

Make the category hierarchy clear to users with a nested UI:

• Indent subcategories using CSS (e.g., margin-left: 20px)
• Add optional expand/collapse functionality if you have deep nesting

Example HTML/CSS snippet:

<ul class="category-tree">
    <li>
        <a href="/category/1">Technology</a>
        <ul class="subcategories">
            <li><a href="/category/2">Web Development</a></li>
            <li><a href="/category/3">Mobile Development</a></li>
        </ul>
    </li>
    <li><a href="/category/4">Lifestyle</a></li>
</ul>

.subcategories {
    margin-left: 20px;
    list-style-type: circle;
}

• Top-level categories: Ensure queries correctly handle ParentCategoryId = NULL (show their posts plus all descendants)
• Category management: When adding/editing categories, add a dropdown to select a parent category (with an option for "Top Level")
• Deletion: Decide if deleting a parent category should move its subcategories to top-level, or delete them entirely—add logic to match your needs

内容的提问来源于stack exchange，提问作者Web R