Programming Logic (1 Viewer)

[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?

 

[DieBagger]

Maybe I'm overlooking something here but to me this sounds like what you're searching for is recursion...

Let me try if I can write some pseudo code:

public void Main()
{//entry point
    TreeNode rootNode = new TreeNode("I am root");
    GetSubNodes(0, rootNode);
}

public void GetSubNodes(int _id, TreeNode _node)
{
   List<Category> subCategories = GET_SUB_FROM_SQL(_id);//get the sub-categories from sql

   foreach(Category c in subCategories)
   {
       TreeNode node = new TreeNode(c.category_name);
       node.Tag = c;
       GetSubNodes(c.category_id, node);//this is the recursion
       _node.SubNodes.Add(c);
    }
}

Fyi it's already late and I'm sure there's something wrong with the code but it should give you the idea

 

[jmhecker]

Maybe I'm overlooking something here but to me this sounds like what you're searching for is recursion...

Let me try if I can write some pseudo code:

public void Main()
{//entry point
    TreeNode rootNode = new TreeNode("I am root");
    GetSubNodes(0, rootNode);
}

public void GetSubNodes(int _id, TreeNode _node)
{
   List<Category> subCategories = GET_SUB_FROM_SQL(_id);//get the sub-categories from sql

   foreach(Category c in subCategories)
   {
       TreeNode node = new TreeNode(c.category_name);
       node.Tag = c;
       GetSubNodes(c.category_id, node);//this is the recursion
       _node.SubNodes.Add(c);
    }
}

Fyi it's already late and I'm sure there's something wrong with the code but it should give you the idea

Call me a moron, why I didn't think of recursion as the solution to this is beyond me. I have been staring at my VS.Net IDE window for hours here, trying to figure something out. This is one of those "I cant believe I overlooked that" moments.

I am going to put a post-it note on my monitor that simply reads "Occam's Razor". (If you are not sure what Occam's Razor is, it states (in its most generic terms) that the easiest solution is typically the correct solution.)

Thank you for your push in the right direction DieBagger. I will get back to work on this now

Maybe I'm overlooking something here but to me this sounds like what you're searching for is recursion...

Let me try if I can write some pseudo code:

public void Main()
{//entry point
    TreeNode rootNode = new TreeNode("I am root");
    GetSubNodes(0, rootNode);
}

public void GetSubNodes(int _id, TreeNode _node)
{
   List<Category> subCategories = GET_SUB_FROM_SQL(_id);//get the sub-categories from sql

   foreach(Category c in subCategories)
   {
       TreeNode node = new TreeNode(c.category_name);
       node.Tag = c;
       GetSubNodes(c.category_id, node);//this is the recursion
       _node.SubNodes.Add(c);
    }
}

Fyi it's already late and I'm sure there's something wrong with the code but it should give you the idea

I took your code and ported it to VB.Net (my language of choice for this project), and it is as follows (I had to make a few corrections to yours to make it work, but the resulting working code is as follows:

        Public Sub PopulateCategoryTree()
            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

 

[joz]

Ever heard of a left right sql tree?
It is more complicated but is first of all faster (only one query needed for what you want) and it is more flexible. When you want to start moving nodes around or deleting nodes the left right sql tree is your friend.