title: How do i over lapping columns as nested array ? NodeJS/MySQL (mysql2)
body: <p>Actually, I'm trying to get my mysql result as nested array data i tried to many times with that main.js code got from github <strong>node-mysql-nesting</strong>. Thinking about ? why i need to use that function to convert those query result as nested array anyone knows help me on those segments</p>
  
  <pre><code>let options = { sql: "SELECT 
          units.name as unit_name ,
          units.id as unit_id, units.description as unit_description ,
          classes.name as class_name , classes.academic_year as class_academic_year , semesters.name as semester,
          standards.name as standard,
          branches.name as branch,
          sections.name as section,
          chapters.id as chapter_id,
          chapters.name as chapter_name,
          chapters.unit_id as chapter_unit_id,
          chapters.description as chapter_description
          FROM units 
           LEFT JOIN chapters ON chapters.unit_id = units.id
          JOIN classes ON classes.id = units.class_id 
          JOIN courses ON courses.id = units.course_id 
          JOIN users ON users.id = units.created_by 
          JOIN branches ON branches.id = classes.branch_id 
          JOIN semesters ON semesters.id = classes.semester_id 
          JOIN sections ON sections.id = classes.section_id 
          JOIN standards ON standards.id = classes.standard_id", nestTables: true };
  
   database.query(options).then(([classes, classesFields]) =&gt; {
          if (classes) {
              var func = require('../../config/main')
  
              let data = {
                  nested: func.convertToNested(classes, nestingOptions),
                  original: classes
              }
              res.send(Response.successResponseWithData(SuccessMessage.DataFetched, data))
          }
          else {
              res.send(Response.failureResponse(FailureMessage.DataFetchFailed))
          }
      }).catch(err =&gt; {
          console.log(err)
          res.send(Response.failureResponse(FailureMessage.DataFetchFailed))
      })
  </code></pre>
  
  <p><strong>What i am getting is</strong> </p>
  
  <pre><code>{
      "status": true,
      "status_code": 200,
      "message": "DATA FETCHED SUCCESSFULLY",
      "data": {
          "nested": [
              {
                  "unit_name": "UNIT I",
                  "unit_id": 1,
                  "unit_description": "UNIT I Delivers all contents "
              },
              {
                  "unit_name": "UNIT II",
                  "unit_id": 2,
                  "unit_description": "UNIT II Delivers all contents "
              }
          ],
          "original": [
              {
                  "units": {
                      "unit_name": "UNIT I",
                      "unit_id": 1,
                      "unit_description": "UNIT I Delivers all contents "
                  },
                  "classes": {
                      "class_name": "I Standard B Section",
                      "class_academic_year": 2020
                  },
                  "semesters": {
                      "semester": "I"
                  },
                  "standards": {
                      "standard": "1st Standard"
                  },
                  "branches": {
                      "branch": "Computer Science"
                  },
                  "sections": {
                      "section": "A"
                  },
                  "chapters": {
                      "chapter_id": 7,
                      "chapter_name": "MARS",
                      "chapter_unit_id": 1,
                      "chapter_description": "Mars is the fourth planet from the Sun and the second-smallest planet in the Solar System after Mercury. In English, Mars carries a name of the Roman god of war and is often referred to as the 'Red Planet'.[15][16] The latter refers to the effect of the iron oxide prevalent on Mars' surface, which gives it a reddish appearance distinctive among the astronomical bodies visible to the naked eye.[17] Mars is a terrestrial planet with a thin atmosphere, having surface features reminiscent both of the impact craters of the Moon and the valleys, deserts, and polar ice caps of Earth."
                  }
              },
              {
                  "units": {
                      "unit_name": "UNIT I",
                      "unit_id": 1,
                      "unit_description": "UNIT I Delivers all contents "
                  },
                  "classes": {
                      "class_name": "I Standard B Section",
                      "class_academic_year": 2020
                  },
                  "semesters": {
                      "semester": "I"
                  },
                  "standards": {
                      "standard": "1st Standard"
                  },
                  "branches": {
                      "branch": "Computer Science"
                  },
                  "sections": {
                      "section": "A"
                  },
                  "chapters": {
                      "chapter_id": 8,
                      "chapter_name": "Jupiter",
                      "chapter_unit_id": 1,
                      "chapter_description": "Jupiter is the fifth planet from the Sun and the largest in the Solar System. It is a gas giant with a mass one-thousandth that of the Sun, but two-and-a-half times that of all the other planets in the Solar System combined. Jupiter is one of the brightest objects visible to the naked eye in the night sky, and has been known to ancient civilizations since before recorded history. It is named after the Roman god Jupiter.[18] When viewed from Earth, Jupiter can be bright enough for its reflected light to cast shadows,[19] and is on average the third-brightest natural object in the night sky after the Moon and Venus."
                  }
              },
              {
                  "units": {
                      "unit_name": "UNIT II",
                      "unit_id": 2,
                      "unit_description": "UNIT II Delivers all contents "
                  },
                  "classes": {
                      "class_name": "I Standard B Section",
                      "class_academic_year": 2020
                  },
                  "semesters": {
                      "semester": "I"
                  },
                  "standards": {
                      "standard": "1st Standard"
                  },
                  "branches": {
                      "branch": "Computer Science"
                  },
                  "sections": {
                      "section": "A"
                  },
                  "chapters": {
                      "chapter_id": null,
                      "chapter_name": null,
                      "chapter_unit_id": null,
                      "chapter_description": null
                  }
              }
          ]
      }
  }
  </code></pre>
  
  <p><strong>My Expectation is</strong></p>
  
  <pre><code>{
      "status": true,
      "status_code": 200,
      "message": "DATA FETCHED SUCCESSFULLY",
      "data": {
          "nested": [
              {
                  "unit_name": "UNIT I",
                  "unit_id": 1,
                  "unit_description": "UNIT I Delivers all contents "
  "chapters": [
                      {
                          "chapter_id": 7,
                          "chapter_name": "MARS",
                          "chapter_unit_id": 1,
                          "chapter_description": "Mars is the fourth planet from the Sun and the second-smallest planet in the Solar System after Mercury. In English, Mars carries a name of the Roman god of war and is often referred to as the 'Red Planet'.[15][16] The latter refers to the effect of the iron oxide prevalent on Mars' surface, which gives it a reddish appearance distinctive among the astronomical bodies visible to the naked eye.[17] Mars is a terrestrial planet with a thin atmosphere, having surface features reminiscent both of the impact craters of the Moon and the valleys, deserts, and polar ice caps of Earth."
                      },
                      {
                          "chapter_id": 8,
                          "chapter_name": "Jupiter",
                          "chapter_unit_id": 1,
                          "chapter_description": "Jupiter is the fifth planet from the Sun and the largest in the Solar System. It is a gas giant with a mass one-thousandth that of the Sun, but two-and-a-half times that of all the other planets in the Solar System combined. Jupiter is one of the brightest objects visible to the naked eye in the night sky, and has been known to ancient civilizations since before recorded history. It is named after the Roman god Jupiter.[18] When viewed from Earth, Jupiter can be bright enough for its reflected light to cast shadows,[19] and is on average the third-brightest natural object in the night sky after the Moon and Venus."
                      }
                  ],
              },
              {
                  "unit_name": "UNIT II",
                  "unit_id": 2,
                  "unit_description": "UNIT II Delivers all contents "
              }
          ],
          "original": [
              {
                  "units": {
                      "unit_name": "UNIT I",
                      "unit_id": 1,
                      "unit_description": "UNIT I Delivers all contents "
                  },
                  "classes": {
                      "class_name": "I Standard B Section",
                      "class_academic_year": 2020
                  },
                  "semesters": {
                      "semester": "I"
                  },
                  "standards": {
                      "standard": "1st Standard"
                  },
                  "branches": {
                      "branch": "Computer Science"
                  },
                  "sections": {
                      "section": "A"
                  },
                  "chapters": {
                      "chapter_id": 7,
                      "chapter_name": "MARS",
                      "chapter_unit_id": 1,
                      "chapter_description": "Mars is the fourth planet from the Sun and the second-smallest planet in the Solar System after Mercury. In English, Mars carries a name of the Roman god of war and is often referred to as the 'Red Planet'.[15][16] The latter refers to the effect of the iron oxide prevalent on Mars' surface, which gives it a reddish appearance distinctive among the astronomical bodies visible to the naked eye.[17] Mars is a terrestrial planet with a thin atmosphere, having surface features reminiscent both of the impact craters of the Moon and the valleys, deserts, and polar ice caps of Earth."
                  }
              },
              {
                  "units": {
                      "unit_name": "UNIT I",
                      "unit_id": 1,
                      "unit_description": "UNIT I Delivers all contents "
                  },
                  "classes": {
                      "class_name": "I Standard B Section",
                      "class_academic_year": 2020
                  },
                  "semesters": {
                      "semester": "I"
                  },
                  "standards": {
                      "standard": "1st Standard"
                  },
                  "branches": {
                      "branch": "Computer Science"
                  },
                  "sections": {
                      "section": "A"
                  },
                  "chapters": {
                      "chapter_id": 8,
                      "chapter_name": "Jupiter",
                      "chapter_unit_id": 1,
                      "chapter_description": "Jupiter is the fifth planet from the Sun and the largest in the Solar System. It is a gas giant with a mass one-thousandth that of the Sun, but two-and-a-half times that of all the other planets in the Solar System combined. Jupiter is one of the brightest objects visible to the naked eye in the night sky, and has been known to ancient civilizations since before recorded history. It is named after the Roman god Jupiter.[18] When viewed from Earth, Jupiter can be bright enough for its reflected light to cast shadows,[19] and is on average the third-brightest natural object in the night sky after the Moon and Venus."
                  }
              },
              {
                  "units": {
                      "unit_name": "UNIT II",
                      "unit_id": 2,
                      "unit_description": "UNIT II Delivers all contents "
                  },
                  "classes": {
                      "class_name": "I Standard B Section",
                      "class_academic_year": 2020
                  },
                  "semesters": {
                      "semester": "I"
                  },
                  "standards": {
                      "standard": "1st Standard"
                  },
                  "branches": {
                      "branch": "Computer Science"
                  },
                  "sections": {
                      "section": "A"
                  },
                  "chapters": {
                      "chapter_id": null,
                      "chapter_name": null,
                      "chapter_unit_id": null,
                      "chapter_description": null
                  }
              }
          ]
      }
  }
  </code></pre>
  
  <p><strong>In my main.js</strong></p>
  
  <pre><code>/**
   * Kyle Ladd, 2015
   * See project's Github page for usage details.
   * https://github.com/kyleladd
   */
  /** 
   * Originally forked from
   * Selman Kahya, 2013
   * See project's Github page for usage details.
   * https://github.com/Selmanh
   */
  
  /**
   * takes nested mysql result array as a parameter,
   * converts it to a nested object
   *
   * @param {Array} rows
   * @param {Array} nestingOptions
   * @return {Object}
   */
  exports.convertToNested = function (rows, nestingOptions) {
  
      if (rows == null || nestingOptions == null)
          return rows;
  
      var levels = nestingOptions;
  
      // put similar objects in the same bucket (by table name)
      var buckets = new Array();
  
      for (var i = 0; i &lt; levels.length; i++) {
          var result = new Array();
  
          var level = levels[i];
  
          var pkey = level.pkey;
          var tableName = level.tableName;
  
          for (var j = 0; j &lt; rows.length; j++) {
              var object = rows[j][tableName];
  
              // check if object has key property
              if (object == null){
                  console.log("Error: couldn't find " + tableName + " property in mysql result set")
                  continue;
              }
  
              // if object isn't in result array, then push it
              if(!isExist(result, pkey, object[pkey]) &amp;&amp; object[pkey] != null)
                  result.push(object);
          }
          // Buckets should have two properties, a table name (to identify for relationships) and values.
          buckets.push({table:tableName,values:result});
      }           
  
      // we have similar objects in the same bucket
      // now, move lower level objects into related upper level objects where relationship key values match
      for (var i = buckets.length-1; i &gt;= 1; i--) {
          // For each upper bucket
          for (var u = i-1; u &gt;= 0; u--) {
              if(levels[u].hasOwnProperty('fkeys')){
                  //Go through upper buckets foreign keys
                  for (var f = 0; f &lt; levels[u].fkeys.length; f++) {
                      // if upper bucket has a foreign key to this bucket's table
                      if(buckets[i].table == levels[u].fkeys[f].table){
                          // For each element in this table
                          for (var ft = 0; ft &lt; buckets[i].values.length; ft++){
                              // Go through each element in upper table matching values with this bucket's values
                              for (var utv = 0; utv &lt; buckets[u].values.length; utv++){
                                  // Relationship match
                                  if(buckets[i].values[ft][levels[i].pkey] == buckets[u].values[utv][levels[u].fkeys[f].col]){
                                      // Shouldn't be a list because there can only one, fk can only match one pk
                                      buckets[u].values[utv][buckets[i].table] = buckets[i].values[ft];
                                  }
                              }
                          }
                      }
                  }
              }
          }
          //CHECK TO SEE IF THIS BUCKET HAS THE FOREIGN KEY USED IN AN UPPER LEVEL
          if(levels[i].hasOwnProperty('fkeys')){
              for (var cf = 0; cf &lt; levels[i].fkeys.length; cf++) {
                  // For each upper bucket
                  for (var ub = i-1; ub &gt;= 0; ub--) {
                      if( levels[i].fkeys[cf].table == levels[ub].tableName){
                          // For each element in this table
                          for (var ct = 0; ct &lt; buckets[i].values.length; ct++){
                              // Go through each element in matching table
                              for (var utbv = 0; utbv &lt; buckets[ub].values.length; utbv++){
                                  if(buckets[i].values[ct][levels[i].fkeys[cf].col] == buckets[ub].values[utbv][levels[ub].pkey]){
                                      //If there is a match, create an empty list with the tablename if it doesn't already exist
                                      if(!buckets[ub].values[utbv].hasOwnProperty(levels[i].tableName)){
                                          buckets[ub].values[utbv][levels[i].tableName] = [];
                                      }
                                      // Append object where relationship key values match
                                      buckets[ub].values[utbv][levels[i].tableName].push(buckets[i].values[ct]);
                                  }
                              }
                          }
                      }
                  }
              }
          }
      }    
      // at the end, we have all the nested objects in the first bucket
      return buckets[0].values;
  };
  
  /**
   * checks if one of the objects in an array has the given property,
   * and the value of that property equals to given value
   *
   * @param {Array} array
   * @param {String} key
   * @param {String} value
   * @return {Array}
   */
  //
  var isExist = function(array, key, value){
  
      for (var i = 0; i &lt; array.length; i++) {
          if(array[i][key] == value)
              return true;
      }
  
      return false;
  }
  </code></pre>