How to make json with infinite parent child category from database-CodePudding

I have an SQLite database table

 --------------------------------------------------- 
|  id    |      Cat_Name      |     Parent_ID       |
|--------------------------------------------------- 
|  1     |     Asset          |       NULL          |
 --------------------------------------------------- 
|  2     |     Bank           |        1            |
 --------------------------------------------------- 
|  3     |     Cash           |        1            |
 --------------------------------------------------- 
|  4     |     Petty Cash     |        3            |
 --------------------------------------------------- 
|  5     |     ABC Bank       |        2            |
 --------------------------------------------------- 
|  6     |  Dollar Account    |        2            |
 ---------------------------------------------------

i can fetch the data as below

[{ id: 1, Category_Name: "Asset", Parent_ID: 0},
 { id: 2, Category_Name: "Bank", Parent_ID: 1},
{ id: 3, Category_Name: "Cash", Parent_ID: 1},
{ id: 4, Category_Name: "Petty_Cash", Parent_ID: 3},
{ id: 5, Category_Name: "ABC_Bank", Parent_ID: 2},
{ id: 6, Category_Name: "Dollar_Account", Parent_ID: 2}]

In this table, category and subcategory created by the user, we can't assume how many parent and child categories will be in the table

Now I want pass the data as a nested javascript object to the front end

example

{Asset: {Bank: {ABC Bank: 5}, {Dollar Account: 6}
         },
         {Cash:{PettyCash: 4}, if any...}
}

Could anybody can help to get this result in the best way...

Thanks in advance

CodePudding user response：

Presented below is one possible way to achieve the desired objective. Admittedly, it is not very elegant (& possibly not the most-efficient).

Code Snippet

// helper method to recursively-add to object
const recurAdd = (arr, idx, res) => {
  // when "idx" exceeds length of array "arr", 
  // simply return existing result "res" object
  if (idx >= arr.length) return res;
  
  // de-structure to access parent-id & id for current elt
  const { Parent_ID, id } = arr[idx];
  
  if (Parent_ID in res) {
    // parent-id exists at current object, 
    // so, add "id" to same object (mutate)
    res[Parent_ID][id] = {};
    
    // make recursive call for "next" elt in "arr"
    return recurAdd(arr, idx 1, res);
    
  } else {
    // find next-level object where current elt will fit
    const foundIt = Object.values(res).map(obj => recurAdd(arr, idx, obj));
    // NOTE: "obj" is part of "res" and it gets mutated
    
    // if found, make recursive call
    if (foundIt.some(x => x !== false)) return recurAdd(arr, idx 1, res);
  };
  
  // in case parent-id is not found, simply return false
  return false;
};

// helper method to substitute "id" with "category names"
const recurNamify = (obj, myMap) => (
  // reconstruct object from key-value pairs of intermediate result
  Object.fromEntries(
    // generate intermediate result of key-value pairs
    Object.entries(obj)
    .map(([k, v]) => (
      // substitute key (ie, "id") with category-name
      Object.keys(v).length === 0
      ? [myMap[k], k]
      : [myMap[k], recurNamify(v, myMap)]
    ))
    // when "v" is not an empty object, make recursive call
  )
);

// transform the array into nested object
const myTransform = arr => {
  // first transform "Number" to "string" for id and parent-id
  // because JS-object keys are string type
  const myArr = arr.map(ob => ({
    ...ob,
    id: ob.id.toString(),
    Parent_ID: ob.Parent_ID.toString()
  }));
  
  // generate a dictionary/map for "id" to category-name
  const myMap = myArr.reduce(
    (acc, itm) => {
      acc[itm.id] = itm.Category_Name
      return acc;
    },
    {}
  );
  
  // find the index of root (ie, parent id is zero)
  const rIdx = myArr.findIndex(({ Parent_ID }) => Parent_ID === '0');
  
  // obtain the root & mutate "arr" by removing the root
  const [root] = myArr.splice(rIdx, 1);
  
  // use the helper methods to transform
  return recurNamify(recurAdd(myArr, 0, {[root.id]: {}}), myMap);
};

const rawData = [
  { id: 1, Category_Name: "Asset", Parent_ID: 0},
  { id: 2, Category_Name: "Bank", Parent_ID: 1},
  { id: 3, Category_Name: "Cash", Parent_ID: 1},
  { id: 4, Category_Name: "Petty_Cash", Parent_ID: 3},
  { id: 5, Category_Name: "ABC_Bank", Parent_ID: 2},
  { id: 6, Category_Name: "Dollar_Account", Parent_ID: 2}
];

console.log('transformed: ', myTransform(rawData));

.as-console-wrapper { max-height: 100% !important; top: 0 }

Explanation

Inline comments added to the snippet above.

PS: If you'd like to add value to stackoverflow community,

Please consider reading: What to do when my question is answered Thank you !

CodePudding user response：

I suggest you change the design of the output object. I think the array approach would be better for the frontend.

const rawData = [
  { id: 1, Category_Name: "Asset", Parent_ID: 0},
  { id: 2, Category_Name: "Bank", Parent_ID: 1},
  { id: 3, Category_Name: "Cash", Parent_ID: 1},
  { id: 4, Category_Name: "Petty Cash", Parent_ID: 3},
  { id: 5, Category_Name: "ABC Bank", Parent_ID: 2},
  { id: 6, Category_Name: "Dollar Account", Parent_ID: 2},
  { id: 7, Category_Name: "Another Wallet", Parent_ID: 4},
];

const getParentDeep = (arr, targetId) => arr.find(({ id }) => id === targetId)
    ?? arr.flatMap(({ children }) => getParentDeep(children, targetId))
    .filter(e => e)
    .at(0);

const result = rawData
  .sort(({ Parent_ID: a }, { Parent_ID: b }) => a - b)
  .reduce((acc, { id, Category_Name, Parent_ID }) => {
    const obj = { id, name: Category_Name, children: [] };
    const parentObj = getParentDeep(acc, Parent_ID);
    if (parentObj) parentObj.children.push(obj)
    else acc.push(obj);
    return acc;
}, []);

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0 }

The result will look like this:

[{
  id: 1,
  name: "Asset",
  children: [{
    id: 2,
    name: "Bank",
    children: [{
      id: 5,
      name: "ABC Bank",
      children: []
    }, {
      id: 6,
      name: "Dollar Account",
      children: []
    }]
  }, {
    id: 3,
    name: "Cash",
    children: [{
      id: 4,
      name: "Petty Cash",
      children: [{
        id: 7,
        name: "Another Wallet",
        children: []
      }]
    }]
  }]
}]