Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Self referencing hierarchy
 

Pages: 1
LLBLGen Pro Runtime Framework
Self referencing hierarchy
Page:1/1 

  Print all messages in this thread  
Poster Message
aazzihh
User



Location:

Joined on:
15-Nov-2005 18:38:13
Posted:
6 posts
# Posted on: 15-Nov-2005 18:50:12.  
How can I setup a self-referencing hierarchy with LLBLGen? Here is my table schema:

Table: SiteMap (
ID int Primary Key,
Field1,
Field2,
ParentID int null
)


Basically a row in SiteMap with ParentID=null is the root of the hierarchy. All other rows will have a value in ParentID which will point to the parent record (via the parent's ID field). i.e. ID to ParentID is a 1:n relationship.

What is the best way to traverse such a hierarchy from top down with LLBLGen Pro? Can I retrieve a collection of child entities from the parent entity?
  Top
aazzihh
User



Location:

Joined on:
15-Nov-2005 18:38:13
Posted:
6 posts
# Posted on: 15-Nov-2005 19:37:57.  
Forgot to mention, I am using the Adapter scenario.

I want to understand how to set this up in the designer because currently by creating the Relationship I am not getting the code I was expecting. I get these 2 methods:
I cant see this retrieving the child entity collection from the GetDependingRelatedEntities().

Code:

/// <summary> Gets a collection of related entities referenced by this entity which depend on this entity (this entity is the PK side of their FK fields). These entities will have to be persisted after this entity during a recursive save.</summary>
/// <returns>Collection with 0 or more IEntity2 objects, referenced by this entity</returns>
public override IEntityCollection2 GetDependingRelatedEntities()
        {
            EntityCollection toReturn = new EntityCollection();

            return toReturn;
        }

/// <summary> Gets a collection of related entities referenced by this entity which this entity depends on (this entity is the FK side of their PK fields). These
        /// entities will have to be persisted before this entity during a recursive save.</summary>
        /// <returns>Collection with 0 or more IEntity2 objects, referenced by this entity</returns>
public override IEntityCollection2 GetDependentRelatedEntities()
        {
            EntityCollection toReturn = new EntityCollection();
            if(_siteMap!=null)
            {
                toReturn.Add(_siteMap);
            }

            return toReturn;
        }


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37375 posts
# Posted on: 16-Nov-2005 12:31:19.  
GetDependingRelatedEntities is a framework method for the recursive save method, you should use the generated fields mapped on relation to retrieve the related entities.

Though with such a hierarchy, it's easier:
- fetch all entities, sort on ParentID asc
- create a hashtable and use it for PK - NodeObject relations
- walk the collection once, create for each entity a new tree node (or whatever hierarchy object you want to use the hierarchy for) and store the relation in the hashtable. The node to store the new node under is found by using parentid as index in the hashtable.

It's not easy to fetch it in 1 query, as it's not definable in 1 SELECT statement.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
sami
User



Location:
Hellsinki, Finland
Joined on:
28-Oct-2005 09:44:08
Posted:
93 posts
# Posted on: 12-Jan-2006 14:30:02.  
Should not it be possible to handle hierarchical queries like this with a single sql statement using common table expressions, available in sql server 2005?

Check out: http://www.theserverside.net/articles/showarticle.tss?id=HeirarchicalQueries

What about supporting this in future version of llblgen pro?


http://www.visualcomponents.com
See for yourself!
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14464 posts
# Posted on: 12-Jan-2006 15:03:59.  
It might be too expensive to fetch all the hierarchy at once.

Though you may fetch the root entity along with its direct children and the next level.... up to the level you want by using PrefetchPaths. (This is also expensive), when I faced this before I used to fetch 2 levels deep at a time.
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37375 posts
# Posted on: 12-Jan-2006 15:32:39.  
Indeed. It might be more cheaper to fetch all entities in an entity collection and then build the hierarchy using 2 hashtables which can be done on O(n)

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
alexdresko
User



Location:
Greenville, SC
Joined on:
08-Jun-2004 17:30:02
Posted:
336 posts
# Posted on: 12-Jan-2006 23:21:59.  
sami wrote:
Should not it be possible to handle hierarchical queries like this with a single sql statement using common table expressions, available in sql server 2005?

Check out: http://www.theserverside.net/articles/showarticle.tss?id=HeirarchicalQueries

What about supporting this in future version of llblgen pro?

I aggree, CTE's should be useful in making this happen in the new version. It should be less "expensive" than reading the entire collection and then wiring it up with hashtables as Frans suggested.
I'm not a player, I just code a lot.   Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37375 posts
# Posted on: 13-Jan-2006 08:33:52.  
alexdresko wrote:
sami wrote:
Should not it be possible to handle hierarchical queries like this with a single sql statement using common table expressions, available in sql server 2005?

Check out: http://www.theserverside.net/articles/showarticle.tss?id=HeirarchicalQueries

What about supporting this in future version of llblgen pro?

I aggree, CTE's should be useful in making this happen in the new version. It should be less "expensive" than reading the entire collection and then wiring it up with hashtables as Frans suggested.

I'm not sure, aren't you re-reading rows from the same table while just changing the filter? Reading htem straight forward is much faster then.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
sami
User



Location:
Hellsinki, Finland
Joined on:
28-Oct-2005 09:44:08
Posted:
93 posts
# Posted on: 14-Jan-2006 18:23:54.  
Also, using CTE's is usefull on following scenario:

For example, you have table of deparments, which can have sub-departments. A deparment contains people working in that deparment.

Now, you want to fetch all people working under certain deparment or its sub-deparments.
I think using CTE is the easiest way to do that.
http://www.visualcomponents.com
See for yourself!
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37375 posts
# Posted on: 16-Jan-2006 10:47:32.  
sami wrote:
Also, using CTE's is usefull on following scenario:

For example, you have table of deparments, which can have sub-departments. A deparment contains people working in that deparment.

Now, you want to fetch all people working under certain deparment or its sub-deparments.
I think using CTE is the easiest way to do that.

If the departments are in the same table, reading the set once is faster than creating different subsets of the same set through CTE's.

Make no mistake: storing a tree in a single table with the 'parentid' fk pointing to the same table isn't the most efficient way to storing tree data, no matter if there are cte's or recursive queries to help you. Much easier is it to store the tree as a balanced tree, so you can use simple select statements to retrieve a complete hierarchy in a branche. Regular Smiley


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
sami
User



Location:
Hellsinki, Finland
Joined on:
28-Oct-2005 09:44:08
Posted:
93 posts
# Posted on: 17-Jan-2006 09:47:04.  
Correct me if I am wrong, but are you suggesting that I would have a table per hierarchy level?
http://www.visualcomponents.com
See for yourself!
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37375 posts
# Posted on: 17-Jan-2006 10:26:20.  
sami wrote:
Correct me if I am wrong, but are you suggesting that I would have a table per hierarchy level?


No, please check this document:
long url on google groups


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
sami
User



Location:
Hellsinki, Finland
Joined on:
28-Oct-2005 09:44:08
Posted:
93 posts
# Posted on: 17-Jan-2006 10:39:03.  
Now this is interesting, thanks for the hint Laugh
http://www.visualcomponents.com
See for yourself!
 
Top
mattcole
User



Location:

Joined on:
25-May-2006 08:10:38
Posted:
15 posts
# Posted on: 01-Jun-2006 03:39:49.  
Is there a way of implementing what is talked about in that google groups page in LLBLGen without using stored procedures? Specifically the adding of new entities?

Thanks,
Matt


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37375 posts
# Posted on: 01-Jun-2006 09:34:13.  
Yes, I don't see a reason why that wouldn't work. The queries are all writable as LLBLGen Pro queries, except perhaps the insert code with the update + CASE statement. This needs 2 or 3 to overcome the lack of support for CASE.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
greenstone
User



Location:
Alexandria, VA, USA
Joined on:
20-Jun-2007 03:39:56
Posted:
124 posts
# Posted on: 10-Aug-2007 13:30:48.  
A nice tip to look into the balanced tree approach.

Might the referenced "Long URL on Google Groups" be now pointing at an unintended link?

Maybe the link previously contained information about database storage using a "balanced tree" in lieu of a "self-referencing" table?

The only information on this link now appears to contain is a suggestion: "Considering get hold of "Advanced Transact-SQL for SQL Server 2000" by Itzik Ben-Gan and Tom Moreau. The book has an entire chapter on dealing with hierarchical structures implemented in SQL Server. "

...or maybe your recommendation is to get this book to understand about hierarchical structures in SQL Server?




Andy  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37375 posts
# Posted on: 12-Aug-2007 14:51:41.  
greenstone wrote:
A nice tip to look into the balanced tree approach.

Might the referenced "Long URL on Google Groups" be now pointing at an unintended link?

Maybe the link previously contained information about database storage using a "balanced tree" in lieu of a "self-referencing" table?

yes.

Quote:

The only information on this link now appears to contain is a suggestion: "Considering get hold of "Advanced Transact-SQL for SQL Server 2000" by Itzik Ben-Gan and Tom Moreau. The book has an entire chapter on dealing with hierarchical structures implemented in SQL Server. "
...or maybe your recommendation is to get this book to understand about hierarchical structures in SQL Server?

No, I was pointing to a thread on google groups where CELKO explained it. He posted it a number of times so do a search on CELKO + tree and you'll find it Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
greenstone
User



Location:
Alexandria, VA, USA
Joined on:
20-Jun-2007 03:39:56
Posted:
124 posts
# Posted on: 12-Aug-2007 14:52:56.  
Thanks!

Andy  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.