python - Django: how to get order_detail_data as per order_id

Question

class Order_ListAPIView(APIView):
    def get(self,request,format=None):
        totalData=[]
        if request.method == 'GET':
            cur,conn = connection()
            order_query = ''' SELECT * FROM orders'''
            order_detail_query = ''' SELECT * FROM order_details'''

            with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
                cursor.execute(order_query)
                order_result = cursor.fetchall()
                order_data = list(order_result)
                # print(order_data)


                cursor.execute(order_detail_query)
                order_detail_result = cursor.fetchall()
                order_detail_data = list(order_detail_result)
                # print(order_detail_data)

            totalData.append({"order_data":order_data, "order_detail_data":order_detail_data})
            return Response({"totalData":totalData,},status=status.HTTP_200_OK)
        else:
            return Response(status=status.HTTP_400_BAD_REQUEST)

output:

{
    "totalData": [
        {
            "order_data": [
                {
                    "order_id": 1,
                    "user_id": 5,
                    "billing_shipping_id": 1,
                    "payment_method_id": 2,
                    "delivery_id": 2,
                    "txnid": "",
                    "order_no": "0822-1582695084-0006",
                    "delivery_amount": 0.0,
                    "discount_amount": 570.0,
                    "order_total": 2280.0,
                    "payment_status": "Unpaid",
                    "created_datetime": "2020-02-26T11:01:24",
                    "updated_datetime": "2020-02-26T05:31:24",
                    "status": "Active",
                    "mihpayid": "",
                    "payuMoneyId": ""
                },    
            ],
           "order_detail_data": [
                {
                    "order_detail_id": 1,
                    "order_id": 1,
                    "user_id": 5,
                    "product_id": 202,
                    "product_size_id": 867,
                    "size_id": 1,
                    "qty": 1,
                    "product_price": 2850.0,
                    "order_item_status": "Placed",
                    "last_status_datatime": "2020-02-26T11:01:24",
                    "feedback": "",
                    "created_datetime": "2020-02-26T11:01:24",
                    "updated_datetime": "2020-02-26T05:31:24",
                    "status": "Active"
                },
                ],
                },                                                                           
                ],                                                                              
                }

what i want:

i want order_detail_data inside order_data as per order_id like this:

{
    "totalData": [
        {
            "order_data": [
                {
                    "order_id": 1,
                    "user_id": 5,
                    "billing_shipping_id": 1,
                    "payment_method_id": 2,
                    "delivery_id": 2,
                    "txnid": "",
                    "order_no": "0822-1582695084-0006",
                    "delivery_amount": 0.0,
                    "discount_amount": 570.0,
                    "order_total": 2280.0,
                    "payment_status": "Unpaid",
                    "created_datetime": "2020-02-26T11:01:24",
                    "updated_datetime": "2020-02-26T05:31:24",
                    "status": "Active",
                    "mihpayid": "",
                    "payuMoneyId": "",                                                          

                    "order_detail_data": [{                                              
                    "order_detail_id": 1,
                    "order_id": 1,
                    "user_id": 5,
                    "product_id": 202,
                    "product_size_id": 867,
                    "size_id": 1,
                    "qty": 1,
                    "product_price": 2850.0,
                    "order_item_status": "Placed",
                    "last_status_datatime": "2020-02-26T11:01:24",
                    "feedback": "",
                    "created_datetime": "2020-02-26T11:01:24",
                    "updated_datetime": "2020-02-26T05:31:24",
                    "status": "Active"                                                         
                      }],
                },
                   {                                                                    
                    "order_id": 2,
                    "user_id": 5,
                    "billing_shipping_id": 1,
                    "payment_method_id": 2,
                    "delivery_id": 2,
                    "txnid": "",
                    "order_no": "0822-1582695084-0007",
                    "delivery_amount": 0.0,
                    "discount_amount": 570.0,
                    "order_total": 2280.0,
                    "payment_status": "Unpaid",
                    "created_datetime": "2020-02-26T11:01:24",
                    "updated_datetime": "2020-02-26T05:31:24",
                    "status": "Active",
                    "mihpayid": "",
                    "payuMoneyId": "",                                                          

                    "order_detail_data": [{                                              
                    "order_detail_id": 2,
                    "order_id": 2,
                    "user_id": 5,
                    "product_id": 202,
                    "product_size_id": 867,
                    "size_id": 1,
                    "qty": 1,
                    "product_price": 2850.0,
                    "order_item_status": "Placed",
                    "last_status_datatime": "2020-02-26T11:01:24",
                    "feedback": "",
                    "created_datetime": "2020-02-26T11:01:24",
                    "updated_datetime": "2020-02-26T05:31:24",
                    "status": "Active"                                                         

                    }], },    
                   # and so on...                                                     
                  ],
                },    
            ],
           "order_detail_data": [
                {
                    "order_detail_id": 1,
                    "order_id": 1,
                    "user_id": 5,
                    "product_id": 202,
                    "product_size_id": 867,
                    "size_id": 1,
                    "qty": 1,
                    "product_price": 2850.0,
                    "order_item_status": "Placed",
                    "last_status_datatime": "2020-02-26T11:01:24",
                    "feedback": "",
                    "created_datetime": "2020-02-26T11:01:24",
                    "updated_datetime": "2020-02-26T05:31:24",
                    "status": "Active"
                },
                ],
                },                                                                            
                ],                                                                              
                }

i am direct fetching data using raw query from databases instead of ORM. i want to get order_detail_data inside order_data as per order_id.

i am trying to solve this problem, but i didn't get any possible answer which could solve this problem. It would be great if anyone could help me out for what i am looking for. Advance thank you so much!.

Answer 1

If you are using raw queries, then you just need to merge the data. Something like this should work,

def merge_order_data_and_detail(orders, details):
  """Group details by order_id and merge it in orders."""
  # create dictionary key:order_id value:[order_detail_data]
  dic = {}
  for d in details:
    if d['order_id'] not in dic:
      dic[d['order_id']] = []
    dic[d['order_id']].append(d)
  # iterate orders and add details
  for o in orders:
    if o['order_id'] in dic:
      o['order_detail_data'] = dic[o['order_id']]

orders = [
  {
      "order_id": 1,
      "user_id": 5
  },
  {
      "order_id": 2,
      "user_id": 50
  }    
]
details = [
  {
      "order_detail_id": 1,
      "order_id": 1,
      "user_id": 5,
      "product_id": 202
  },
  {
      "order_detail_id": 2,
      "order_id": 1,
      "user_id": 5,
      "product_id": 203
  },
  {
      "order_detail_id": 3,
      "order_id": 2,
      "user_id": 50,
      "product_id": 402
  },
  {
      "order_detail_id": 4,
      "order_id": 2,
      "user_id": 50,
      "product_id": 403
  }
]
merge_order_data_and_detail(orders, details)
print(orders)

Result:

[{'order_id': 1, 'user_id': 5, 'order_detail_data': [{'order_detail_id': 1, 'order_id': 1, 'user_id': 5, 'product_id': 202}, {'order_detail_id': 2, 'order_id': 1, 'user_id': 5, 'product_id': 203}]}, {'order_id': 2, 'user_id': 50, 'order_detail_data': [{'order_detail_id': 3, 'order_id': 2, 'user_id': 50, 'product_id': 402}, {'order_detail_id': 4, 'order_id': 
2, 'user_id': 50, 'product_id': 403}]}]

I remove a lot of orders and details attributes just to make simple to test.

I don't have the whole picture, but should think in using Django models if you are not already using it. It is the way to take advantage of the full potential of the framework.

Hope it helps.