how to show parent category in delphi 2010?

Question

OK, I'm using Embarcadero Delphi 2010. Currently I'm using an Access database file named flib.mdb within that file, I make a table named MCategory which has 4 (four) columns named: codecategory as Text and Primary Key, parentcategory as Text, category as Text, notes as Text.

For the Database connection, I use ADOConnection. For the query I use ADOQuery. For the table I use ADOTable.

For the code is auto generated with prefix, but for the example I use regular number as string.

There is only one ROOT : codecategory="0" // parentcategory="" // category="ROOT" not allowed to add another ROOT ( the one with empty parentcategory )

My question is how to remasking all of the parentcategory like the picture below ? And how to view it on DBGrid ?

parent category masking

Should I use recursive ? Is there any simple method to do so ?

And also source code in Delphi 2010 please.... ^^

I would prefer to do this job on the server side, not to fetch all categories and then recursively iterate them to build the path. Try to read [`this article`](http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/). It's for MySQL, but there are useful information how to design hierarchical database structure including how to get the tree path you want. Much more faster will also be to use codecategory and parentcategory as NUMBER not TEXT. — TLama, Apr 24 '12 at 12:18
well, unfortunately there is no need client-server on this one and it must use Access / mdb — Galvion, Apr 24 '12 at 19:16
I know what you mean, but believe or not, you are still accessing a database engine (let's call it server side) even if the database is just a local file. But never mind, if you want TEXT field as a foreign key, follow that way ;-) — TLama, Apr 25 '12 at 10:22

score 0 · Accepted Answer · answered Apr 24 '12 at 10:26

You can convert all record of dataset to TStringList and Sorting it.

in interface section declare this :

TForm1=class(...)
  RecordList:TStringList;
  constructor Create(AOwner:TComponent);override;
 destructor Destroy;override; 

end;
TCategoryItem=class
public:
   CategoryTitle:string;
   Id,ParentId:Variant;
end;

in implementation section :

constructor TForm1.Create(AOwner: TComponent);  
begin
  inherited ;
  RecordList:=TstringList.create(true);  //  use this code in FormCreate  also


end;
 destructor TForm1.Destroy;
    begin
      ...
   RecordList.Free;//  use this code in FormDestory  also

    end;

procedure TForm1.AdoQuery1AfterOpen(DataSet:TDataSet);
var
  Item,RootItem:TCategoryItem;
begin
  DataSet.First;
  DataSet.DisableControls;

  try
   (*add abstract root item to RecordList if needed 
     RootItem:=TCategoryItem.Create;
     RootItem.CategoryTitle:='ROOT';
     RecordList.AddObject('',RootItem);*)
    while not DataSet.Eof do
    begin
      Item:=TCategoryItem.Create;
      Item.CategoryTitle:=DataSet['Cateogory'];
      Item.Id:=DataSet['CodeCategory'];
      Item.ParentId:=DataSet['ParentCategory'];
      RecordList.AddObject(VarToStr(Item.Id),Item);
      DataSet.Next;
    end;
  finally
    RecordList.Sort; // for use find(binary search)
    DataSet.EnableControls;
    DataSet.First;
  end;
end;
procedure TForm1.OnGetFieldText(Sender: TField; var Text: string;
  DisplayText: Boolean);
var 
  Idx:Integer;
  ParentValue:Variant;
  Item:TCategoryItem;
  Texts:TStringList;
begin
  ParentValue:=Sender.Value;
  Texts:=TStringList.create;
  try
    while RecordList.Find(varToStr(ParentValue),Idx) do
    begin
      Item:=RecordList.Objects[Idx] as TCategoryItem; 
      Texts.Insert(0,Item.CategoryTitle); 
      ParentValue:=Item.ParentId;
    end;
    Texts.Delimiter:='>';
    Text:=Texts.DelimitedText;
  finally
    Texts.Free;
  end;


end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOQuery1.FieldByName('parentcategory').OnGetText := OnGetFieldText;
  ADOQuery1.Refresh;
end;

You might use `OnCreate` and `OnDestroy` events instead of overriding constructor and destructor of the form. Also `TField.OnGetText` is used when you want to modify the text to be displayed for one field, not for dataset calculations; this would be the task for `TDataset.OnCalcFields`, but still I would prefer to do this on the DB engine side, not to fetch all categories and iterate recursively for each field (it might be very inefficient if you consider the huge amount of data). — TLama, Apr 24 '12 at 12:27
And forgot to mention, OP uses Delphi 2010, so why not to use e.g. `TDictionary` instead of `TStringList`. — TLama, Apr 24 '12 at 18:52
Sorted TStringList is faster than TDictionary.Because Find Method in Sorted A StringList use binary search but TDictionary use hash search. — MajidTaheri, Apr 24 '12 at 19:54
Also don't forget that if you'll select limited count of categories (`SELECT..WHERE`) you will need to fetch all categories before, because how would you find those parents when they are not fetched by the initial select ? — TLama, Apr 25 '12 at 10:26

how to show parent category in delphi 2010?

1 Answers1