# Aggregating API data set in MySQL for tabulator table

**URL:** <https://discourse.nodered.org/t/aggregating-api-data-set-in-mysql-for-tabulator-table/71888>\
**Category:** General\
**Created:** [8 December 2022 17:50 UTC](https://discourse.nodered.org/t/aggregating-api-data-set-in-mysql-for-tabulator-table/71888 "2022-12-08T17:50:30Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![interscope101](https://avatars.discourse-cdn.com/v4/letter/i/a88e4f/32.png) [@interscope101](https://discourse.nodered.org/u/interscope101)\
**Post date:** [8 December 2022 17:50 UTC](https://discourse.nodered.org/t/aggregating-api-data-set-in-mysql-for-tabulator-table/71888/1 "2022-12-08T17:50:30Z")

</div>

Hello! I am trying to aggregate trading transaction history in tabulator table.

1. I make an API call to get transaction record(s); there is a limit of 100 records per call (within any specified timeframe). Hence I need to create a local data set that continuously aggregates new transactions which are sent to the table.

2. I got as far as the function below: grab certain amount of of records every minute (enough records assures sufficient overlap so nothing is missed) and use unique "trade\_id" index ((tempObj.trade\_id = obj.id) to update/append the set. The approach is working (with a few glitches) as long as the data is getting saved in memory - when I try to switch saving to local file, the data set no longer gets appended, the entire set just gets replaced (i.e. always capped at 100).

3. I want to switch to using MySQL instead file, both to overcome problem and because I expect the data to grow indefinity, so seems like DB is better suited. Unfortunately my MySQL skills are non-existent; I created the db and connected to it with node-red (that's about it). Hoping someone can help with this step. Thanks!

```auto
var check = flow.get('recordKeeper', 'file');
var keyTemp = flow.get('recordKeys', 'file');

// var check = context.get('recordKeeper');
// var keyTemp = context.get('recordKeys');

const input = msg.payload.filter(s => s.status.type == "stop_loss_finished" || s.status.type == "finished" || s.status.type == "panic_sold");   

var tempObj ={};
var tempArr = [];
for(let obj of input){

    if(!(check.hasOwnProperty(obj.id))){
        tempObj = {};
        tempObj.pair = (String(obj.pair)).replace("USDT_", "");
        tempObj.trade_id = obj.id;
        tempObj.profit_usd = Number(obj.profit.usd).toFixed(2);
        tempObj.profit_percent = obj.profit.percent;
        tempObj.position = Number(obj.position.total.value).toFixed(0);
        tempObj.volume = obj.data.current_price.quote_volume;
        tempObj.close_price = obj.data.average_close_price;
        tempObj.closed = obj.data.closed_at;
        
  duration = new Date(obj.data.closed_at) - new Date(obj.data.created_at)

  var milliseconds = Math.floor((duration % 1000) / 100),
      seconds = Math.floor((duration / 1000) % 60),
      minutes = Math.floor((duration / (1000 * 60)) % 60),
      hours = Math.floor((duration / (1000 * 60 * 60)) % 24);

      hours = (hours < 10) ? "0" + hours : hours;
      minutes = (minutes < 10) ? "0" + minutes : minutes;
      seconds = (seconds < 10) ? "0" + seconds : seconds;

      tempObj.dur = hours + ":" + minutes;

        if (obj.status.type === "stop_loss_finished"){tempObj.status = "SL";}
            else if (obj.status.type === "finished"){tempObj.status = "TP";}
            else if (obj.status.type === "panic_sold"){tempObj.status = "EX";}
            else {tempObj.status = obj.status.type}

        if (obj.stop_loss.enabled) {
            tempObj.to_sl = ((1 - obj.stop_loss.conditional.price.value / obj.data.average_enter_price) * 100).toFixed(2);
        }

        if (obj.status.type == "stop_loss_finished") {
            tempObj.S_L = (((obj.data.average_enter_price - obj.stop_loss.conditional.price.value) / obj.data.average_enter_price) * 100).toFixed(2);
        }

        if (obj.status.type == "finished") {
            tempObj.T_P = (((obj.take_profit.steps[0].price.value - obj.data.average_enter_price) / obj.data.average_enter_price) * 100).toFixed(2);
        }

        tempArr = obj.note.split("_");
        
        tempObj.frames = tempArr[0];
        tempObj.gain = tempArr[1];
        tempObj.gain_st = tempArr[2];
        tempObj.freq = tempArr[3];
        tempObj.analysis = tempArr[4];
        tempObj.tp_a = tempArr[5];
        tempObj.tp_trl_a = tempArr[6];
        tempObj.sl_a = tempArr[7];
        tempObj.sl_trl_a = tempArr[8];
        tempObj.tm_a = tempArr[9];
        tempObj.close = tempArr[10];
        tempObj.crypt = tempArr[11];
        tempObj.exch = tempArr[12];
        tempObj.v = tempArr[13];       

        check[obj.id] = tempObj;
        keyTemp.push(obj.id);
    
  }
}

flow.set('recordKeeper', check, 'file');
flow.set('recordKeys', keyTemp, 'file');

// context.set('recordKeeper', check);
// context.set('recordKeys', keyTemp);

msg = {};
msg.payload = [];

for(let k of keyTemp){
    msg.payload.push(check[k]);
}

return msg;

```

---

<div class="post-metadata">

**Author:** ![interscope101](https://avatars.discourse-cdn.com/v4/letter/i/a88e4f/32.png) [@interscope101](https://discourse.nodered.org/u/interscope101)\
**Post date:** [11 December 2022 19:42 UTC](https://discourse.nodered.org/t/aggregating-api-data-set-in-mysql-for-tabulator-table/71888/2 "2022-12-11T19:42:03Z")

</div>

I got a bit further in being able to execute each one of the MySQL commands. Hoping someone can assist with this function. Thanks in advance.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/f/0fba19ddc9c33d896c6bb6b25417d2098549785a.png)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [9 February 2023 19:42 UTC](https://discourse.nodered.org/t/aggregating-api-data-set-in-mysql-for-tabulator-table/71888/3 "2023-02-09T19:42:34Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.