Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

I am trying to implement a data structure that accomplishes the following:

Root - A

Child B1 B2

Child C1 C2 C3 C4 C5

Child D1 D2 D3 D4 D5 D6 D7

A has B1 and B2 as children

B1 has C1, C2 and C3 as children

B2 has C4, C5 as children

C1 has D1 and D2 as children

C2 has D3 as child

C4 has D4, D5 and D6 as children

C5 has D7 as child.

I am using an SQL database to implement this. Note that the number of children in each node is not specific. It could be 1 to n. I would like some souce code to implement this:

1. I want to be able to find out how many children each node has up to x generations. For example:

Generation(A,2)=B1,C1,C2,C3,B2,C4,C5

Generation(B1,2)=C1,D1,D2,C2,D3

2. How many children are immediately under a specific node? For example:

Children(A) =B1,B2

Children(B1)=C1,C2,C3

3. How many children are totally under a specific node? For example:

AllChildren(B1)=C1,D1,D2,C2,D3,C3

AllChildren(B2)=C4,D4,D5,D6

AllChildren(C1)=D1,D2

I want the actual source code Interbase SQL and Delphi 5.0 code ( no controls for Enterprise, only up to Professional)

Root - A

Child B1 B2

Child C1 C2 C3 C4 C5

Child D1 D2 D3 D4 D5 D6 D7

A has B1 and B2 as children

B1 has C1, C2 and C3 as children

B2 has C4, C5 as children

C1 has D1 and D2 as children

C2 has D3 as child

C4 has D4, D5 and D6 as children

C5 has D7 as child.

I am using an SQL database to implement this. Note that the number of children in each node is not specific. It could be 1 to n. I would like some souce code to implement this:

1. I want to be able to find out how many children each node has up to x generations. For example:

Generation(A,2)=B1,C1,C2,C

Generation(B1,2)=C1,D1,D2,

2. How many children are immediately under a specific node? For example:

Children(A) =B1,B2

Children(B1)=C1,C2,C3

3. How many children are totally under a specific node? For example:

AllChildren(B1)=C1,D1,D2,C

AllChildren(B2)=C4,D4,D5,D

AllChildren(C1)=D1,D2

I want the actual source code Interbase SQL and Delphi 5.0 code ( no controls for Enterprise, only up to Professional)

could you show your datastructure?

it should like

CREATE TABLE People (

ID NUMBER(9, 0) NOT NULL,

NAME VARCHAR2(24) NOT NULL,

REMARK VARCHAR2(48),

PARENT_ID NUMBER(9, 0)

)

meikl

well, i've done a little sample-app,

its independent from the database, therefore you can use interbase or paradox or whatever.

unit db_childs_u;

interface

uses

Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,

StdCtrls, Grids, DBGrids, Db, DBTables;

type

TForm1 = class(TForm)

Table1: TTable;

DataSource1: TDataSource;

DBGrid1: TDBGrid;

Button1: TButton;

Button2: TButton;

Button3: TButton;

procedure Button1Click(Sender: TObject);

procedure Button2Click(Sender: TObject);

procedure Button3Click(Sender: TObject);

private

function CountChildren(Parent : Integer) : Integer;

{ Private declarations }

public

{ Public declarations }

end;

var

Form1: TForm1;

implementation

{$R *.DFM}

//Table-structure named People

//ID->Integer->Autoincreme

//ParentID->Integer

//Name->Char(Whateverlengt

//here can be more fields

//append as child of current selected record

