I'm trying to combine data from two tables (the first being a shipment details table with data for a single shipment per row, the other a table that tracks transactions made against shipments) to build a view that shows me the product data for a given shipment.

My current attempt to get the data looks like this:

SELECT DISTINCT
    sd.erp_order AS shipment_id, 
    th.product_code, 
    th.lot
FROM transaction_history th
JOIN shipment_detail sd
    ON sd.shipment_id = th.reference_id
    AND sd.item = th.item
WHERE th.transaction_type = '1'
    AND sd.erp_order in ('1111', '1112')

which returns my data in the following format:

|shipment_id|product_code|lot|
|       1111|   PRODUCT_A| 1A|
|       1111|   PRODUCT_B| 2B|
|       1112|   PRODUCT_A| 1A|
|       1112|   PRODUCT_B| 3B|

This is great, but now I need to organize it so that when it goes through my API (I'm using Django), it the lot code and the product code are grouped together in their own object, and then all the products are listed under the relevant shipment:

[
  {
    "shipment_id": '1111',
    "products": [
      {
        "product_code": "PRODUCT_A",
        "lot": "1A",
      },
      {
        "product_code": "PRODUCT_B",
        "lot": "2B",
      }
    ]
  }
]

and I'm not quite sure how to do it. Is this something that can be done with SQL, or will I have to do it with Python?

I also recognize that I should be able to get this kind of data from existing tables, but this is a siloed database that I cannot modify, and I'm told by the supporting team that this is the best place to get the data I need.

CodePudding user response：

Try something like:

data = serializers.serialize('json', SomeModel.objects.raw(query), fields=('id', 'name', 'parent'))