Trees and Hierachies (sort of)

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

SQL Server Development (2000)

Trees and Hierachies (sort of)

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2006-05-22 : 10:47:31

Hi All,

I'm tempted to address this to Corey as he's sorted every other query I've ever had about trees and hierachies but I'm sure someone else will know the answer.

Basically I've two tables:
1. tblCategories
CategoryId
pCategoryId
CategoryName
CategoryURL
CategoryImage

2. tblProducts
ProductId
ProductName
ProductImage

What I would like to do is have the CategoryImage populated from the ProductImage field of one it's children. The catch is that it may be many levels deep before a product is available. How can I do this?

Example for when a product is in the category "SubCategory":

Category/ <--No Image here
Category/SubCategory/ <--Image does appear

Many thanks,

Tim

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-23 : 04:20:03

http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-05-23 : 08:33:04

Oooh!! I've done this... let me see...

Are you doing this in bulk? (aka more than 1 category at a time)
What happens if more than one subcategory has products?
Choose image at random?
Are you opposed to a 'paths' table? - I believe that would be the easiest if you don't change products and categories too often...

I'll be back...

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-05-23 : 10:38:51

Well... I must say. I checked my old db for my website, and it appears that I did not use a path table for a random photo.

In psuedo-code:


declare table variable @c
do
  insert subcatIds in table variable @c
loop while record cnt > 0

join @c list with image list

then join with randomized view and return top 1

I really don't agree with that anymore though. If your tree doesn't change that often. build a path, or a flattened version. The flatten version would consist of each 'node' and the bottom of the paths below it. for example:


1/2/3/4
1/2/3/5
1/2/6/7
1/2/8


Parent,LastChild
1,4
2,4
3,4
1,5
2,5
3,5
1,7
2,7
6,7
1,8
2,8

The flattened tree would allow you to use a simple join to get the possible images, and then you could randomly sort with newid() to get a random 1.

To get the flattened table:


Declare @cat table (catId int, pCatId int)
Insert Into @cat
Select 1, null Union All
Select 2, 1 Union All
Select 3, 2 Union All
Select 4, 3 Union All
Select 5, 3 Union All
Select 6, 2 Union All
Select 7, 6 Union All
Select 8, 2

Declare @img table (catId int, img varchar(10))
Insert Into @img
Select 4, 'A-4' Union All
Select 4, 'B-4' Union All
Select 4, 'C-4' Union All
Select 5, 'D-5' Union All
Select 5, 'E-5' Union All
Select 5, 'F-5' Union All
Select 6, 'G-6' Union All
Select 6, 'H-6' Union All
Select 7, 'I-7' Union All
Select 7, 'J-7' Union All
Select 8, 'K-8' Union All
Select 8, 'L-8'


--Select * From @img
Declare @flat table (catId int, pCatId int)

Insert Into @flat
Select distinct A.catId, A.pCatId 
From @cat A
Left Join @cat B
On A.catId = B.pCatId
Where B.pCatId is null

While @@RowCount>0
Begin
	Insert Into @flat
	Select Distinct Z.* 
	From 
		(
		Select Distinct A.catId, B.pCatId
		From @flat A
		Inner Join @cat B
		On A.pCatId = B.catId
		Where B.pCatId is not null
		) Z
	Left Join @flat Y
	On Z.catId = Y.catId
	and Z.pCatId = Y.pCatId
	Where Y.catId is null
End

Insert Into @flat
Select distinct
	catId = A.catId,
	pCatId = A.catId
From @cat A
left join @flat B
On A.catId = B.pCatId
where B.pCatId is null

Select * From @flat Order By pCatId

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

timgaunt
Posting Yak Master

115 Posts

Posted - 2006-05-23 : 13:07:52

Madhivanan thanks, but I don't think that's what I wanted. I can get the data out of the "master" categories table in a nice tree but it's the product images I wanted to populate...

Corey how did I know you'd answer :) in response to your Q's:

There will often be more than one, I was just ordering by newid() to choose one at random.

As for the path's table, I'm one step ahead of you there, as the number of categories increased the speed decreased. I don't have many (perhaps 1k) but none the less the speed was impacted so I made a new categories table which I've since indexed etc which has dramatically increased the speed. I've thrown the "rebuild" SProc into a nightly job and it's also called if the user makes any changes to the master categories table. Works nicely

The thing with your example is its the same result as I'm getting, if there is a product in the category it gets the photo fine but if it's a category within the category it's not cascading. Really I don't want the pCat's image to be the same as the Cat's image as this could look messy.

I'm not sure if I'm missing the point with what you outlined but I couldn't get results any different from what I had, this is what I've got:


DECLARE @Categories_Paths TABLE (
	[path] [nvarchar](1000) NULL,
	[URLPath] [nvarchar](1000) NULL,
	[ParentId] [int] NULL,
	[CategoryId] [int] NULL,
	[Depth] [int] NULL,
	[CategoryPhoto] [varchar](255)
)

DECLARE @Products_Built TABLE(
	[ProductId] [int] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[CategoryId] [int] NOT NULL,
	[URLPath] [varchar](2000) NOT NULL,
	[PhotoPath] [varchar](255) NULL
)

INSERT INTO @Categories_Paths
SELECT '0000;0100;', 'Main/', 0, 100, 0, 'NULL' Union All
SELECT '0000;0100;0102;', 'Main/Child1/', 100, 102, 0, 'NULL' Union All
SELECT '0000;0100;0104;', 'Main/Child2/', 100, 104, 0, 'NULL' Union All
SELECT '0000;0100;0104;0106;', 'Main/Child2/SubChild1/', 104, 106, 0, 'NULL' Union All
SELECT '0000;0100;0104;0108;', 'Main/Child2/SubChild1/', 104, 108, 0, 'NULL'

