How do I format plain query to hierarchy json object?

Question

I want to format plain query to hierarchy json to display on front-end but I don't know how

I may have relational database that has Table called "Todo" which has following column

1. ID
2. Name
3. ParentID

for example

[
    {id: 2, Name:"Task 1.1", ParentID: 1},
    {id: 5, Name:"Task 2", ParentID: null},
    {id: 6, Name:"Task 2.1", ParentID: 5},
    {id: 1, Name:"Task 1", ParentID: null},
    {id: 3, Name:"Task 1.2", ParentID: 1},
    {id: 4, Name:"Task 1.3", ParentID: 1},
    {id: 7, Name:"Task 2.2", ParentID: 5},
    {id: 8, Name:"Task 2.3", ParentID: 5},
    {id: 9, Name:"Task 3", ParentID: null}
]

to

[
  {
    "id": 5,
    "Name": "Task 2",
    "ParentID": null,
    "subtasks": [
      {
        "id": 6,
        "Name": "Task 2.1",
        "ParentID": 5,
        "subtasks": []
      },
      {
        "id": 7,
        "Name": "Task 2.2",
        "ParentID": 5,
        "subtasks": []
      },
      {
        "id": 8,
        "Name": "Task 2.3",
        "ParentID": 5,
        "subtasks": []
      }
    ]
  },
  {
    "id": 1,
    "Name": "Task 1",
    "ParentID": null,
    "subtasks": [
      {
        "id": 2,
        "Name": "Task 1.1",
        "ParentID": 1,
        "subtasks": []
      },
      {
        "id": 3,
        "Name": "Task 1.2",
        "ParentID": 1,
        "subtasks": []
      },
      {
        "id": 4,
        "Name": "Task 1.3",
        "ParentID": 1,
        "subtasks": []
      }
    ]
  },
  {
    "id": 9,
    "Name": "Task 3",
    "ParentID": null,
    "subtasks": []
  }
]

For two level (root and children) my attempt is to push Todo that has no parent ID (root) into an array and then push Todos that have parent into the parent todo

I have tried 2 levels hierarchy and I think it works fine but don't know how to implement more level in my next.js project and I'm not quite sure this is good practice or not.

here the code I have tried.

const data = [
    {id: 2, Name:"Task 1.1", ParentID: 1},
    {id: 5, Name:"Task 2", ParentID: null},
    {id: 6, Name:"Task 2.1", ParentID: 5},
    {id: 1, Name:"Task 1", ParentID: null},
    {id: 3, Name:"Task 1.2", ParentID: 1},
    {id: 4, Name:"Task 1.3", ParentID: 1},
    {id: 7, Name:"Task 2.2", ParentID: 5},
    {id: 8, Name:"Task 2.3", ParentID: 5},
    {id: 9, Name:"Task 3", ParentID: null},
];

function buildHierarchy(data) {
    let result = [];
    let todos = [...data];

    // construct root level
    todos.forEach((task)=>{
        if(task.ParentID === null){
            const new_task = {...task, subtasks:[]}

            result.push(new_task)
        }
    })

    // constrct child level
    result.forEach((parent, index)=>{
        todos.forEach((task)=>{
            if(task.ParentID === parent.id){
                const new_task = {...task, subtasks:[]}
    
                parent.subtasks.push(new_task)
            }
        })
    })
    
    return result
}

const result = buildHierarchy(data);
console.log(JSON.stringify(result,null,2));

Answer 1

This solution uses 3 collections to keep track of all the data.

• rootCollection (array) holds all tasks that have ParentID null (or undefined), this variable will act as the result.
• todos (Map) holds all the todo tasks we've already visited, indexed by id.
• subtasks (Map) stores todo tasks that where visited and have a ParentID, but the parent has not yet been iterated.

With above structure in mind, we iterate each task in the data and follow the following steps:

1. Clone the task so we do not mutate the original.
2. Add a subtasks property. If subtasks variable holds has a value for this task use that value as the subtasks collection, if not use an empty array.
3. Store the task in the todos variable.
4. If the task has no parent, add it to the rootCollection array.
5. If the task does have a parent and the parent is present inside todos, add the current task to the subtasks of the parent.
6. If the task does have a parent, but the parent is NOT present inside todos (meaning it has not been iterated yet):
   1. Check if the subtasks variable has a key for the current parent. If there is no key yet, store an empty array as the value.
   2. Add the current task to the collection found in the subtasks variable, so we can use it as subtasks property once we encounter the parent (see 2).

const data = [
  { id: 2, Name:"Task 1.1", ParentID: 1    },
  { id: 5, Name:"Task 2",   ParentID: null },
  { id: 6, Name:"Task 2.1", ParentID: 5    },
  { id: 1, Name:"Task 1",   ParentID: null },
  { id: 3, Name:"Task 1.2", ParentID: 1    },
  { id: 4, Name:"Task 1.3", ParentID: 1    },
  { id: 7, Name:"Task 2.2", ParentID: 5    },
  { id: 8, Name:"Task 2.3", ParentID: 5    },
  { id: 9, Name:"Task 3",   ParentID: null },
];

const rootCollection = [];
const todos = new Map();
const subtasks = new Map();

for (let task of data) {
  task = { ...task, subtasks: subtasks.get(task.id) || [] }; // 1 & 2
  todos.set(task.id, task); // 3
  
  if (task.ParentID == null) {
    rootCollection.push(task); // 4
  } else if (todos.has(task.ParentID)) {
    todos.get(task.ParentID).subtasks.push(task); // 5
  } else {
    if (!subtasks.has(task.ParentID)) subtasks.set(task.ParentID, []); // 6.1
    subtasks.get(task.ParentID).push(task); // 6.2
  }
}

console.log(rootCollection);

The above should work for all nesting levels. Assuming the provided structure has a unique id for each task (not just unique within the parent), and all references to parents are valid. If an parent reference is invalid (because the parent id does not exist) the task will not be included in the result.