点击切换按钮后执行Django/SQL双表关联查询的实现方案

Got it, let's walk through how to build this feature from backend to frontend. Here's a step-by-step solution tailored to your Django models and requirements:

First, you'll need a Django view to handle the AJAX request when the toggle button is clicked. This view will perform the NSN-based join between your two tables and return the data (we'll cover two options: frontend-handled repetition for better performance, or backend-handled repetition with raw SQL).

Option A: ORM-Based Query (Frontend Handles Repetition) #

This approach keeps backend data transfer lean by returning only unique products plus the line item count, letting the frontend duplicate rows as needed.

# views.py
from django.http import JsonResponse
from .models import (
    DibbsSpiderDibbsMatchedProductFieldsDuplicate as MatchedProduct,
    DibbsSpiderSolicitation as Solicitation
)

def fetch_matched_products(request):
    # Get the target NSN from the frontend request (passed via button data attribute)
    target_nsn = request.GET.get('nsn')
    if not target_nsn:
        return JsonResponse({'error': 'NSN parameter is missing'}, status=400)
    
    # Fetch the line item count from the Solicitation table
    solicitation = Solicitation.objects.filter(nsn=target_nsn).first()
    line_item_count = solicitation.line_items if solicitation else 0
    
    # Get all unique matched products for the NSN
    matched_products = list(MatchedProduct.objects.filter(nsn=target_nsn).values())
    
    return JsonResponse({
        'products': matched_products,
        'line_item_count': line_item_count
    })

Option B: SQL-Based Query (Backend Handles Repetition) #

If you prefer to handle row duplication on the backend (e.g., for complex logic), use a raw SQL query. This example uses PostgreSQL's generate_series to duplicate rows based on the line_items value:

# views.py
def fetch_matched_products_sql(request):
    target_nsn = request.GET.get('nsn')
    if not target_nsn:
        return JsonResponse({'error': 'NSN parameter is missing'}, status=400)
    
    # Raw SQL to join tables and repeat rows by line item count
    raw_sql = """
        SELECT mp.*
        FROM dibbs_spider_dibbs_matched_product_fields_duplicate mp
        JOIN dibbs_spider_solicitation s ON mp.nsn = s.nsn
        JOIN generate_series(1, s.line_items) AS gs
        WHERE mp.nsn = %s
    """
    
    # Execute query and convert results to a dictionary list
    results = MatchedProduct.objects.raw(raw_sql, [target_nsn])
    product_list = []
    for product in results:
        product_dict = {field.name: getattr(product, field.name) for field in MatchedProduct._meta.fields}
        product_list.append(product_dict)
    
    return JsonResponse({'data': product_list})

Add URL Route #

Map your view to a URL in urls.py:

# urls.py
from django.urls import path
from . import views

urlpatterns = [
    # ... your existing URLs
    path('fetch-matched-products/', views.fetch_matched_products, name='fetch_matched_products'),
    # Uncomment if using the SQL option:
    # path('fetch-matched-products-sql/', views.fetch_matched_products_sql, name='fetch_matched_products_sql'),
]

Update your toggle button to include the NSN (populate this from your template context) and add JavaScript to handle clicks, AJAX requests, and result rendering.

<!-- Update your toggle button to include the NSN as a data attribute -->
<td>
    <button type="button" class="btn btn-default toggle-products-btn" data-nsn="{{ your_object.nsn }}">
        Toggle Product Details
    </button>
</td>

<!-- Container to display the query results -->
<div id="products-results" class="mt-3"></div>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
$(document).ready(function() {
    $('.toggle-products-btn').click(function() {
        const button = $(this);
        const targetNSN = button.data('nsn');
        const resultsContainer = $('#products-results');
        
        // Send AJAX request to the backend
        $.ajax({
            url: '/fetch-matched-products/', // Match your URL route
            method: 'GET',
            data: {nsn: targetNSN},
            success: function(response) {
                if (response.error) {
                    resultsContainer.html(`<div class="alert alert-danger">${response.error}</div>`);
                    return;
                }
                
                if (response.products.length === 0) {
                    resultsContainer.html('<div class="alert alert-info">No matched products found.</div>');
                    return;
                }
                
                // Build the results table
                let tableHTML = '<table class="table table-striped"><thead><tr>';
                // Create headers from product keys
                const productKeys = Object.keys(response.products[0]);
                productKeys.forEach(key => {
                    tableHTML += `<th>${key.replace('_', ' ').toUpperCase()}</th>`;
                });
                tableHTML += '</tr></thead><tbody>';
                
                // Repeat each product row based on line item count
                response.products.forEach(product => {
                    for (let i = 0; i < response.line_item_count; i++) {
                        tableHTML += '<tr>';
                        productKeys.forEach(key => {
                            tableHTML += `<td>${product[key] || ''}</td>`;
                        });
                        tableHTML += '</tr>';
                    }
                });
                
                tableHTML += '</tbody></table>';
                resultsContainer.html(tableHTML);
                
                // Toggle button state for better UX
                button.toggleClass('btn-default btn-primary');
            },
            error: function() {
                resultsContainer.html('<div class="alert alert-danger">Failed to load product data.</div>');
            }
        });
    });
});
</script>

• NSN Population: Ensure the data-nsn attribute on the button uses the actual NSN value from your template context (e.g., {{ solicitation.nsn }} if looping through Solicitation objects).
• Database Compatibility: The generate_series SQL function works for PostgreSQL. For MySQL, you'll need an alternative like a numbers table or stored procedure to duplicate rows.
• Performance: Frontend repetition is generally better for large line item counts, as it reduces the amount of data transferred between backend and frontend.

内容的提问来源于stack exchange，提问作者Rikky Bhai