Build a Table Tree from Records to Objects

Problem/Question/Abstract:

In a relational database like the well known IBLocal you find a table DEPARTMENT which has a self join on her own, means there is a tree inside with a Parent/Child structure.

Answer:

In a relational database like the well known IBLocal you find a table DEPARTMENT which has a  self join on her own, means there is a tree inside with a Parent/Child structure:

DEPT_NO       DEPARTMENT                 HEAD_DEPT
100           Sales and Marketing        000
120           European Headquarters      100
121           Field Office Swiss         120
and so on...

This article is aimed mainly at people interested in trees and recurcions who need to find their  way around self referencing system and get going with a object-visualisation in a tree. Furthermore it's an example for the Composite Design Pattern.
First we need a startprocedure mainly to call the query, build the tree with records in objects and   show the tree at last:

procedure TForm1.btnTreeSelfClick(Sender: TObject);
var
dimlist: TStringList;
begin
dimlist := TStringlist.create;
datdepartment := TBusinessObj.Create(nil);
try
if datDepartment.open_recursiveQuery then
with TDims.create(nil, DimList) do
begin
FillTree(treeview1, nil);
Free;
end;
treeview1.FullExpand;
btnLTree.visible := true;
finally;
dimlist.Free;
datDepartment.Free;
end;
end;

The records in the query must follow one condition [child# > parent#], means a child like  "FieldOffice Swiss" has a child# 121 so the parent it belongs is 120. So we call the query and open the dataset:

function TBusinessObj.open_recursiveQuery: boolean;
begin
result := false;
with qryselfTree do
begin
try
SQL.Clear;
SQL.add('SELECT dept_no, department, location, head_dept' +
' FROM department ORDER BY dept_no');
open;
result := true;
except on EDataBaseError do
showmessage('data not found');
end;
end;
end;

Next we implement a class which holds at runtime the whole tree table in objects. Every object is a stored record from the table DEPARTMENT with the attributes you want to publish or  manipulate at runtime. Grace the members FParent, FChild the whole table is chained in objects  with a top level object, in our case the CORPORATE HEADQUARTERS. This top level object doesn't have a parent, the parent is NIL.

TDims = class(TObject)
private
FstrDimArt: string;
FstrDimArtBez: string;
FParent: TDims;
FChilds: TList;
public
constructor create(Sender: TDims; myRegister: TStringList);
destructor Destroy; override;
procedure FillTree(aOl: TTreeview; xnode: TTreenode);
function IsDimartInChilds(DimArt: string): Boolean;
property DimArtBez: string read FstrDimArtBez;
property DimArt: string read FstrDimArt;
property treechilds: TList read FChilds;
end;

Now comes the real power part, a recursive constructor which collects all records to build the  tree in memory. When a parent like "Sales and Marketing" finds some childs like "European  Headquarters" it creates new objects in a recursion and adds the object to the list:

FChilds.Add(TDims.create(self, myRegister));

Recursions aren't dark chapter by opening in Delphi the debug windows "Call Stack and Local  Variables" you'll learn a lot. When a function name appears anywhere else in a statement block,  the compiler interprets it as a recursive call to the function itself.
The constructor has been used to recursively include another objects. But in every tree an object  without childs terminates without having cycles in them. The last level of a tree is almost the  deepness of recursions.
By the way do you know the explanation of a recursion in a "well behaved" dictionary:

Recursion: See under Recursion ;)

Let jokes aside, here it is:
A programming technique in which a subroutine calls itself. Use care to ensure that a recursion eventually exits. Otherwise, an infinite recursion will cause a stack fault.

constructor TDims.create(Sender: TDims; myRegister: TStringList);
var
bmAkt: TBookmark;
begin
inherited Create;
with datDepartment.qrySelfTree do
begin
FstrDimArt := fieldByName('DEPT_NO').AsString;
FstrDimArtBez := fieldbyName('DEPARTMENT').AsString;
myRegister.AddObject(Format('%10s', [FstrDimArt]), self);
FChilds := TList.Create;
FParent := Sender;
bmAkt := GetBookmark;
if Locate('DEPT_NO', FstrDimArt, []) then
while not (EOF) do
begin
if (fieldByName('HEAD_DEPT').Asstring = FstrDimArt) then
FChilds.Add(TDims.create(self, myRegister));
Next;
end;
GotoBookmark(bmAkt);
FreeBookmark(bmAkt);
end;
end;

destructor TDims.Destroy;
var
i: integer;
begin
if FChilds <> nil then
for i := 0 to FChilds.Count - 1 do
TDims(FChilds[i]).Free;
FChilds.Free;
inherited Destroy;
end;

Now comes the last part, the most efficient way to represent the tabel tree in a view. TTreeView represents a window that displays a hierarchical list of items, such as the headings in a document, the entries in an index, or the files and directories on a disk.
Use TTreeView to add an expanding and contracting outline to a form. Each node in a tree view  control consists of a label and a number of optional bitmapped images. Each node can have a  list of subnodes associated with it. By clicking on a node, the user can expand or collapse the associated list of subnodes.
At run-time nodes can be added and inserted by using the TTreeNodes methods AddChildFirst, AddChild, AddChildObjectFirst, AddChildObject, AddFirst, Add, AddObjectFirst, AddObject and Insert. We only need AddChild:

procedure TDims.FillTree(aOl: TTreeview; xnode: TTreenode);
var
i: integer;
dbcontent: string[255];
begin
dbcontent := dimart + ' ' + dimartbez;
xnode := aOl.items.addchild(xnode, dbcontent);
for i := 0 to treechilds.Count - 1 do
TDims(treechilds.items[i]).FillTree(aOl, xnode);
end;