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 a recursive function which will get the data object and put where condition like WHERE parentId = <ID>.

So let say I have 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 query to get all of the rows in 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 those data to be tree view easily. There are a lot of quick and simple solution 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 got the array of object SQL query above, then we can create a hashMap or objectMap.

const itemGroup = {
  item: {},
  parents: {}
}

Then we can fill itemGroup with the data we already have 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 got 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.

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 of the parentId of the top level item is null, so let's we set null here as the first parameter.

We check if 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

Categories:

Javascript Sql

Tags:

Javascript Node Programming Sql

Create Nested Tree View with Only One Query.

Categories:

Tags:

Related Posts