Jump to content

Heirarchical Structure


Clark
 Share

Recommended Posts

Hello all

I have the feeling my brain is getting old, as I cannot figure out how to achieve the following.

I wish to display a list of folders in a treeview heirarchical structure. I am retrieving the folders from a database with the attributes of folder_id, folder_id_2 (which is the parent folder id), and the folder name (which is used for display purposes).

Firstly, here is the code for retrieving the folder structure from the database. To me, it seems inelegant, but I can't work out how to elengatize it.

$sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = 7;" ; top level defect folders
$iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns)
if $iRval <> $SQL_OK then
    Msgbox(0 + 16 +262144,"Error","Unable to populate folder listbox")
    _SQL_Close()
    Exit
EndIf
for $j = 1 to $gRows         ; for each high level folder
    $agArray[1][$j][0]=$ahArray[$j][0]   ; Folder ID
    $agArray[1][$j][1]=$ahArray[$j][1]   ; Folder name
    $agArray[1][$j][2]=7        ; Parent folder
Next
; Now we go to the second level
for $j = 1 to $gRows
    $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $agArray[1][$j][0] & ";"
    $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns)
    $agArray[2][$j][0]=$ahArray[$j][0]   ; Folder ID
    $agArray[2][$j][1]=$ahArray[$j][1]   ; Folder name
    $agArray[2][$j][2]=$agArray[1][$j][0]   ; Parent folder
Next
; Now for the third level
for $j = 1 to $gRows        
    $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $agArray[2][$j][0] & ";"
    $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns)
    $agArray[3][$j][0]=$ahArray[$j][0]   ; Folder ID
    $agArray[3][$j][1]=$ahArray[$j][1]   ; Folder name
    $agArray[3][$j][2]=$agArray[2][$j][0]   ; Parent folder
Next
; and the fourth level
for $j = 1 to $gRows        
    $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $agArray[3][$j][0] & ";"
    $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns)
    $agArray[4][$j][0]=$ahArray[$j][0]   ; Folder ID
    $agArray[4][$j][1]=$ahArray[$j][1]   ; Folder name
    $agArray[4][$j][2]=$agArray[3][$j][0]   ; Parent folder
Next
; and the Fifth level
for $j = 1 to $gRows        
    $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $agArray[4][$j][0] & ";"
    $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns)
    $agArray[5][$j][0]=$ahArray[$j][0]   ; Folder ID
    $agArray[5][$j][1]=$ahArray[$j][1]   ; Folder name
    $agArray[5][$j][2]=$agArray[4][$j][0]   ; Parent folder
Next
; and the sixth and final level
for $j = 1 to $gRows        
    $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $agArray[5][$j][0] & ";"
    $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns)
    $agArray[6][$j][0]=$ahArray[$j][0]   ; Folder ID
    $agArray[6][$j][1]=$ahArray[$j][1]   ; Folder name
    $agArray[6][$j][2]=$agArray[5][$j][0]   ; Parent folder
Next

Apart from the elegance issue, here is where I run into trouble, converting it into a treeview.

First I tried this. Yes, I know this wont work as uBound doesn't work like this. In this context I was trying to use uBound to determine the number of rows to display at each level. Obvious fail.

GUICtrlCreateButton("Folders                                                                        ",144,40,257,15)
$hTreeView = GUICtrlCreateTreeView(144, 55, 257, 106, BitOR( $TVS_HASBUTTONS, $TVS_HASLINES, $TVS_LINESATROOT, $TVS_DISABLEDRAGDROP, $TVS_CHECKBOXES))
for $j = 1 to (uBound($agArray)-1)       ; For all the rows in the array    
    $tv1=GUICtrlCreateTreeViewItem($agArray[1][$j][1], $hTreeView) ; Top level
    for $j = 1 to (UBound($agArray,3)-1)    
        $tv2=GUICtrlCreateTreeViewItem($agArray[2][$j][1], $tv1) ; Second level items
        for $j = 1 to (UBound($agArray,4)-1)
            $tv3=GUICtrlCreateTreeViewItem($agArray[3][$j][1], $tv2)  ; Third level items
            for $j = 1 to (UBound($agArray,5)-1)
                $tv4=GUICtrlCreateTreeViewItem($agArray[4][$j][1], $tv3)  ; Fourth level items
                for $j = 1 to (UBound($agArray,6)-1)
                    $tv5=GUICtrlCreateTreeViewItem($agArray[5][$j][1], $tv4)  ; Fifth level items
                        for $j = 1 to (UBound($agArray,7)-1)
                            GUICtrlCreateTreeViewItem($agArray[6][$j][1], $tv5)  ; Sixth level items
                       Next
                Next
           Next
      Next
   Next
Next

Next, I tried this, but this doesn't work either. (I haven't ran the code, I just can tell it wont work looking at it)

GUICtrlCreateButton("Folders                                                                        ",144,40,257,15)
$hTreeView = GUICtrlCreateTreeView(144, 55, 257, 106, BitOR( $TVS_HASBUTTONS, $TVS_HASLINES, $TVS_LINESATROOT, $TVS_DISABLEDRAGDROP, $TVS_CHECKBOXES))
for $j = 1 to (uBound($agArray)-1)       ; For all the rows in the array    
    $tv1=GUICtrlCreateTreeViewItem($agArray[1][$j][1], $hTreeView) ; Top level
    $Parent=$agArray[1][$j][0]
    for $j = 1 to (uBound($agArray)-1)      ; Check all rows to see if its a child
        if $agArray[2][$j][2]=$Parent Then
            $tv2=GUICtrlCreateTreeViewItem($agArray[1][$j][1], $hTreeView)
       EndIf
        etc etc for each level
    Next
