Create Nested Tree View with Only One Query.

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