DEV Community

Cover image for Create Nested Tree View with Only One Query.
Burhanuddin Ahmed
Burhanuddin Ahmed

Posted on • Edited on

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  |
|--|--------|--------|
Enter fullscreen mode Exit fullscreen mode

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

SELECT * FROM TABLE_NAME;
Enter fullscreen mode Exit fullscreen mode

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"
  }
]
Enter fullscreen mode Exit fullscreen mode

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: {}
}
Enter fullscreen mode Exit fullscreen mode

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]
  }
})
Enter fullscreen mode Exit fullscreen mode

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
    ]
  }
}
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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": []
  }
]
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)