Next

Is anyone able to offer any suggestions? I've been on this two days and not really getting anywhere.

Edited by Clark
Link to comment
Share on other sites

  • Moderators

Clark,

Can you please post an example of the $iRval array and a rough diagram of what you want the final TreeView to look like. Trying to code something without those is far too likely to be wasted time as it probably will not meet the real-world conditions with which you are working. :)

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

You could do this with a recursive function. I assume your data will never have more than a dozen of levels, so recursion errors should not be a problem.

Here is an example, but it's untested and you'll have to add the SQL stuff and GUI first:

$hTreeView = GUICtrlCreateTreeView(144, 55, 257, 106, BitOR( $TVS_HASBUTTONS, $TVS_HASLINES, $TVS_LINESATROOT, $TVS_DISABLEDRAGDROP, $TVS_CHECKBOXES))
_FillTree_Rec($hTreeView)
Func _FillTree_Rec($hParent, $iFID = 7)
    Local $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $iFID & ";"  ;query to select children of the passed folder ID
    Local $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns) ;fetch to array
    ;it's probably best to check if any values are returned here and return from the function if not. (I wasn't sure if there is a return count in $iRval[0][0])
    Local $hItem
    For $i = 1 To UBound($iRval) -1
        $hItem=GUICtrlCreateTreeViewItem($iRval[$i][1], $hParent) ;create item for each child
        _FillTree_Rec($hItem, $iRval[$i][0]) ;run again with the last child as parent.
    Next
EndFunc
Link to comment
Share on other sites

Thanks guys

I shall subject my brain to another day of torture whilst I work through Tvern's solution. If I can't get anywhere I will post back with more info as per Melba's request.

I'm sure when I was younger my brain could have done this no problems. :)

regards

Clark

Link to comment
Share on other sites

Right, well after some experiment and more brain torture, I conclude there is a problem with the recursive solution.

Here is the code edited:

Func _FillTree_Rec($hParent, $iFID)
    Local $sSQL="SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $iFID & " AND folder_id <> 7;" ;query to select children of the passed folder ID
    Local $iRval= _Sql_GetTable2D($hDBHandle,$sSQL,$ahArray,$gRows,$gColumns)  ;fetch to array
    if $iRval = 0 then
        Local $hItem
        For $i = 1 To $gRows
            $hItem=GUICtrlCreateTreeViewItem($ahArray[$i][1], $hParent)   ;create item for each child
            _FillTree_Rec($hItem, $ahArray[$i][0])       ;run again with the last child as parent.
        Next
    EndIf
EndFunc

The problem seems to lie in the fact that $ahArray needs to be a global variable in order to query the Treeview later, and have it return results. Due to this the recursive nature of the function means that it is always being overwritten.

If it wasn't for this I'm sure this would work, but as it is I'm still stuck.

Link to comment
Share on other sites

Hmm I see I was pretty sloppy there with not declaring variables and using the wrong return value from _Sql_GetTable2D.

If you want to keep an array that contains all elements of the treeview it's probably best to copy the content over to another array. (You can use a global array for this, or a local one and pass it ByRef)

The problem with this approach is that ReDim is a slow function, so you don't want to add the items one by one and redim for each one.

Another option could be to copy each version of $ahArray into another array, but putting arrays into arrays is frowned upon and with good reason, although they have their uses.

I'm not sure how many tree nodes you will have, but this should reduce the amount of ReDims to once per parent node Hopefully that would give an acceptable result.

It's getting a little hard to write these examples without testing them, so I hope it's not too far off.

Global $aTreeViewItems[1][2]
;I'm using [0][0] and [0][1] to keep track of array size and last filled row.
;I should really set them to a known value for this, but as I know the value to be 0 at this point I'm not going to.
Global $hTreeView = GUICtrlCreateTreeView(144, 55, 257, 106, BitOR($TVS_HASBUTTONS, $TVS_HASLINES, $TVS_LINESATROOT, $TVS_DISABLEDRAGDROP, $TVS_CHECKBOXES))
_FillTree_Rec($hTreeView,7)
Func _FillTree_Rec($hParent, $iFID)
    Local $ahArray, $gRows, $gColumns, $hItem
    Local $sSQL = "SELECT folder_id,folder_name FROM table_folder where folder_id_2 = " & $iFID & " AND folder_id <> 7;"
    Local $iRval = _Sql_GetTable2D($hDBHandle, $sSQL, $ahArray, $gRows, $gColumns)
    If $iRval Then Return
    $aTreeViewItems[0][0] += $gRows ;increase the row count with the number of returned rows.
    ReDim $aTreeViewItems[$aTreeViewItems[0][0]+1][2] ;resize the array.
    For $i = 1 To $gRows
        $aTreeViewItems[0][1] += 1 ;copy data to the first empty row.
        $aTreeViewItems[$aTreeViewItems[0][1]][0] = $ahArray[$i][0]
        $aTreeViewItems[$aTreeViewItems[0][1]][1] = $ahArray[$i][1]
        $hItem = GUICtrlCreateTreeViewItem($ahArray[$i][1], $hParent) ;You might want to store the handle in $aTreeViewItems here, just add a 3rd collumn if needed.
        _FillTree_Rec($hItem, $ahArray[$i][0])
    Next
EndFunc

Since you seem to want to have all treeview items in one array, wouldn't it make more sense to read the entire array with a single Sql query, rather than merging multiple ones?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...