Hi People,

I have a typical Hierarchical structure where an item can be contained to any inside another item. Each node can have multiple children (I left it out in this example to make things simpler). What I would like to try to do is have it so a child item inherits a status from up the tree. So if B has a status of 1 then all the nodes below B should also have a status of 1. Like wise if A had a status of 1 then all nodes below it should report its status as being 1 as well.

For instance:

................A .............../..\ .............B.....C ............/........\ ...........D.........E

Excuse my crude ACSII art (where would be without ASCII art?)

Each node knows who its parent is by store the primary key of its parent in a ParentId field. if the item doesn't have a parent the ParentId field is null.

So for a practical example: let’s say the Item b has been marked as destroyed then all items below it must also be as destroyed because they are contained within it.

Below I have included some SQL that will create the tables and some data. I’m just not sure what a good solution is. Often one node will have thousands of child nodes making hard just to read the whole lot out and cache it.

Whats the best way to handle this in LLBLGEN?

I'm Using SQL 2005, LLBLGEN Pro 2.0 and VS 2005.

Thanks heaps for your help! -M

PS hope it made sense

GO /****** Object: Table [dbo].[TestHierarchy] Script Date: 08/23/2006 19:08:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TestHierarchy]( [Id] [int] IDENTITY(1,1) NOT NULL, [ParentId] [int] NULL, [Name] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Status] [int] NOT NULL, CONSTRAINT [PK_TestHierarchy] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[TestHierarchy] WITH CHECK ADD CONSTRAINT [FK_TestHierarchy_TestHierarchy] FOREIGN KEY([ParentId]) REFERENCES [dbo].[TestHierarchy] ([Id]) GO ALTER TABLE [dbo].[TestHierarchy] CHECK CONSTRAINT [FK_TestHierarchy_TestHierarchy]

INSERT INTO TestHierarchy (ParentId,Name,Status) VALUES (null,'A',1) INSERT INTO TestHierarchy (ParentId,Name,Status) VALUES (1,'B',3) INSERT INTO TestHierarchy (ParentId,Name,Status) VALUES (1,'C',3) INSERT INTO TestHierarchy (ParentId,Name,Status) VALUES (2,'D',3) INSERT INTO TestHierarchy (ParentId,Name,Status) VALUES (3,'E',3)

That's a business logic problem not an LLBLGen Pro.

Basicly in most of these cases the read operations will be far more than the write operations.

So I'd implement it as follows:

1- When setting the field in an entity, I'll have a method that go to all its children and set the same field too (a recursive loop in one transaction)

2- Then you will only have to check the field of the entity on hand to know its value without the need of traversing the tree.

Hi Walaa,

Yep I understand its business logic issue I just though there might be a way have LLBLGEN pro help me out a bit. Just for anyone else interested one way of solving it was to add another field to the table called isParentOut . Then I created a strored proceedure and connected to it through LLBLGEN pro then called it on the save of an entity when the status gets set to a certain type.

Code to loop through (only works on SQL 2005 using CTE):

With ItemStatus(Id) AS ( SELECT Id FROM StorageItem WHERE ParentId = 1284066 AND ParentId is not null UNION ALL SELECT E.id FROM StorageItem E JOIN ItemStatus ON ItemStatus.Id = E.ParentId ) UPDATE StorageItem SET isParentOut = 1 FROM StorageItem INNER JOIN ItemStatus ON StorageItem.Id = ItemStatus.Id

Thanks for your help Walaa! -M

FYI SqlServer2005 supports recursive queries, too (albeit they are not supported through LLBLGenPro I guess). Perhaps this feature might come handy sometime.

just fyi, there is an sql hierarchy model that are very usefull to use (imho), it's called "Nested set". Take a look on google, quick link : http://www.developersdex.com/gurus/articles/112.asp

Hi Fabrice,

Thats for the link I will have a look into to it and see how it works out.

Thanks -M

And here you are, another 2 threads discussing the nested set model: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5880 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5493