javascript - 连接具有多对多关系字段的两个表

Question

我正在使用 google data studio 并尝试从第三方系统获取数据。那样的话，第三方系统就有问题了，因为它不允许SQL join。因此，我通过分别使用2个URL获取数据并将两个表(customer_order表和Customer_Order_Demand表)连接起来。但问题是有一个字段(Order_NO)在一个表中具有多对多关系。根据我的代码，在加入两个表后，我只在 Order_No 列中获得第一个值。我想按相应日期显示每个项目的订单数。

  Customer Order Demand Table                                        
+-----------------------------+ 
| id  | item     |    Date    |         
|-----|----------|------------| 
|  1  |  BLU     | 7/10/2020  |  
|  2  |  PM      | 7/20/2020  |   
|  3  |  BLU     | 7/23/2020  |          
+-----------------------------+ 


    Customer Order Table                                        
+---------------------------------------------------------+
| id  |  COD_id         |  Order_NO      |   Date         |
|-----|----------------------------------|----------------|
|  1  |      1          |  #100          |    7/10/2020   |
|  2  |      1          |  #130          |    7/10/2020   |
|  3  |      2          |  #200          |    7/20/2020   |
|  4  |      3          |  #500          |    7/23/2020   | 
+---------------------------------------------------------+

                   Expected  Table                                        
+-----------------------------------------------+
|     |  Item    |    Date    |  Order_NO       |
|-----|----------|------------|-----------------|
|  1  |  BLU     | 7/10/2020  |  #100 /#130     |
|  2  |  PM      | 7/20/2020  |  #200           |
|  3  |  BLU     | 7/23/2020  |  #500           |        
+-----------------------------------------------+



 

 var url=[
    'http://test.smartapps.lk/rest/api/selectQuery?sessionId=' + SessionId + '&maxRows=10&query=select id, Item, Date from Customer_Order_demand &output=json'
  ].join('');
  
  var url2=[
    'http://test.smartapps.lk/rest/api/selectQuery?sessionId=' + SessionId + '&maxRows=10&query=select id,Order_NO from Customer_Order &output=json'
  ].join(''); 
  
  
  var responseID2 = UrlFetchApp.fetch(url);
  var responseID3 = UrlFetchApp.fetch(url2);
   
  var table1 = JSON.parse(responseID2.getContentText());     
  var table2 = JSON.parse(responseID3.getContentText());
   
  var finalArray = [];
  
  for(var i in table1){
      for(var j in table2){

        //Joining two tables 
        if(table1[i][0]==table2[j][0]){

            // converting to json object
             var newObj = {      
             "Item": table1[i][1],
             "Date":table1[i][2],
             "Order_NO": table2[j][1] 
             };
          
         finalArray.push(newObj);
        }
      }
   
  }
  
   return finalArray;
  
  
 

Answer 1

• Reduce第二个表到 map :

  const table2map = table2.reduce(
    (mp,[codId,ordNo]) => 
        mp.set(codId,
         mp.has(codId)
          ? [...mp.get(codId),ordNo]
          : [ordNo]
        ), new Map
  )
  

• Map table1 到对象数组:

  const finalArr = table1.map(
    ([id,Item,Date]) =>
        ({Item,Date,
        "Order_No": table2map.get(id).join()})
  )