基于多模块依赖状态的页面启用/禁用管控方案问询

Great question—this is a super common scenario when dealing with dependent system states, and there are a couple of robust approaches to implement this, depending on whether you prefer handling the heavy lifting in the database or your application code. Let’s dive in:

If your database supports recursive queries (most modern databases like PostgreSQL, MySQL 8+, SQL Server do), you can handle the entire dependency check directly in SQL, avoiding unnecessary data transfer to your application layer.

First, let’s confirm the assumed table structure (matching your description):

• pages: id, status (1 = enabled, 0 = disabled), [other page fields]
• modules: id, status (1 = enabled, 0 = disabled), [other module fields]
• page_module_deps: page_id, module_id (stores direct page-to-module dependencies)
• module_module_deps: parent_module_id, child_module_id (stores module-to-module dependencies, e.g., if Module A depends on Module B, this table has a row linking A to B)

Here’s the recursive query to fetch only valid enabled pages:

WITH RECURSIVE all_page_deps AS (
    -- Start with direct module dependencies for each page
    SELECT pmd.page_id, pmd.module_id
    FROM page_module_deps pmd
    UNION ALL
    -- Recursively add all indirect module dependencies
    SELECT apd.page_id, mmd.child_module_id AS module_id
    FROM all_page_deps apd
    JOIN module_module_deps mmd ON apd.module_id = mmd.parent_module_id
)
SELECT p.*
FROM pages p
WHERE p.status = 1 -- Exclude pages that are directly disabled
AND NOT EXISTS (
    -- Ensure none of the page's direct/indirect dependencies are disabled
    SELECT 1
    FROM all_page_deps apd
    JOIN modules m ON apd.module_id = m.id
    WHERE apd.page_id = p.id
    AND m.status = 0
);

Key Notes: #

• The recursive CTE (all_page_deps) builds a complete list of every module a page depends on, including indirect dependencies (e.g., Page → Module A → Module B).
• The main query filters out pages that are disabled themselves, plus any page that has any disabled module in its dependency chain.
• If a page has no dependencies at all, it will be returned as long as its own status is 1.

If you’re working with a database that doesn’t support recursive queries, or if you need to add custom business logic alongside dependency checks, you can handle the validation in your application code.

Here’s a simplified example in Python (adjust to your language/framework):

def get_valid_enabled_pages():
    # First, fetch all pages that are directly enabled
    enabled_pages = Page.query.filter_by(status=1).all()
    valid_pages = []

    for page in enabled_pages:
        # Get all direct modules the page depends on
        direct_modules = page.dependent_modules
        # Recursively fetch every indirect module dependency
        all_dependencies = _get_all_module_dependencies(direct_modules)
        
        # Check if all dependencies are enabled
        all_modules_enabled = all(module.status == 1 for module in all_dependencies)
        if all_modules_enabled:
            valid_pages.append(page)
    
    return valid_pages

def _get_all_module_dependencies(modules):
    # Use a set to avoid duplicate modules (and handle circular dependencies)
    all_deps = set(modules)
    for module in modules:
        child_dependencies = module.dependent_modules
        if child_dependencies:
            all_deps.update(_get_all_module_dependencies(child_dependencies))
    return all_deps

Key Notes: #

• The helper function _get_all_module_dependencies uses a set to prevent infinite loops if there’s a circular dependency (e.g., Module A depends on B, B depends on A).
• You can easily extend this to add custom checks (e.g., exclude pages based on user permissions alongside dependency status).

• Cache Valid Page States: Since module and page statuses don’t change constantly, cache the list of valid enabled pages (e.g., with Redis). Invalidate the cache whenever a page’s status changes, or any module in a page’s dependency chain is updated.
• Precompute Enabled Pages: For systems where real-time accuracy isn’t critical, run a scheduled job (e.g., every minute) to precompute valid pages and store them in a dedicated enabled_pages table. Query this table directly for lightning-fast results.
• Index Critical Fields: Add indexes to pages.status, modules.status, and all foreign key fields in your dependency tables to speed up joins and recursive queries.

内容的提问来源于stack exchange，提问作者Fahad Bhatti