procedure TForm1.Button1Click(Sender

var ID : Integer;

begin

If (Table1.State = dsBrowse) and

not (Table1.IsEmpty) then

Begin

ID := Table1.FieldByName('ID').A

Table1.Append;

Table1.FieldByName('Parent

end;

end;

//post - save changes

procedure TForm1.Button2Click(Sender

begin

if (Table1.State = dsEdit) or

(Table1.State = dsInsert) then Table1.Post;

end;

//Count children of current Record

procedure TForm1.Button3Click(Sender

begin

ShowMessage(table1.FieldBy

inttostr(CountChildren(tab

' Children and ChildCildren');

end;

//recursive counting

function TForm1.CountChildren(Paren

var

q : TQuery;

begin

Result := 0;

q := TQuery.Create(Self);

q.DatabaseName := Table1.DataBaseName;

q.SQL.Clear;

q.SQL.Add('Select * from People where ParentId = '+IntToStr(Parent));

q.Open;

q.First;

while not q.Eof do

Begin

Result := Result+CountChildren(q.Fie

q.Next;

end;

q.Close;

q.Free;

end;

//Count children of current Record

procedure TForm1.Button3Click(Sender

begin

ShowMessage(table1.FieldBy

inttostr(CountChildren(tab

' Children and ChildChildren');

end;

end.

meikl

its a solution to point 3.

other follows

(just a change in the

CountChildren-Function)

meikl

//recursive counting

function TForm1.CountChildren(Paren

var

q : TQuery;

begin

Result := 0;

q := TQuery.Create(Self);

q.DatabaseName := Table1.DataBaseName;

q.SQL.Clear;

q.SQL.Add('Select * from People where ParentId = '+IntToStr(Parent));

q.Open;

q.First;

while not q.Eof do

Begin

if MaxGenarations <> 0 then

Result := Result+CountChildren(q.Fie

Result := Result + 1;

q.Next;

end;

q.Close;

q.Free;

end;

//Count children of current Record

procedure TForm1.Button3Click(Sender

begin

//All Childs-> MaxGenerations-Param is -1

ShowMessage(table1.FieldBy

inttostr(CountChildren(tab

' Children');

//Childs of one generation->MaxGenerations

ShowMessage(table1.FieldBy

inttostr(CountChildren(tab

' Children');

//Childs of two generation->MaxGenerations

ShowMessage(table1.FieldBy

inttostr(CountChildren(tab

' Children');

end;

meikl

I provided three questions. You answered the second one. What about the first and third? I am not trying to be picky but I just want a complete solution.

Thanks.

i thought i had answered all three

based on the last version of the

function TForm1.CountChildren(Paren

//Count children of current Record

procedure TForm1.Button3Click(Sender

begin

//that call to 3.

//All Childs-> MaxGenerations-Param is -1

ShowMessage(table1.FieldBy

inttostr(CountChildren(tab

' Children');

//that call to 2.

//Childs of one generation->MaxGenerations

ShowMessage(table1.FieldBy

inttostr(CountChildren(tab

' Children');

//that call to 1.

//Childs of two generation->MaxGenerations

genetionsdepth "-1 := 1

ShowMessage(table1.FieldBy

inttostr(CountChildren(tab

' Children');

end;

because you can pass now a MaxGenerationdepth-paramat

is the paramater -1 -> all childs are counted of current record

is the paramater 0 -> one child-generation is counted of current record

is the paramater 1 -> two child-generations are counted of current record

is the paramater 2 -> three child-generations are counted of current record

is the paramater 3 -> four child-generations are counted of current record

...and so on

did i missed something ?

meikl

I tried your solution and I have a comment regarding the performance. I used it with the data I posted for the question and even with such little data I noticed that the performance was an issue. I am concerned if for example I am working with 4~5,000 records. This will be evidently noticeable. Any improvements on your code?

first, yes, it will take time as every counting or calculation (just go into the explorer->rightclick on a dir->select propertys and look how the explorer counted the byteamount of the dir)

well have done a test to all three parts with your given data and get the result in <0.5 sec for each.

yes,

there are performance-improvements possible by code:

- generally to minimize the datatransfer, the query can changed into:

q.SQL.Add('Select Id from People where ParentId = '+IntToStr(Parent));

- if the maxgenarations parameter is 0, then a codebranch can be added like

If MaxGenarations = 0 then

begin

q.SQL.Add('Select count(Id) as CId from People where ParentId = '+IntToStr(Parent));

q.Open;

if q.eof then Result := 0

else Result := q.FieldByName(CId).AsInteg

end

else . . .

and yes, there also performance-improvements (code-independent) in configuration-settings and os-settings (caching) also in the used hardware (highend)

and yes, there are also improvements on sql-based serversystems->let the server count->stored procedure/function

as you see, are there some performance influences, which are code independent, used hardware, used databasesystem, OS-Settings, used databasedriver, concurrent tasks on server/workstation, network and so on.

meikl

this will be the changes for code:

//recursive counting

function TForm1.CountChildren(Paren

var

q : TQuery;

begin

Result := 0;

q := TQuery.Create(Self);

q.DatabaseName := Table1.DataBaseName;

q.SQL.Clear;

if MaxGenarations = 0 then

begin

q.SQL.Add('Select Count(Id) as CId from People where ParentId = '+IntToStr(Parent));

q.Open;

If q.Eof then Result := 0

else Result := q.FieldByName('CId').AsInt

end

else

begin

q.SQL.Add('Select Id from People where ParentId = '+IntToStr(Parent));

q.Open;

q.First;

while not q.Eof do

Begin

// if MaxGenarations <> 0 then //never used

Result := Result+CountChildren(q.Fie

Result := Result + 1;

q.Next;

end;

end;

q.Close;

q.Free;

end;

meikl

All Courses

From novice to tech pro — start learning today.

this is just a preventive-lock to prevent that you can delete this q.

be free to reject, if my advise does

not match your needs.

meikl ;-)