cat/sub cats hierarchy - totals column problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

cat/sub cats hierarchy - totals column problem

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-09-23 : 16:15:41

I have a hierarchy of categories/subcategories (idea taken from an article on this site about having unlimited cat/sub cats). Everything works fine except I need to somehow tally up the # of content items associated to each category (and all its sub-categories).

DDL below:

Table Categories:
CategoryID, ParentID, Name, Desc, Depth, BreadCrumb, TotalContentItems

I need a batch stored sproc that will fill in the TotalContentItems value.
Content is stored in the following table:

Table Content

CategoryID, contentID

So somehow this super-query has to figure out how many content items are associated with it. Keep in mind
that there can be an unlimited # of sub-categories in the Categories table (it uses ParentID to reference the parent category).

thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-23 : 16:22:09

You said DDL below, but you didn't provide DDL. DDL is CREATE TABLE statement. We also need sample data in the form of INSERT INTO statement along with the expected result set using the sample data.

Tara

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-09-23 : 16:36:35

Hi,

My DDL is basically based on this article: http://www.sqlteam.com/item.asp?ItemID=8866

I'll be back with my DDL and other statements in a bit (this might take a while for me).

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-23 : 16:45:11

Once you have all of the paths generated, you can identify which paths extend below the current 'node'.

By using the last child in each path that extends past the current node you have a list of all its children, join that list against the content items and count the distinct items. Thats all.

Corey

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-09-23 : 16:57:41

7thnight, i have been struggling with this for a looooooong time...i need help on it bigtime!

HERE is my DDL etc:

value of TotalContentItems is in brackets below:

Here is what the hierarchy looks like:

Topic1 (5)
-Topic1.1 (3)
--Topic1.1.1 (1)
--Topic1.1.2 (1)
-Topic1.2 (1)
Topic2 (2)
-Topic2.1 (1)
Topic3 (1)

CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TotalContentItems] [int] NULL

) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Contents] (
[ContentID] [int] IDENTITY (1, 1) NOT NULL ,
[Body] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO

CREATE TABLE [dbo].[ContentsCategories] (
[CategoryID] [int] NOT NULL ,
[ContentID] [int] NOT NULL
)
GO
-- load categories table
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (0, 'Topic1', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (1, 'Topic1.1', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (0, 'Topic2', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (1, 'Topic1.2', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (3, 'Topic2.1', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (2, 'Topic1.1.1', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (0, 'Topic3', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (2, 'Topic1.1.2', 0)

-- load content table
INSERT INTO Contents(body)
VALUES ('1111')
INSERT INTO Contents(body)
VALUES ('2222')
INSERT INTO Contents(body)
VALUES ('3333')
INSERT INTO Contents(body)
VALUES ('4444')
INSERT INTO Contents(body)
VALUES ('5555')
INSERT INTO Contents(body)
VALUES ('6666')
-- load contentscategories table
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (1, 1)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (2, 1)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (3, 6)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (4, 5)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (5, 3)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (6, 2)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (7, 4)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (8, 2)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-23 : 17:14:46

I also have an article on this... http://www.seventhnight.com/treestructs.asp

but this might help:


CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TotalContentItems] [int] NULL 

) ON [PRIMARY]
GO


CREATE TABLE [dbo].[Contents] (
[ContentID] [int] IDENTITY (1, 1) NOT NULL , 
[Body] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) 
GO

CREATE TABLE [dbo].[ContentsCategories] (
[CategoryID] [int] NOT NULL ,
[ContentID] [int] NOT NULL 
)
GO
-- load categories table
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (0, 'Topic1', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (1, 'Topic1.1', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (0, 'Topic2', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (1, 'Topic1.2', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (3, 'Topic2.1', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (2, 'Topic1.1.1', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (0, 'Topic3', 0)
INSERT INTO Categories(ParentID, [name], [totalcontentitems])
VALUES (2, 'Topic1.1.2', 0)

-- load content table
INSERT INTO Contents(body)
VALUES ('1111')
INSERT INTO Contents(body)
VALUES ('2222')
INSERT INTO Contents(body)
VALUES ('3333')
INSERT INTO Contents(body)
VALUES ('4444')
INSERT INTO Contents(body)
VALUES ('5555')
INSERT INTO Contents(body)
VALUES ('6666')
-- load contentscategories table
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (1, 1)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (2, 1)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (3, 6)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (4, 5)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (5, 3)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (6, 2)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (7, 4)
INSERT INTO contentscategories(categoryID, ContentID)
VALUES (8, 2)


--Select * From categories
--Select * From contents
--Select * From contentscategories

Declare @paths table (path varchar(1000), childCat int, step int)

Declare @rowCount int,
	@counter int

	Set @counter = 0 
	Insert Into @paths
	Select 
		path = right('000'+convert(varchar,categoryId),3), 
		childCat = categoryId, 
		step = @counter 
	From categories 
	where parentId=0

Set @rowCount = @@rowcount
Set @counter = @counter + 1

While @rowCount > 0
Begin
	Insert Into @paths
	Select 
		path = isnull(path+',','')+right('000'+convert(varchar,categoryId),3), 
		childCat = categoryId, 
		step=@counter 
	From categories A
	Inner Join @paths B
	On A.parentId = B.childCat
	where B.step = @counter-1
	
	Set @rowCount = @@rowcount
	Set @counter = @counter + 1
End


Declare @startNode int

Set @startNode = 1

Select * From @paths

Select A.*, B.ContentId, C.body 
From @paths A
Inner Join contentscategories B
On A.childCat = B.categoryId
Inner Join contents C
On B.contentId = C.contentId
Where A.path like '%'+Right('000'+@startNode,3)+',%'

Drop Table [dbo].[Categories]
Drop Table [dbo].[Contents]
Drop Table [dbo].[ContentsCategories]

Corey

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-23 : 17:33:20

I've used the same article to create hierachies too, but I've never performed this query before. Perhaps this could work.


SELECT	parent.CategoryID, parent.Name, 
	--Get the number of content items for this categories children.
	(SELECT COUNT(*) 
	 FROM Categories child INNER JOIN Content ON (child.CategoryID = Content.CategoryID)
	 WHERE child.BreadCrumb LIKE parent.BreadCrumb + CONVERT(varchar(200), parent.CategoryID) + ',%')
	--add to it the number of content items for this category.
     + 	(SELECT COUNT(*)
	 FROM Content
	 WHERE Content.CategoryID = parent.CategoryID) AS NumberOfContentItems
FROM	Category parent

I deviated from the example a little bit, in which the lineage also stores the ID of the item it is on.

For example if in the articles, item 10 has a lineage of ,1,2,3, the lineage in my database for item 10 would be ,1,2,3,10,

This way you could combine the 2 sub queries into a single one.


(SELECT COUNT(*) 
 FROM Categories child INNER JOIN Content ON (child.CategoryID = Content.CategoryID)
 WHERE child.BreadCrumb LIKE parent.BreadCrumb + '%')

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-09-24 : 09:31:39

how about using a recursive query, i'm guessing that would be cleaner.

any help on that one?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-24 : 09:33:38

you can only recurse 32 times... at least i think thats what it was...

Plus how is that cleaner?
If you keep and maintain the path table you can answer all manner of parent/child questions very easily... Plus it is easier to use in reporting.

Corey

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-09-24 : 09:42:28

7thnight, yeah I guess your right.

here is what I have now, I have a lineage column that stores all the parent ID's, slash seperated.

If there is a single slash, it means it is a TopLevelCategory, here is my ouput of that column:

Lineage
/
/
/
/
/5/
/5/
/5/
/
/9/
/1/
/1/
/
/13/
/13/
/13/
/13/
/4/
/4/
/4/
/4/
/4/
/4/
/4/
/4/
/4/
/4/
/5/
/5/
/
/
/
/
/
/35/
/35/
/30/
/30/
/30/
/30/
/30/
/32/
/
/44/
/
/
/47/
/47/
/47/
/47/
/32/
/32/

now what? (sorry for the trouble, but my brain can't wrap around this!)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-24 : 09:46:49

it seems like you column should look more like:

1
1/2
1/2/5
1/2/5/6
1/3
1/3/7
1/3/7/8
1/3/9
1/3/9/13
1/4
1/4/10
1/4/11
1/4/11/12
1/4/11/12/14

etc...

Show how you got this lineage column??

Corey

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-09-24 : 09:51:54

I just modified the query from the original article, instead of using the name of the category to create the breadcrumb, I used the ID's:

-- STEP #3
WHILE EXISTS (SELECT * FROM Categories WHERE Depth Is Null)
BEGIN
UPDATE T
SET
T.depth = P.Depth + 1,
T.Lineage = P.Lineage + Ltrim( Str(T.ParentID,6,0) ) + '/' ,

FROM Categories AS T
INNER JOIN Categories AS P ON (T.ParentID=P.CategoryID)
WHERE
P.Depth>=0
AND P.Lineage is NOT NULL
AND T.Depth Is Null

END

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-24 : 10:05:15

but that will only do one pass right? Plus you are updating? you should be inserting...

Take a look at my post directly after you posted DDL and what not... run it and make sure understand what the path table should look like.

Be careful that you don't drop your tables as my code has drops at the end...

Corey

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-09-24 : 10:26:25

I ran it, and the table output is:

001 1 0
003 3 0
007 7 0
001,004 4 1
001,002 2 1
003,005 5 1
001,002,008 8 2
001,002,006 6 2

001,002 2 1 1 1111
001,002,008 8 2 2 2222
001,002,006 6 2 2 2222
001,004 4 1 5 5555

but i'm not sure I understand what to do with it!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-24 : 10:40:17

ok...

first result set is the paths
the first column is the catId lineage padded to 3 digits (that allows for easy sorting)
the second column is the last catId in the lineage
the last column is the depth, or number of steps in the path

you can query the path table (@paths) to get all of the children category for a particular parent category.

Lets say we want all of the child categories of catId #1.
Just looking at the @paths table we see that we would want the following paths include catId #1:
001 1 0
001,004 4 1
001,002 2 1
001,002,008 8 2

We know that 1 is not a child of itself so that leaves
001,004 4 1
001,002 2 1
001,002,008 8 2

How to get that list from @paths
Declare @startNode int
Set @startNode = 1

Select A.*
From @paths A
Where A.path like '%'+Right('000'+@startNode,3)+',%'

The comma in bold is how we force only children of catId #1

Pulling in the rest of the info
Using that same query, and joining in the contentcategories and the contents tables we get:

Declare @startNode int
Set @startNode = 1

Select A.*, B.ContentId, C.body
From @paths A
Inner Join contentscategories B
On A.childCat = B.categoryId
Inner Join contents C
On B.contentId = C.contentId
Where A.path like '%'+Right('000'+@startNode,3)+',%'

Which gives:
001,002 2 1 1 1111
001,002,008 8 2 2 2222
001,002,006 6 2 2 2222
001,004 4 1 5 5555

So ultimately the count of all children's contents
would be the unique contents from the above query, or:


Select
	cnt = count(distinct c.contentId)
From
	(
	Select A.*, B.ContentId, C.body 
	From @paths A
	Inner Join contentscategories B
	On A.childCat = B.categoryId
	Inner Join contents C
	On B.contentId = C.contentId
	Where A.path like '%'+Right('000'+@startNode,3)+',%'
	) Z

Which should give 3 (1,2, & 5)

I know its alot, but does that make better sense??

Corey

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-09-27 : 17:21:08

should I keep a paths table in my db or create on the fly?

if yes, does your script let me do that?

are there any limiations to your design? i'm guessing the padding of 000 is a limitation right?

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-27 : 17:36:55

Were you able to try what I did in my post Sql777?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-27 : 17:40:58

I would guess you probably want to generate it on the fly

If it doesn't change very often, then you could probably keep it as a static table, and then regenerate when you make changes.

Limitations, well the length of the path column is a limitation, but the pad really isn't as you can change it to whatever you want (as long as you are consistent through the script).

Thats all I can think of, but let me know if you have any questions

Corey

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-09-28 : 12:56:54

great.

so to put it all together, what shall I do?

I'm thinking loop through all ID's and use your path to generate the Totals for each Category and then update the db?

if you are bored one day, if you could put all this complicated stuff togher that would be great! (i feel like a ** asking you all of this, but i understand if its too much to ask for!)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-28 : 15:08:19

well I am not entirely done with my extend article, but I am putting it all together...
if you want to browse it now go ahead, just remember its not 100% done. I have tested and verified parts 1 and 2 (working on part 3)

http://www.seventhnight.com/new/treestructs.asp
Let me know what you think about it!?!

for your specific question: I don't think you need to loop through the ids to get the count. Check out the bottom of part 2 and maybe that will lend you some help

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources