how to create tree structure recursive json & query for it ,where id =5 in nodejs

Question

Table for my folder structure

 id |    name      | parent_id 
----+--------------+-----------
  1 | parent       |          
  2 | child        |         1
  3 | grandchild A |         2
  4 | grandchild B |         2
  5 | grandchild c |         3

select id,parent_id, name from table.

I want to create a json for this result set which should be in tree structure. how to make a json for this, please help.

Orpha

what kind of structure are you using - 2d array? array of objects? – Drucilla

Answer 1

Something like this?

{
  "1": {
      "name": "parent",
      "parent_id": 0
   },
  "2": {
     "name": "child",
     "parent_id": 1
  },
  "3": {
     "name": "grandchild a",
     "parent_id": 2
  }
}

Edit: Or array:

[
    { "id": 1, "name": "parent", "parent_id": 0},
    { "id": 2, "name": "child",  "parent_id": 1}
    // and so on
]

Subgroup

json should generate [5,3,2,1],[4,2,1],[3,2,1],[2,1],[1] – Orpha

i want parent of each child jsut like we have folder structure in windows os, c drive->folder1->folder2, in my example 1 is the parent of 2 & ,2 is parent of 3 & so on, – Orpha

Answer 2

I guess I would structure the tree like this (with id's where appropriate):

{
     name: "parent"
     children: [
       {
          name: "child"
          children: [
             {
               name: "grandchild a"
              }   ,
             {
               name: "grandchild b"
              }   ,         
             {
               name: "grandchild c"
              }   
          ]   
        }
      ]
  }

What is your data structure inside of Node that you need to transform? If this is in a DB table, then how are you getting it into node and what does it look like once it is there - you could console.log(JSON.stringify(object, null 4)) to output the current structure

Feaster

what are you starting with? – Feaster

if i select any parent then i should get all its grand children – Orpha

Are you able to get the data from the DB into Node? Have you solved that bit of the problem yet? – Feaster

no i have't got that as well, m getting wrong result set – Orpha

you need to solve that first. Look into node-orn2 or something similar like waterline. – Feaster

Answer 3

why dont yo do it like this:

function Directory(p_id, p_name){
  this.name = p_name;
  this.id = p_id;
  this.subdir = [];
}

Directory.prototype.addSubDir(p_directory){
  this.subdir.push(p_directory);
}

then somewhere in your code do this:

var arr_struc = ...;//[your data]
var dir_mem = [];
var rootDir = new Directory(0, 'ROOT')
dir_mem.push(rootDir);

for(var i = 0; i < arr_struc.length; i++){
  var tmp_directory = new Directory(i+1, arr_struc[i].name)
  dir_mem.push(tmp_directory);
  if(!arr_struc[i].parent_id)
    { rootDir.addSubDir(tmp_directory) }
  else
    { dir_mem[arr_struc[i].parent_id].addSubDir(tmp_directory) }
}

adding some other methods to read subdirectorys by ID or simular and returning "this" you would be able to get subdirectorys by methodchaining ;) pretty OO style but I think its a nice way to structure code

Hope it helped in your special case

EDIT: here is an example of methodchaining your subdir's:

Directory.prototype.getSubDirs(){
  return this.subDir;
}
Directory.prototype.getSubDirById(p_id){
  var allSubDirs = this.getSubDirs();
  for(var i = 0; i < allSubDirs.length; i++){
    if(allSubDirs[i].id === p_id) return allSubDirs[i];
  }
  return false;
}
Directory.prototype.getSubDirByName(p_name){
  var allSubDirs = this.getSubDirs();
  for(var i = 0; i < allSubDirs.length; i++){
    if(allSubDirs[i].name === p_name) return allSubDirs[i];
  }
  return false;
}

Then you could do:

rootDir.getSubDirByName('parent').getSubDirByName('child').getSubDirByName('grandchild A');

or something like that :) -crazy

Drucilla

I have edited my Answer for some prototype methods - just typed them in here without testing & syntaxchecking ... so look out for errors – Drucilla

sorry but what will the query for it, if i want to get record for id=5 – Orpha

loop over the dir_mem -> if ( dir_mem[i].getSubDirById(5) ) return dir_mem[i].getSubDirById(5) – Drucilla

searching for a directory this way is a hard job... but if you dont habve to many querys and to difficult directory structure everything is fine.... having this dir_mem doesnt produce allot more memory usage because its refering to the objects - not holding copys – Drucilla

select folderId,parentId from userFolder m where (parentId in (select folderId from userFolder n where n.folderId=m.parentId )or parentId=85) or (folderId in (select parentId from userFolder n where n.parentId= m.folderId )or folderId=85); my query for it but not proper, here folderid refrence to id & parentId refre to parent_id in above example – Orpha

What kind of query are you talking about? – Drucilla

I am clearly lacking information - im starting to make assumptions that your going nodejs -> mongodb or something or why are you talking about a QUERY= – Drucilla

sorry ,i am using mysql with nodejs not mongodb – Orpha

Mysql is a relational DB where you have FIELDS! not any json structures! why the heck did you ask how to save stuff in a json format then? – Drucilla

Answer 4

On a project I worked on for the Rwandan NGO Solid Africa, tree structure was a important part of keeping track of expenses and donations (your expense or donation belonged to a category, food, special care etc.). Based on this experience I developed the tree-util node package.

To get a tree structure including some handy methods do this:

1. Install the package with this command: npm install tree-util

2. You need to get the data represented as json. If it is a table in the database, a simple select using a node package will get you the data as json.

3. Build the tree based on the json data loaded from the db. A more generic example can be below, but it can be adjusted by changing the items array to be the data loaded from your table and setting the parentid property of the config to be 'parent_id'

var tree_util = require('tree-util')
 
// An array where the items has a parent child reference using id properties 
var items = [{ id : 1 }, { id : 2, parentid : 1 }, { id : 3, parentid : 1 },
             { id : 4, parentid : 1 }, { id : 5, parentid : 3 }];
 
// Config object to set the id properties for the parent child relation 
var standardConfig =  { id : 'id', parentid : 'parentid'};
 
// Creates an array of trees. For this example there will by only one tree 
var trees = tree_util.buildTrees(items, standardConfig);

Hemichordate