Programming Logic

[jmhecker]

Okay, so I have a database that is constructed as so:

CREATE TABLE Categories (
  category_id         integer PRIMARY KEY AUTOINCREMENT,
  content_id          int,
  parent_category_id  int,
  category_name       varchar(100),
  pinned              int,
  pincode             int
);

That DB will have the names of root and child nodes which will be used to populate a treeview. What will be done is the code will run a query such as this on the DB:

SELECT category_id, category_name FROM Categories WHERE parent_category_id=0;

That will grab all of the category information I need (at this point in time) from the DB. It will basically return all of the 'root nodes' for my category tree.

Now that I have the root nodes, I need to use each of those (referencing the category_id) to go through the DB again to select any child nodes.

SELECT category_id, category_name FROM Categories WHERE parent_category_id=%VAR HERE WITH ID%;

That works fine...it goes ahead and grabs the listing of child nodes for each root node. But, this is where the trick comes into play.

I once again need to repeat the 2nd SQL statement for each of the child nodes returned the first time I ran the 2nd query...then I need to do it again for the 2nd list returned, and then the 3rd, and the 4th, etc. Depending on the complexity of the tree, I could have to do that hundreds of times per each root node.

I am running into a problem there. I can not seem to logically tackle this without nesting hundreds upon hundreds of for/next, or if/then, or while loops.

Can anyone offer some ideas? Perhaps some pseudo code or something to point me in the right direction?

 

[JohnH]

Recursion (computer science) - Wikipedia, the free encyclopedia

Pseudo code sample of a recursive function (that calls itself):

Sub LoadCatNodes(parId As Integer, treeNodes As TreeNodeCollection)
   Dim dbNodes = db.GetIds(parId)
   For Each dbNode In dbNodes
      Dim treeNode = treeNodes.Add(dbNode.Name)
      LoadCatNodes(dbNode.Id, treeNode.Nodes)
   Next
End Sub

first call would be:

LoadCatNodes(0, Me.CatTreeView.Nodes)

 

[jmhecker]

I have no clue why recursion never came into mind. I sat here for hours looking at it trying to figure a solution. Recursion gets mentioned and I am done in 5 minutes, heh.

The completed code for what I was working on is:

        Public Sub PopulateCategoryTree()
            CategoryTree.Nodes.Clear()
            Dim RootNode As New TreeNode("My Collection")
            RootNode.Tag = "0"
            GetSubCategories(0, RootNode)
            CategoryTree.Nodes.Add(RootNode)
        End Sub
        Private Sub GetSubCategories(ByVal category_id As String, ByVal _node As TreeNode)
            Dim db As New db
            Dim item As String() = Nothing
            Dim SubCategories As List(Of String) = db.GetSubCategories(category_id)
            For Each c As String In SubCategories
                item = Split(c, "|")
                Dim Node As New TreeNode(item(1))
                Node.Name = item(0)
                GetSubCategories(item(0), Node)
                _node.Nodes.Add(Node)
            Next
        End Sub

I thank you much JohnH, it was a great help

 

[cjard]

If you were using Oracle as your DB< you could have run an SQL that looked like:

SELECT
  *
FROM
  hierarc_table
START WITH
  parent_node_id = 0
CONNECT BY 
  PRIOR node_id = parent_node_id
WHERE
  level <= 4 --to limit recursion to 4 levels