Displaying a database table as a tree

A few months ago, I added to the Occupational Psychologist's management program a module which tracks calls to various clients. This is a simplified version of a Customer Management System (CMS) and became necessary when two of the OP's staff left without leaving proper documentation of their work. A single call holds the customer's name (well, the customer's id number), the subject of the call, the contact person, the text of the call and the date on which the call was made. There then arose the need to create a followup call which is based on a previously existing call; such followup calls would be stored in the same table as the original call, but obviously there need be a way of showing the connection between the two.

The logical way of doing this would be to add a 'predecessor' field to the calls table; a new call would store 0 in this field whereas a followup call would store the id of the predecessor call. For reasons which escape me now, I elected not to do this. Despite this, I will write here as if there is a 'predecessor' field in the calls table.

Use of this field is the basis of the threaded view of calls. A thread will start with one call (which has been termed the 'original' call above), then continue to a second call (the 'followup' call) and so on. The original call may have two threads arising from it, and a followup call may have its own followup call. In order to show the call hierarchy correctly, the calls have to be displayed in a tree view.

Let us assume that there are the following calls: A0 is the first call in a thread. From this call, two successor calls were made, A1 and A2. There is a successor call from A1 denoted as call A11. A separate thread was opened for the same customer, with calls B0, B1 and B11. Crudely drawn in an HTML table, the hierarchy looks like this:

A0	A1	A11
	A2
B0	B1	B11

Here is a partial view of how the calls table might look:

# node	subject	predecessor
1	A0	0
2	B0	0
3	B1	2
4	A1	1
5	A2	1
6	B11	3
7	A11	4

Originally I used a fairly complicated algorithm, but looking at the problem now, I see that I can draw the tree with a much simpler algorithm which requires only one access to the database.

select id, curdate, predecessor
from calls
where customer = :p1
order by id

The resulting dataset will be the same as the table drawn above (except for the fact that I am retrieving each call's date and not its subject). Here is suitable code to draw the tree based on this dataset:

with qGetCalls do
 begin
  close;
  params[0].asinteger:= custnum;
  open;
  try;
   tv.items.BeginUpdate;
   while not eof do
    begin
     anchor:= fieldbyname ('predecessor').asinteger;
     if anchor = 0
      then node:= nil
      else node:= FindFatherNode (anchor);
     AddChildObject (node, fieldbyname ('curdate').asstring, pointer (fieldbyname ('id').asinteger))
     next
    end;
  finally;
   tv.items.EndUpdate;
  end;
end;

Function TShowCallsTree.FindParentNode (father: longint): ttreenode;
var
 node: ttreenode;

begin
 node:= tv.nodes[0];
 while (node <> nil) and (longint (node.data) <> father) do node:= node.GetNext;
 result:= node;
end;

The above code is unchecked but looks as if it should do the work correctly. The hack part of this code uses the data property of each tree node. Nominally, this is a pointer which points to a dynamically created record holding some form of data connected with the node (indeed, I saw code a few days ago which worked this way, creating a pointer to a record with new and then saving the pointer in the data property), but when the data to be stored is an integer, it can be stored directly in the data property, via a type cast, and of course can be retrieved directly. 

The FindParentNode code as written is very simple - and possibly very slow, as every time it is called, it has to start from the first node in the tree. At the moment, I think that each customer has less than ten calls (meaning ten nodes in the tree), but in a year's time, this code is going to be slow and will probably need some form of optimisation. It turns out that there is a GetPrev method, which returns the previous node in the tree; it should be quicker to start always at the end of the tree and move backwards. The final node in the tree is always known as this is returned by the AddChildObject method (I neglected to save this value in the code above). This assumes, though, that the speed of GetNext/GetPrev are similar; even so, a 'backwards' approach would require checking fewer nodes.