SQL and Delphi: recursive mechanism for creating a tree from a table

I suggest you not load the whole tree at once, why should you ? no man can view at the moment a thousand of items. And it can be long and your program would look frozen. And it makes a huge load pike over network and server.

You better use VirtualTreeView approach, where each item loads its children items on request. That would require one parametrized prepared query like

 Select ID, Title, This, That from TREE where Parent_ID = :ID

• http://code.google.com/p/virtual-treeview/
• http://www.lischke-online.de/index.php/controls/virtual-treeview-gallery
• Tree-like Datastructure (for use with VirtualTreeview)
• VirtualStringTree Correct/recommended use

And yes, don't make new SQL text for every item. It is both dangerous and slow (you need to drop all the data gathered for an old request and parse the new one)

You should make one parametrized query, Prepare it and just do close/change param values/open.

See reasons and Delphi sample at http://bobby-tables.com/

One example of "load it all at once altogether" rush is at dynamically create popup menu tree from sql server table in Delphi - though i don't think rush is good approach for more or less large trees.

Note about this approach: you fill in root elements, then you find one way or another to fill in elements, not filled yet, but already referenced by others until there is no such elements at last.

You can do it recursively of course, traversing tree to its ends - but that would ask for many nested database queries.

You may make a recursive SQL request, but it would probably be very server-dependent and RDBMS engines usually impose their limits on recursion depth.

An approach maybe slightly worse on tree control but more clean and easier on RDBMS would be to make a dedicated TQueue of just added tree items. After you load some element - initially all root ones - you remember it in the queue. Then you remove one by another from the queue and fill in (load and enqueue) its children. Until the queue gets empty.

I like using a hash table to create an index of all nodes indexed by keyID and use this to build the tree.It requires 2 passes of the table. The first pass creates a root tree node for each recordand adds a hash entry of keyID against tree node. the second pass walks the table looking up the parentId in the hash. If it finds it, then it moves the current node under the parent node otherwise ignores it. At the end of the second pass, you have the complete tree built.

    var i,imax,ikey,iParent : integer;        aNode,aParentNode : TTreeNode;        aData : TMyData;        aContainer : TSparseObjectArray; // cDataStructs , delphi fundamentals        aNodeIndex : TSparseObjectArray; // delphi 7    begin      try        aContainer := TSparseObjectArray.Create(true);        aNodeIndex := TSparseObjectArray.Create(False);        imax := 10000;        // create test data;        for i := 1 to imax do        begin          aData := TMyData.Create;          aData.iKey := i;          aData.iParent := Random(imax); // random parent          aData.Data := 'I:' + IntToStr(aData.iKey);          aContainer.Item[i] := aData;        end;        tv1.Items.Clear;        tv1.Items.BeginUpdate;        // build tree        // First Pass - build root tree nodes and create cross ref. index        for i := 1 to imax do        begin          aData := TMYData(aContainer.Item[i]);          aNode := tv1.Items.AddChild(nil,aData.Data);          aNodeIndex.Item[aData.iKey] := aNode;        end;        // Second Pass - find parent node using index and move node        for i := 1 to imax do        begin          aData := TMYData(aContainer.Item[i]);          aNode := TTreeNode(aNodeIndex.Item[aData.iKey]);          if aNodeIndex.HasItem(aData.iparent)          then begin                 aParentNode := TTreeNode(aNodeIndex.Item[aData.iparent]);                 aNode.MoveTo(aParentNode,naAddChild);               end;        end;        tv1.Items.EndUpdate;        tv1.Select( tv1.Items.GetFirstNode);      finally        aContainer.Free;        aNodeIndex.free;      end;  end;