INSERT INTO @Products_Built
SELECT 1, 'SubChild1 Product', 106, 'Main/Child2/SubChild1/SubChild1Product', 'Prod1.jpg' UNION ALL
SELECT 2, 'SubChild1 Product', 106, 'Main/Child2/SubChild1/SubChild1Product', 'Prod2.jpg' UNION ALL
SELECT 3, 'SubChild2 Product', 108, 'Main/Child2/SubChild2/SubChild1Product', 'Prod3.jpg' UNION ALL
SELECT 4, 'SubChild2 Product', 108, 'Main/Child2/SubChild2/SubChild1Product', 'Prod4.jpg' UNION ALL
SELECT 5, 'SubChild2 Product', 108, 'Main/Child2/SubChild2/SubChild1Product', 'Prod5.jpg'

SELECT *
FROM @Categories_Paths

SELECT *
FROM @Products_Built

SELECT
	c.[path],
	p.[PhotoPath]
FROM
	@Categories_Paths As c LEFT JOIN @Products_Built As p
		ON c.CategoryId = p.CategoryId
ORDER BY
	NewID()

When running that, the Main and Child1/2 Nodes won't have a product image which should be pulled from one of their child's products, in the case of main, it should be one of any of the products...

Thanks again,

Tim

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-05-23 : 16:57:22

Ok... Basic issue is that you need to look at the bottom of the tree. That is what I meant by the 'flattened' table. I modified your code a bit, but you didn't have any products under 102 - so no image:


DECLARE @Categories_Paths TABLE (
	[path] [nvarchar](1000) NULL,
	[URLPath] [nvarchar](1000) NULL,
	[ParentId] [int] NULL,
	[CategoryId] [int] NULL,
	[Depth] [int] NULL,
	[CategoryPhoto] [varchar](255)
)

DECLARE @Products_Built TABLE(
	[ProductId] [int] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[CategoryId] [int] NOT NULL,
	[URLPath] [varchar](2000) NOT NULL,
	[PhotoPath] [varchar](255) NULL
)

INSERT INTO @Categories_Paths
SELECT '0000;0100;', 'Main/', 0, 100, 0, 'NULL' Union All
SELECT '0000;0100;0102;', 'Main/Child1/', 100, 102, 0, 'NULL' Union All
SELECT '0000;0100;0104;', 'Main/Child2/', 100, 104, 0, 'NULL' Union All
SELECT '0000;0100;0104;0106;', 'Main/Child2/SubChild1/', 104, 106, 0, 'NULL' Union All
SELECT '0000;0100;0104;0108;', 'Main/Child2/SubChild1/', 104, 108, 0, 'NULL'

INSERT INTO @Products_Built
SELECT 1, 'SubChild1 Product', 106, 'Main/Child2/SubChild1/SubChild1Product', 'Prod1.jpg' UNION ALL
SELECT 2, 'SubChild1 Product', 106, 'Main/Child2/SubChild1/SubChild1Product', 'Prod2.jpg' UNION ALL
SELECT 3, 'SubChild2 Product', 108, 'Main/Child2/SubChild2/SubChild1Product', 'Prod3.jpg' UNION ALL
SELECT 4, 'SubChild2 Product', 108, 'Main/Child2/SubChild2/SubChild1Product', 'Prod4.jpg' UNION ALL
SELECT 5, 'SubChild2 Product', 108, 'Main/Child2/SubChild2/SubChild1Product', 'Prod5.jpg'

SELECT *
FROM @Categories_Paths

SELECT *
FROM @Products_Built


Declare @Categories_Flat table (ParentId int, CategoryId int)

--Insert Bottom of tree.
Insert Into @Categories_Flat
Select A.ParentId, A.CategoryId 
From @Categories_Paths A
Left Join @Categories_Paths B
On A.CategoryId = B.ParentId
Where B.ParentId is null

--Insert Parent Rollups
While @@RowCount>0
Begin
	Insert Into @Categories_Flat
	Select Distinct Z.* 
	From 
		(
		Select Distinct B.ParentId, A.CategoryId
		From @Categories_Flat A
		Inner Join @Categories_Paths B
		On A.ParentId = B.CategoryId
		Where isnull(B.ParentId,0)<>0
		) Z
	Left Join @Categories_Flat Y
	On Z.CategoryId = Y.CategoryId
	and Z.ParentId = Y.ParentId
	Where Y.CategoryId is null
End

--Insert Self-Relations
Insert Into @Categories_Flat
Select distinct
	ParentId = A.CategoryId,
	CategoryId = A.CategoryId
From @Categories_Paths A
left join @Categories_Flat B
On A.CategoryId = B.ParentId
where B.ParentId is null

Select * From @Categories_Flat Order By ParentId


--Get Random Photo for each.
SELECT
	c.[path],
	[PhotoPath] = 
		(

		Select top 1 p.PhotoPath 
		From @Categories_Flat f
		Inner Join @Products_Built p 
		On f.CategoryId = p.CategoryId
		Where f.ParentId = c.CategoryId
		Order By NewId()

		)
FROM @Categories_Paths As c

Results:


path			photopath
0000;0100;		Prod2.jpg
0000;0100;0102;		NULL
0000;0100;0104;		Prod3.jpg
0000;0100;0104;0106;	Prod1.jpg
0000;0100;0104;0108;	Prod5.jpg

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

timgaunt
Posting Yak Master

115 Posts

Posted - 2006-05-24 : 04:39:06

Once again you've come up with the goods, thanks very much -I knew I was missing something. I have youi flat table but I was thinking you meant my built paths table :) Produces exactly the results I was after, many thanks for that.

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources