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
Top comments (0)