LWC group record by category lightning-tree-grid ignores the hierachy

Question

I currently have a LWC ( which i didn’t create as totally new to LWC) that returns a lightning-datatable that allows a user to select many products and then do some logic.

I am now asked to have this structure in Salesforce Where Category is a custom object and product 2 is the standard SF object.

Category 0
    Category 1
        Product2-A
        Product2-B
        Category 1-1
            Product2-C
            Product2-D
        Category 1-2
            Product2-E
    Category 2
        Product2-F
        Category 2-1
                        Product2-G  

I created the category custom object and attempted to use lightning-tree-grid Which displays the items but the category hierarchy is ignored. I reckon i am missing some looping but being too new i can't figure out what i am missing Output

The JS

import { LightningElement, track, wire, api } from 'lwc';
import LightningTreeGrid from 'lightning/treeGrid';
import getSpotforCats from '@salesforce/apex/ProductsBrowserByCatController.getSpotforCats';
import SystemModstamp from '@salesforce/schema/Account.SystemModstamp';




const columns = [




   {
       label: 'Spot Category',
       fieldName: 'Category_Name__c',
       type: 'text',
       sortable: true,
       initialWidth:400,
       wrapText: true,
       cellAttributes: {
           iconName: 'standard:category',
       },
   },
   {
       label: 'Spot name',
       fieldName: 'prod_name__c',
       type: 'text',
       sortable: true,
       wrapText: true,


   }
];






export default class ProductsBrowserCat extends LightningElement {


   key;
   @track initialized = false;
   @track isloading = false;
   @api recordId;
   @api existingProducts = [];
   @track products;
   columns = columns;
   selectedRows = [];




@track myData = [];


   error;
  
   @wire(getSpotforCats)
   wiredProducts__r({error, data}) {
       if (error) {
           // Handle error
           this.error = error;
       } else if (data) {
           console.log('@@@ data ' +data);
           // Process record data
           var strData = JSON.parse( JSON.stringify( data ) );


           strData.map((row, index) => {
               if (row['Products__r']) {
                   row._children = row['Products__r']; //define rows with children
                   delete row.Products__r;
               }    
           });
           this.myData = strData;
       }
   }

The HTML:

           <lightning-layout-item size='12' padding="arround-small">


               <lightning-tree-grid
                   columns={columns}
                   data={myData}
                   key-field="Category_Name__c"
                   onrowselection={handleRowSelection}>
               </lightning-tree-grid>
       
      
       </lightning-layout-item>

The controller

   @AuraEnabled(cacheable=true)
    public static list<Spot_Category__c> getSpotforCats(){
           return [SELECT id,Category_Name__c, Parent_Category__c, (SELECT name,prod_name__c,ProductCode FROM Products__r) FROM Spot_Category__c];
    }

Answer 1

You returned Parent_Category__c, but you're not actually building a hierarchy with that data. First, create a object mapped by the category Id, then add each category to the parent category's _children property. Also, you're using map incorrectly, it should be forEach as used here, or better, you can just use map as intended.

// Transform Products__r to _children, copy data
let mappedData = data.map((v) => {
  return {
    ...v,
    _children: v.Products__r?.map((product) => ({ ...product })) || [],
  };
});
// Map into an object by Id
let categoryById = mappedData.reduce(
  (p, v) => {
    p[v.Id] = v;
    return p;
  },
  { null: { _children: [] } }
);
// Add children to parent
Object.entries(categoryById).forEach(([k, v]) => {
  k !== 'null' && categoryById[v.Parent_Category__c || null]._children.push(v);
});
// List of root categories
this.myData = categoryById[null]._children;