transform an Array into a multidimensional Array in livecode

Question

How do i transform an Array like this into a multidimensional Array in livecode? I have about 20 Toplevel categories with nested categories as described below. The nesting can be up to 6 levels deep.

Starting array as result of database query

Array
(
 [1] => Array
    (
        [id] => 10
        [parent_id] => 0
        [name] => Hitachi
    )

 [2] => Array
    (
        [id] => 15
        [parent_id] => 0
        [name] => Milwaukee
    )

 [3] => Array
    (
        [id] => 20
        [parent_id] => 0
        [name] => Thoshiba

    )

 [4] => Array
    (
        [id] => 31
        [parent_id] => 10
        [name] => tools
    )

 [5] => Array
    (
        [id] => 32
        [parent_id] => 10
        [name] => Spareparts Hitachi
    )

 [6] => Array
    (
        [id] => 35
        [parent_id] => 32
        [name] => electric parts
    )

 [7] => Array
    (
        [id] => 37_
        [parent_id] => 32
        [name] => hydraulic Parts
    )

  [8] => Array
    (
        [id] => 40_
        [parent_id] => 32
        [name] =>  other Parts
    )

   [9] => Array
    (
        [id] => 43_
        [parent_id] => 32
        [name] =>  more Parts
    )

    [10] => Array
    (
        [id] => 45_
        [parent_id] => 15
        [name] =>  Spareparts Milwaukee
    )     

    ........      

)

My goal is to get an nested Array like this:

 Array
 (
   [1] => Array
        (
        [id] => 10
        [parent_id] => 0
        [name] => Hitachi
        [children] => Array

                        (
                         [id] => 31
                         [parent_id] => 10
                         [name] => tools
                         )

                         (
                         [id] => 32
                         [parent_id] => 10
                         [name] => Spareparts Hitachi
                         [children] => Array

                                           (
                                            [id] => 35
                                            [parent_id] => 32
                                            [name] => electric parts
                                           )

                                           (
                                             [id] => 37_
                                             [parent_id] => 32
                                             [name] => hydraulic Parts
                                           )

                                           (
                                             [id] => 40_
                                             [parent_id] => 32
                                             [name] =>  other Parts
                                           )

                         )


[2] => Array
    (
        [id] => 15
        [parent_id] => 0
        [name] => Milwaukee
        [children] => 

                    (
                     [id] => 45_
                     [parent_id] => 15
                     [name] =>  Spareparts Milwaukee
                     )     
    )

[3] => Array
    (
        [id] => 20
        [parent_id] => 0
        [name] => Thoshiba
     )   
   )

The end result should be to build a categorie tree as found in online shops to select a nested categorie and display the products of the selected cat. For building the tree i would like to use rtree from tapirsoft.

Answer 1

So you have to write a recursive function for this:

function buildTree pArray, pParentID
   if pParentID is empty then put 0 into pParentID
   local tBranch

   repeat for each key tKey in pArray
      local tElement
      put pArray[tKey] into tElement
      if (tElement["parent_id"] is pParentID) then
         local tChildren
         put buildTree(pArray, tElement["id"]) into tChildren
         if the number of elements of tChildren > 0 then
            put tChildren into tElement["children"] 
         end if
         put tElement into tBranch[the number of elements of tBranch + 1]
      end if
   end repeat

   return tBranch
end buildTree

To test the script I created a LiveCode array "tFlatArray" with your data in a mouseUp handler and then called the recursive "buildTree" function which returns a structured array in the format you're looking for. I changed some of the "parent_id"numbers from the example you provided so that the 10 elements made up a nested array:

on mouseUp
   local tFlatArray
   put 1 into tFlatArray[1]["id"]
   put 0 into tFlatArray[1]["parent_id"]
   put "Hitachi" into tFlatArray[1]["name"]

   put 2 into tFlatArray[2]["id"]
   put 3 into tFlatArray[2]["parent_id"]
   put "Hitachi" into tFlatArray[2]["name"]

   put 3 into tFlatArray[3]["id"]
   put 3 into tFlatArray[3]["parent_id"]
   put "Thoshiba" into tFlatArray[3]["name"]

   put 4 into tFlatArray[4]["id"]
   put 10 into tFlatArray[4]["parent_id"]
   put "tools" into tFlatArray[4]["name"]

   put 5 into tFlatArray[5]["id"]
   put 10 into tFlatArray[5]["parent_id"]
   put "Spareparts Hitachi" into tFlatArray[5]["name"]

   put 6 into tFlatArray[6]["id"]
   put 32 into tFlatArray[6]["parent_id"]
   put "electric parts" into tFlatArray[6]["name"]

   put 7 into tFlatArray[7]["id"]
   put 32 into tFlatArray[7]["parent_id"]
   put "hydraulic Parts" into tFlatArray[7]["name"]

   put 8 into tFlatArray[8]["id"]
   put 32 into tFlatArray[8]["parent_id"]
   put "other Parts" into tFlatArray[8]["name"]

   put 9 into tFlatArray[9]["id"]
   put 32 into tFlatArray[9]["parent_id"]
   put "more Parts" into tFlatArray[9]["name"]

   put 10 into tFlatArray[10]["id"]
   put 9 into tFlatArray[10]["parent_id"]
   put "Spareparts Milwaukee" into tFlatArray[10]["name"]

   put 32 into tFlatArray[32]["id"]
   put 0 into tFlatArray[32]["parent_id"]
   put "Parts" into tFlatArray[10]["name"]

   local tStructuredTree
   put buildTree(tFlatArray) into tStructuredTree
end mouseUp