Create Nested Tree View with Only One Query.
Tags: javascript, node, programming, sql
Published: 2022-01-06 14:04:55

I have struggled with some kind of multi level tree view maybe you too, usually the easiest way to make this kind of data structure is using `SELECT * FROM table WHERE parentId = <ID>;` and then run it recursively until there is no more data found. This obviously will make a lot of database connections or database input/output processes.

But we can do it by only using a single SQL query though.

So let's say I have a table like this.

```
|id|parentId|category|
|--|--------|--------|
|1 |  null  |  Car   |
|2 |  null  | Gadget |
|3 |   1    | SUV    |
|4 |   1    |  MVP   |
|5 |   3    |  LSUV  |
|--|--------|--------|
``` 

First of all, create a query to get all of the rows in the database.

```
SELECT * FROM TABLE_NAME;
```

We get the result from the query with data structure as follows,

```
[
  {
    "id": 1,
    "parentId": null,
    "category": "Car"
  },
  {
    "id": 2,
    "parentId": null,
    "category": "Gadget"
  },
  {
    "id": 3,
    "parentId": 1,
    "category": "SUV"
  },
  {
    "id": 4,
    "parentId": 1,
    "category": "MVP"
  },
  {
    "id": 5,
    "parentId": 3,
    "category": "LSUV"
  }
]
```

If you use language such as Javascript or Python or maybe Go, you likely can find the shorthand to structure that data to be tree view easily. There are a lot of quick and simple solutions on Stackoverflow.

So I will use Javascript here.

But, I won't give you the shorthand solution. I found this interesting solution from a blog post written in 2007 using PHP.

After we get the array of objects from the SQL query above, then we can create an object.
```
const itemGroup = {
  item: {},
  parents: {}
}
```

Then we can fill `itemGroup` with the data we already had from SQL query.

We can have something like this.

```
const data = <SQL query result>

data.forEach((e) => {
  itemGroup.item[e.id] = e;
  
  if (itemGroup.parents[e.parentId]) {
    itemGroup.parents[e.parentId].push(e.id)
  } else {
    itemGroup.parents[e.parentId] = [e.id]
  }
})
```
Not really straightforward 😅

If we console log, we get some data like this.

```
{
  "item": {
    "1": {
      "id": 1,
      "parentId": null,
      "category": "Car"
    },
    "2": {
      "id": 2,
      "parentId": null,
      "category": "Gadget"
    },
    "3": {
      "id": 3,
      "parentId": 1,
      "category": "SUV"
    },
    "4": {
      "id": 4,
      "parentId": 1,
      "category": "MVP"
    },
    "5": {
      "id": 5,
      "parentId": 3,
      "category": "LSUV"
    }
  },
  "parents": {
    "1": [
      3,
      4
    ],
    "3": [
      5
    ],
    "null": [
      1,
      2
    ]
  }
}
```

With this data structure, you can create a recursive function to loop over the parent property.

_We didn't involve SQL query in this recursive function._

```
function buildTree (parentId, data) {
  let newData = []

  if (data.parents && data.parents[parentId]) {
    for (itemId of data.parents[parentId]) {
      let newObject = data.item[itemId]

      newObject.child = buildTree(itemId, data)

      newData.push(newObject)
    }
  }

  return newData
}

const result = buildTree(null, itemGroup)
```

Because the `parentId` of the top level item is `null`, so let we put `null` here as the first parameter.

We check if the given `parentId` exists in `parents` property, then it will loop over the `parents[parentId]` arrays.

Here is the end result:
```
[
  {
    "id": 1,
    "parentId": null,
    "category": "Car",
    "child": [
      {
        "id": 3,
        "parentId": 1,
        "category": "SUV",
        "child": [
          {
            "id": 5,
            "parentId": 3,
            "category": "LSUV",
            "child": []
          }
        ]
      },
      {
        "id": 4,
        "parentId": 1,
        "category": "MVP",
        "child": []
      }
    ]
  },
  {
    "id": 2,
    "parentId": null,
    "category": "Gadget",
    "child": []
  }
]
```


Reference https://crisp.tweakblogs.net/blog/317/formatting-a-multi-level-menu-using-only-one-query.html