Reduce SQL query result with getting sum for an object

Question

I need to resolve 2 major problems.

1. Need to remove duplicate rows which I getting as a result of the SQL join query (which has one to many relationship).
2. Need to reduce the SQL result calculating the sum of the corresponding values.

My SQL query is as follows.

SELECT
    ki.item_code,
    i.NAME AS item_name,
    it.item_type_name,
    ist.item_sub_type_name,
    s.company_name,
    rg.delivered_quantity,
    rg.measuring_unit AS delivered_measuring_unit,
    DATE_FORMAT( rg.manufactured_date, '%Y-%m-%d' ) AS manufactured_date,
    DATE_FORMAT( rg.expiry_date, '%Y-%m-%d' ) AS expiry_date,
    ki.total_quantity,
    ki.measuring_unit AS total_measuring_unit,
    ki.available_quantity 
FROM
    vx_hms.kitchen_inventory ki
    INNER JOIN vx_hms.issued_items ii ON ki.issued_items_id = ii.id
    INNER JOIN vx_hms.main_inventory mi ON ii.main_inventory_id = mi.id
    INNER JOIN vx_hms.good_received_note grn ON mi.good_received_note_no = grn.good_received_note_no
    INNER JOIN vx_hms.received_goods rg ON grn.good_received_note_no = rg.good_received_note_no
    INNER JOIN vx_hms.items i ON i.item_code = ki.item_code
    INNER JOIN vx_hms.item_type it ON i.item_type = it.item_type_name
    INNER JOIN vx_hms.item_sub_type ist ON i.item_sub_type = ist.item_sub_type_name
    INNER JOIN vx_hms.supplier s ON grn.supplier_id = s.id
    INNER JOIN vx_hms.supplier_items si ON s.id = si.supplier_id 
WHERE
    si.active_status = 1 
    AND rg.active_status = 1 
    AND s.active_status = 1 
ORDER BY
    rg.expiry_date

This is the SQL result I get.

    [ {
    item_code: '130A',
    item_name: 'Item_name',
    item_type_name: 'Food',
    item_sub_type_name: 'Rice',
    company_name: 'Abc pvt ltd',
    delivered_quantity: 100,
    delivered_measuring_unit: 'kg',
    manufactured_date: null,
    expiry_date: null,
    total_quantity: 10,
    total_measuring_unit: 'kg',
    available_quantity: 10 },
   {
    item_code: '130A',
    item_name: 'Item_name',
    item_type_name: 'Food',
    item_sub_type_name: 'Rice',
    company_name: 'Abc pvt ltd',
    delivered_quantity: 100,
    delivered_measuring_unit: 'kg',
    manufactured_date: null,
    expiry_date: null,
    total_quantity: 5,
    total_measuring_unit: 'kg',
    available_quantity: 5 },
   {
    item_code: '130A',
    item_name: 'Item_name',
    item_type_name: 'Food',
    item_sub_type_name: 'Rice',
    company_name: 'Abc pvt ltd',
    delivered_quantity: 100,
    delivered_measuring_unit: 'kg',
    manufactured_date: null,
    expiry_date: null,
    total_quantity: 5,
    total_measuring_unit: 'kg',
    available_quantity: 5 },
   {
    item_code: '130A',
    item_name: 'Item_name',
    item_type_name: 'Food',
    item_sub_type_name: 'Rice',
    company_name: 'Abc pvt ltd',
    delivered_quantity: 100,
    delivered_measuring_unit: 'kg',
    manufactured_date: null,
    expiry_date: null,
    total_quantity: 10,
    total_measuring_unit: 'kg',
    available_quantity: 10 } ]

It has duplicate rows. I tried using GROUP BY and DISTINCT, but It didn't work for me.

And I tried to reduce the result as follows. I think It is not suitable to use 'includes()' as it ignores similar values in the SQL result.

    const inventoryByItemCode = _.reduce(result, function(a, item) {

const { item_code, item_name, item_type_name, item_sub_type_name, company_name, delivered_quantity, delivered_measuring_unit, manufactured_date, expiry_date, total_quantity, total_measuring_unit, available_quantity } = item;

if  (!a[item_code]) {
   a[item_code] = { item_code, item_name, item_type_name, 
   item_sub_type_name, delivered_measuring_unit, company_name:[], 
   delivered_quantity:[], manufactured_date:[], expiry_date:[], 
   total_quantity:[], total_measuring_unit, available_quantity:[] };
}
if  (!a[item_code].company_name.includes(company_name)) {
   a[item_code].company_name.push(company_name);
}
if  (!a[item_code].delivered_quantity.includes(delivered_quantity)) {                
   a[item_code].delivered_quantity.push(delivered_quantity);
}               
if  (!a[item_code].total_quantity.includes(total_quantity)) {
   a[item_code].total_quantity.push(total_quantity);
}
if  (!a[item_code].manufactured_date.includes(manufactured_date)) {
   a[item_code].manufactured_date.push(manufactured_date);
}
if  (!a[item_code].expiry_date.includes(expiry_date)) {
   a[item_code].expiry_date.push(expiry_date);
}
if  (!a[item_code].available_quantity.includes(available_quantity)) {                   
   a[item_code].available_quantity.push(available_quantity);
}
return a;
}, {});

const obj = Object.values(inventoryByItemCode);

In my case, I need to calculate the total of 'total_quantity' as follows.

