Django原生SQL查询：按order_id嵌套order_detail_data到order_data

Got it, let's fix this nested structure issue for your Order_ListAPIView. Here's a straightforward approach to map order details to their corresponding orders using Python's built-in data structures, since you're working with raw SQL instead of Django ORM:

Modified Code #

from collections import defaultdict
from rest_framework import status
from rest_framework.views import APIView
# Ensure your connection() function is imported correctly

class Order_ListAPIView(APIView):
    def get(self, request, format=None):
        if request.method == 'GET':
            cur, conn = connection()
            try:
                with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
                    # Fetch orders data
                    order_query = ''' SELECT * FROM orders'''
                    cursor.execute(order_query)
                    order_data = list(cursor.fetchall())

                    # Fetch order details data
                    order_detail_query = ''' SELECT * FROM order_details'''
                    cursor.execute(order_detail_query)
                    order_detail_data = list(cursor.fetchall())

                # Step 1: Create a lookup map for order details (O(n) time complexity)
                order_detail_map = defaultdict(list)
                for detail in order_detail_data:
                    order_id = detail['order_id']
                    order_detail_map[order_id].append(detail)

                # Step 2: Nest details into their corresponding order objects
                for order in order_data:
                    order_id = order['order_id']
                    # Return empty list if no details exist for the order (keeps structure consistent)
                    order['order_detail_data'] = order_detail_map.get(order_id, [])

                # Step 3: Build the final response structure
                totalData = [{"order_data": order_data}]
                return Response({"totalData": totalData,}, status=status.HTTP_200_OK)
            finally:
                # Ensure database connection is closed to prevent leaks
                conn.close()
        else:
            return Response(status=status.HTTP_400_BAD_REQUEST)

Key Changes Explained #

• Order Detail Lookup Map: We use defaultdict(list) to create a dictionary where each key is an order_id, and the value is a list of all related order details. This makes looking up details for each order fast (O(1) per lookup).
• Nesting Logic: We loop through every order in order_data, pull its matching details from the map, and add them directly to the order object as a new order_detail_data field.
• Connection Cleanup: Added a finally block to guarantee the database connection gets closed, even if an error occurs during data processing.
• Response Structure: Updated totalData to only include the nested order_data (since we no longer need to return separate, unlinked order_detail_data).

Edge Case Handling #

• If an order has no associated details, order_detail_map.get(order_id, []) will add an empty list for order_detail_data, ensuring your response structure stays consistent across all orders.

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