Self Joined Tables

Posts   
 
    
simon831
User
Posts: 152
Joined: 19-Jan-2006
# Posted on: 10-Feb-2010 15:19:45   

I have a Folder table, that represents a folder structure on disc with a possible infinite number of levels. I am trying to Prefetch all sub-folders, but I get the first folder, and the subfolders of it, but no levels below that. How can I do this?



Folder table
FolderId
Name
ParentId

            EntityCollection<FolderEntity> folders = GetFolders(myFolder);

            foreach (FolderEntity folder in folders)
            {
                OutputFolder(folder);
            }



        private void OutputFolder(FolderEntity folder)
        {
            Response.Write(folder.Name);

            foreach (FolderEntity subfolder in folder.Folder_)
            {
                OutputFolder(subfolder);
            }   
        }


        public static EntityCollection<FolderEntity> GetFolders(Guid folderId)
        {
            EntityCollection<FolderEntity> folders = new EntityCollection<FolderEntity>(new FolderEntityFactory());
            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.PredicateExpression.Add(FolderFields.FolderId == folderId);
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.FolderEntity)
                                              {
                                                  FolderEntity.PrefetchPathFolder,  //to the ParentFolder
                                                  FolderEntity.PrefetchPathFolder_, //EntityCollection of subfolders
                                              };

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(folders, bucket, prefetchPath);
            }
            return folders;
        }

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 10-Feb-2010 15:29:43   

Fetch the entire table and build a dictionary to build parent-children relations.

Code sample can be found here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=11583

rdhatch
User
Posts: 198
Joined: 03-Nov-2007
# Posted on: 10-Feb-2010 16:58:18   

Hi Simon -

Just a side note on the prefetches. We've been using Left/Right IDs for Trees in our Databases, with good results. It's efficient for reading, because you know exactly where you are in the tree & can easily select the n-level children/grandchildren of any given node. In other words - you can fetch what you need - without prefetching the entire table. The disadvantage is inserting nodes causes an update on many other nodes.

http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

Just a thought for you. Good luck!

Ryan

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 10-Feb-2010 17:39:23   

Another option which I usually use, is to fetch the root nodes and their direct children only. Then when a user selects a child node, you should go and fetch their direct children, and so on. (i.e. load on demand).

simon831
User
Posts: 152
Joined: 19-Jan-2006
# Posted on: 10-Feb-2010 18:05:34   

Many thanks.....