Published: 07/14/2020

There comes a time in every developers life when they have to decide “Do I work towards the best solution or just ship what I have?”

Today was that day. I built all kinds of neat things, a SQL materialized view to process 1,400,000 records, not to mention an ETL process to import that data, but my HARDEST problem was trying to figure out how to build a tree. Yes, I’m a CS background and I’m not talking about a 2 leaf binary tree with integers, I’m really talking about a nested structure.

The problem

The SQL output a table result like this:

category_1	category_2	category_3	category_4
IT	Hardware	Servers	Dell
IT	Software	Adobe	Design Software
IT	Hardware	Laptops	Apple
Office Supplies	Printer	Paper	Unclassified

This is a very clean view; but the json looks like this:

[
  {
    "category_1": "IT",
    "category_2": "Hardware",
    "category_3": "Servers",
    "category_4": "Dell"
  },{
    "category_1": "IT",
    "category_2": "Hardware",
    "category_3": "Laptops",
    "category_4": "Apple"
  },{
    "category_1": "IT",
    "category_2": "Software",
    "category_3": "Adobe",
    "category_4": "Design Software"
  },{
    "category_1": "Office Supplies",
    "category_2": "Printer",
    "category_3": "Paper",
    "category_4": "Unclassified"
  }
]

The JSON sucks. This is what I want my output to look like:

{
  data: {
    map: {
      "IT": {
        "Hardware": {
          "Servers": [ "DELL" ],
          "Laptops": [ "Apple" ] 
        },
        "Software": {
          "Adobe": [ "Design Software" ]
        }
      },
      "Office Supplies": {
        "Printer": {
          "Paper": [ "Unclassified" ]
        }
      }
    }
  }
}

Not bad but a lot of work for 4 records; I could just hard code it, right? Remember that million records; I have about 400 categories of deepley nested like this.

How I solved it

To get that result, I looked at NPM modules, binary tree generation algorithms; I thought this is stupid easy; somebody has written an module for it. What I found had explicit child / parent relationships defined as IDs and they wouldn’t support a function to change anything. My Solution is stupid, infact I’m not posting all of it, but its something like this:

var map = {};

for(var item in list) {
  if(map[item.category_1]) {
    if(map[item.category_1][item.category_2]) {
    if(map[item.category_1][item.category_2][item.category_3]) {
      map[item.category_1][item.category_2][item.category_3].push(map[item.category_1][item.category_2][item.category_3][item.category_4]);
    } else {
      // ... build level 3 & 4
    } 
    }else {
      // build level 2,3 & 4
    }
  } else {
      // build level 1, 2,3 & 4
  }
}

Thats a big, nasty function call. All to get nesting? You’re crazy man, but then I remembered, it’s always better to ship OK but working code today rather then shipping “PERFECT” code next week. There you have it, ship ugly code man, sometimes you need to.