[ { item_code: '130A',
    item_name: 'Item_name',
    item_type_name: 'Food',
    item_sub_type_name: 'Rice',
    delivered_measuring_unit: 'kg',
    company_name: [ 'Abc pvt ltd' ],
    delivered_quantity: [ 100 ],
    manufactured_date: [ null ],
    expiry_date: [ null ],
    total_quantity: 15,
    total_measuring_unit: 'kg',
    available_quantity: [5, 10] } ]

Answer 1

Run the following code, it filters duplicates and it will group up by item_code and company_name.

I've left some comments in the script so you can understand what i did there.

let a = [{
    item_code: '130A',
    item_name: 'Item_name',
    item_type_name: 'Food',
    item_sub_type_name: 'Rice',
    company_name: 'Abc pvt ltd',
    delivered_quantity: 100,
    delivered_measuring_unit: 'kg',
    manufactured_date: null,
    expiry_date: null,
    total_quantity: 10,
    total_measuring_unit: 'kg',
    available_quantity: 10
  }, {
    item_code: '130A',
    item_name: 'Item_name',
    item_type_name: 'Food',
    item_sub_type_name: 'Rice',
    company_name: 'Abc pvt ltd',
    delivered_quantity: 100,
    delivered_measuring_unit: 'kg',
    manufactured_date: null,
    expiry_date: null,
    total_quantity: 5,
    total_measuring_unit: 'kg',
    available_quantity: 5
  },
  {
    item_code: '130A',
    item_name: 'Item_name',
    item_type_name: 'Food',
    item_sub_type_name: 'Rice',
    company_name: 'Abc pvt ltd',
    delivered_quantity: 100,
    delivered_measuring_unit: 'kg',
    manufactured_date: null,
    expiry_date: null,
    total_quantity: 5,
    total_measuring_unit: 'kg',
    available_quantity: 5
  },
  {
    item_code: '130A',
    item_name: 'Item_name',
    item_type_name: 'Food',
    item_sub_type_name: 'Rice',
    company_name: 'Abc pvt ltd',
    delivered_quantity: 100,
    delivered_measuring_unit: 'kg',
    manufactured_date: null,
    expiry_date: null,
    total_quantity: 10,
    total_measuring_unit: 'kg',
    available_quantity: 10
  }
];



a = a.map(i => JSON.stringify(i)) // convert objects to strings
  .filter((i, x, s) => s.indexOf(i) === x) // remove duplicate strings
  .map(i => JSON.parse(i)); // convert strings to objects

a = a.reduce((a, i, x, s) => {

  /**
   Object "a" new format:
   {
     item_code: {
        company_name: [ i ] // push the raw "i" item
     },
     item_code2: {
        company_name: []
     },
     ...
     item_codeN: {
        company_name: []
     }
   
   }
  */
  if (!a[i.item_code]) {
    a[i.item_code] = {};
  }
  if (!a[i.item_code][i.company_name]) {
    a[i.item_code][i.company_name] = [];
  }

  a[i.item_code][i.company_name].push(i);

  // Prepare the accumulator for the last Index of the loop
  if (x < s.length - 1) {
    return a;
  } else {

    // Get the accumulator keys (item_code's)
    let v = Object.keys(a);

    /** Mark the keys that we want to alter */
    // Arrays - These are the keys from your desired output that appear as arrays
    let stackKeys = ['delivered_quantity', 'manufactured_date', 'expiry_date', 'available_quantity'];
    // Numbers
    let sumKeys = ['total_quantity'];

    return v.reduce((acc, i, x) => {

      for (let item in a[i]) {

        const company = a[i][item].reduce((acc, i, x) => {

          for (const entry in i) {

            // Add the new keys in THIS accumulator
            if (!acc.hasOwnProperty(entry)) {
              acc[entry] = stackKeys.indexOf(entry) !== -1 ? [] : null;
            }

            // Apply Logic to Keys here
            // This should stack as a list
            if (stackKeys.indexOf(entry) !== -1) {
              acc[entry].push(i[entry]);

              // This will do a sum
            } else if (sumKeys.indexOf(entry) !== -1) {
              acc[entry] = acc[entry] + i[entry]

            } else {

              // Add the key "as is"
              acc[entry] = i[entry]

            }

          }

          return acc;

        }, {});

        acc.push(company);

      }

      return acc;

    }, []);

  }

}, {});

console.log(a);

Answer 2

i don't sure if this will work for each use case. i thinking your db query or db standardization can be better.

const products = [
  {
    company_name: "Abc pvt ltd",
    total_quantity: 10,
    available_quantity: 10
  },
  {
    company_name: "Abc pvt ltd",
    total_quantity: 5,
    available_quantity: 5
  },
  {
    company_name: "Abc pvt ltd",
    total_quantity: 5,
    available_quantity: 5
  },
  {
    company_name: "Abc pvt ltd",
    total_quantity: 10,
    available_quantity: 10
  }
];

const result = products.reduce((acc, obj) => {
  if (!acc.company_name) {
    acc = {
      company_name: obj.company_name,
      total_quantity: 0,
      available_quantity: []
    };
  }
  if (acc.available_quantity.findIndex(i => i === obj.available_quantity) === -1) {
    acc.total_quantity += obj.total_quantity;
    acc.available_quantity.push(obj.available_quantity);
  }
  return acc;
}, {});

console.log("result filtered: ", result);