February 7, 2012 at 7:40 am
I have a hierarchy setup to store product categories as below :
ID int
ParentID int
CategoryName varchar(50)
My products are categorised only at the leaf level.
My general category navigation I have got working OK using some CTE queries I found that gives subordinate categories. We show products at every level, with a list that reduces as they drill down - this I achieved just by looking at which products are in the subordinate categories of the current level - i.e. look all the way down at all possible child categories to the lowest level and grab products that match.
However I am now working on the on-site search. When a user searches I want to show them the categories that the products that match their criteria are in, and allow them to drill down within their search results. Ideally I want to start them at level 2 of my hierarchy also.
So when they search they would see the 2nd tier categories of the products that match their criteria, and when they click through they'd see the next tier down and so on.
I can't wrap my head around how to pull out the categories at level 2 to begin with, and then to get the next step down only for the matching products. For my general navigation it's easy as they are all relevant so as long as they are linked it's OK, but in this search scenario they are only relevant if they match the search!
Many Thanks for any light anyone can shed on this!
February 7, 2012 at 7:42 am
cdh-727733 (2/7/2012)
I have a hierarchy setup to store product categories as below :ID int
ParentID int
CategoryName varchar(50)
My products are categorised only at the leaf level.
My general category navigation I have got working OK using some CTE queries I found that gives subordinate categories. We show products at every level, with a list that reduces as they drill down - this I achieved just by looking at which products are in the subordinate categories of the current level - i.e. look all the way down at all possible child categories to the lowest level and grab products that match.
However I am now working on the on-site search. When a user searches I want to show them the categories that the products that match their criteria are in, and allow them to drill down within their search results. Ideally I want to start them at level 2 of my hierarchy also.
So when they search they would see the 2nd tier categories of the products that match their criteria, and when they click through they'd see the next tier down and so on.
I can't wrap my head around how to pull out the categories at level 2 to begin with, and then to get the next step down only for the matching products. For my general navigation it's easy as they are all relevant so as long as they are linked it's OK, but in this search scenario they are only relevant if they match the search!
Many Thanks for any light anyone can shed on this!
Can you read this article, then post sample data, DDL scripts and expected results please?[/url]
February 7, 2012 at 8:25 am
Here goes I hope this helps - I have way oversimplified it but hopefully enough info is here :
My Category table :
CREATE TABLE [dbo].[tblCategory](
[ID] [int] NOT NULL,
[ParentID] [int] NULL,
[Name] [varchar](150) NOT NULL,
CONSTRAINT [PK_tblCategory] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (1, 0, 'Power Tools')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (2, 0, 'Hand Tools')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (3, 1, 'Drills')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (4, 1, 'Saws')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (5, 1, 'Planers & Thicknessers')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (6, 3, 'Twist Drills')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (7, 3, 'Hammer Drills')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (8, 5, 'Planers')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (9, 5, 'Thicknessers')
My Product Table :
CREATE TABLE [dbo].[tblProduct](
[ProductID] [int] NOT NULL,
[ProductName] [varchar](150) NOT NULL,
[CategoryID] [int] NOT NULL
CONSTRAINT [PK_tblProduct] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO tblProduct (ProductID, ProductName, CategoryID) VALUES (1, 'A Twist Drill', 6)
INSERT INTO tblProduct (ProductID, ProductName, CategoryID) VALUES (2, 'Another Twist Drill', 6)
INSERT INTO tblProduct (ProductID, ProductName, CategoryID) VALUES (3, 'A Hammer Drill', 7)
INSERT INTO tblProduct (ProductID, ProductName, CategoryID) VALUES (4, 'Another Hammer Drill', 7)
INSERT INTO tblProduct (ProductID, ProductName, CategoryID) VALUES (5, 'A Planer with Drill in the name', 9)
This is my CTE that gives me for example all categories under Category ID 1 (Power Tools)
WITH subnodes( Distance, ID, Name, Node_Seq )
AS
(
SELECT 0,
h.ID,
h.Name,
CONVERT( varchar(80), ltrim(str(h.ID))) as Node_Seq
FROM tblCategory h
WHERE h.ID = 1
UNION ALL SELECT distance+1,
h.ID,
h.Name,
CONVERT( varchar(80), sn.Node_Seq + '.' + ltrim(str(h.ID)))
FROM tblCategory h
INNER JOIN subnodes sn ON
h.ParentID = sn.ID
)
SELECT Distance, ID, Name, Node_Seq FROM subnodes
So I want to be able to search the product table say for the term "drill" that would return all 5 product records in my listing - but in my summary categorisation I'd ideally want to show initially "Drills" and "Planers & Thicknessers" then if the choose "Drills" it clicks through to show "Electric Drills" and "Hammer Drills" which is the lowest level click through.
I hope that makes sense!
February 7, 2012 at 8:43 am
Sorry, I accept that I may be being dense here but as far as I can see you still haven't said what you actually want in your result-set.
February 7, 2012 at 8:54 am
No I think it's me being dense sorry.
So in my result set I just want the categories that match.
So if I feed in search critera and the selected category id, I just want the next level down of categories.
February 7, 2012 at 9:06 am
cdh-727733 (2/7/2012)
No I think it's me being dense sorry.So in my result set I just want the categories that match.
So if I feed in search critera and the selected category id, I just want the next level down of categories.
I'm still not sure I get it.
WITH subnodes (Distance, ID, NAME, Node_Seq, ParentID)
AS (
SELECT 0, h.ID, h.NAME, CONVERT(VARCHAR(80), ltrim(str(h.ID))) AS Node_Seq, h.ParentID
FROM tblCategory h
WHERE h.ID = 1
UNION ALL
SELECT distance + 1, h.ID, h.NAME, CONVERT(VARCHAR(80), sn.Node_Seq + '.' + ltrim(str(h.ID))),
h.ParentID
FROM tblCategory h
INNER JOIN subnodes sn ON h.ParentID = sn.ID
)
SELECT baseQ.Distance, baseQ.ID, baseQ.NAME, baseQ.Node_Seq
FROM subnodes baseQ
OUTER APPLY (SELECT TOP 1 Distance, ID, NAME, Node_Seq, b.ProductName, ParentID
FROM subnodes innerQ
CROSS APPLY (SELECT TOP 1 ProductName
FROM tblProduct
WHERE PATINDEX('%'+'drill'+'%',ProductName) > 0 --your search term replaces "drill"
AND innerQ.ID = CategoryID
ORDER BY CategoryID ASC) b
WHERE baseQ.ID = ParentID) outerQ
WHERE ProductName IS NOT NULL
In the above, I've searched for products that contain "drill", so have displayed the upper level categories that have products that contain drill.
Is this what you're after?
Distance ID NAME Node_Seq
----------- ----------- -------------------------- ----------
1 3 Drills 1.3
1 5 Planers & Thicknessers 1.5
February 7, 2012 at 9:22 am
It is yep - sorry it's hard enough for me to wrap my head round what I need this to do let alone expect someone to pick up the pieces and put a solution together so I applaud you!
If the user were to then click on the link driven by "Drills" result it should then come back with the next level down choices of "Twist Drills" and "Hammer Drills". I tried switching the "where h.ID=1" to 3 but that stuck it at Drills and it didn't drill down (no pun intended)
I'm hacking at it to try and achieve that because this has to be nearly it I think.
Thanks for sticking with me on this.
February 7, 2012 at 9:46 am
cdh-727733 (2/7/2012)
It is yep - sorry it's hard enough for me to wrap my head round what I need this to do let alone expect someone to pick up the pieces and put a solution together so I applaud you!If the user were to then click on the link driven by "Drills" result it should then come back with the next level down choices of "Twist Drills" and "Hammer Drills". I tried switching the "where h.ID=1" to 3 but that stuck it at Drills and it didn't drill down (no pun intended)
I'm hacking at it to try and achieve that because this has to be nearly it I think.
Thanks for sticking with me on this.
Back from my meeting.
OK, I've only knocked this up very quickly so it could do with being looked at again but I think this is what you're after: -
WITH subnodes (Distance, ID, NAME, Node_Seq, ParentID)
AS (
SELECT 0, h.ID, h.NAME, CONVERT(VARCHAR(80), ltrim(str(h.ID))) AS Node_Seq, h.ParentID
FROM tblCategory h
WHERE h.ID = 1
UNION ALL
SELECT distance + 1, h.ID, h.NAME, CONVERT(VARCHAR(80), sn.Node_Seq + '.' + ltrim(str(h.ID))), h.ParentID
FROM tblCategory h
INNER JOIN subnodes sn ON h.ParentID = sn.ID
),
CTE AS (
SELECT baseQ.Distance, baseQ.ID, baseQ.NAME, baseQ.Node_Seq, ProductName, outerQ.ID AS prod, pNode_Seq
FROM subnodes baseQ
OUTER APPLY (
SELECT Distance, ID, NAME, Node_Seq AS pNode_Seq, b.ProductName, ParentID
FROM subnodes innerQ
CROSS APPLY (
SELECT ProductName
FROM tblProduct
WHERE PATINDEX('%' + 'drill' + '%', ProductName) > 0 --your search term replaces "drill"
AND innerQ.ID = CategoryID
) b
WHERE baseQ.ID = ParentID
) outerQ
WHERE ProductName IS NOT NULL
),
levels AS (
SELECT Distance, ID AS seID, ID, NAME, Node_Seq, 0 AS level
FROM CTE
GROUP BY Distance, ID, NAME, Node_Seq
UNION ALL
SELECT Distance, ID, prod, ProductName, pNode_Seq, 1 AS level
FROM CTE
)
SELECT Distance, ID, NAME, Node_Seq
FROM levels
--WHERE level = 1 AND seID = 3
WHERE level = 0
You can set the level you're after to 0 for the lowest matching categories, or 1 for the actual products.
February 7, 2012 at 9:52 am
Ah sorry I need the next level down of category not the product level itself, so if we pass the category id of 3 (Drills) that needs to grab "Twist Drills" and "Hammer Drills" in this instance
February 7, 2012 at 9:57 am
cdh-727733 (2/7/2012)
Ah sorry I need the next level down of category not the product level itself, so if we pass the category id of 3 (Drills) that needs to grab "Twist Drills" and "Hammer Drills" in this instance
Minor tweek.
As I say though, this needs cleaning up a lot. If I wasn't about to head out the office, I'd have a go, but there's going to be a Craig shaped hole in the door to our office in about 5 minutes 😉
WITH subnodes (Distance, ID, NAME, Node_Seq, ParentID)
AS (
SELECT 0, h.ID, h.NAME, CONVERT(VARCHAR(80), ltrim(str(h.ID))) AS Node_Seq, h.ParentID
FROM tblCategory h
WHERE h.ID = 1
UNION ALL
SELECT distance + 1, h.ID, h.NAME, CONVERT(VARCHAR(80), sn.Node_Seq + '.' + ltrim(str(h.ID))), h.ParentID
FROM tblCategory h
INNER JOIN subnodes sn ON h.ParentID = sn.ID
),
CTE AS (
SELECT baseQ.Distance, baseQ.ID, baseQ.NAME, baseQ.Node_Seq, outerQ.NAME AS ProductName, outerQ.ID AS prod, pNode_Seq
FROM subnodes baseQ
OUTER APPLY (
SELECT Distance, ID, NAME, Node_Seq AS pNode_Seq, b.ProductName, ParentID
FROM subnodes innerQ
CROSS APPLY (
SELECT ProductName
FROM tblProduct
WHERE PATINDEX('%' + 'drill' + '%', ProductName) > 0 --your search term replaces "drill"
AND innerQ.ID = CategoryID
) b
WHERE baseQ.ID = ParentID
) outerQ
WHERE ProductName IS NOT NULL
),
levels AS (
SELECT Distance, ID AS seID, ID, NAME, Node_Seq, 0 AS level
FROM CTE
GROUP BY Distance, ID, NAME, Node_Seq
UNION ALL
SELECT Distance, ID, prod, ProductName, pNode_Seq, 1 AS level
FROM CTE
)
SELECT Distance, ID, NAME, Node_Seq
FROM levels
WHERE level = 1 AND seID = 3
--WHERE level = 0
GROUP BY Distance, ID, NAME, Node_Seq
February 7, 2012 at 10:18 am
Thanks for all your efforts much appreciated.
It does seem to mostly work with the example data - I even put in a 4th category level and it returned that.
However it doesn't seem to work at higher levels now - e.g. passing in the 2nd level ID doesn't show the 3rd, but passing in the 3rd level does return the 4th.
I also need to try and get it to work when it doesn't know what the initial category selection is and return the 2nd tier ones (or even just the top tier ones would be a step forward from where I was at).
I will try and deconstruct this and see if I can (a) understand it! and (b) engineer in the missing pieces.
Cheers.
February 7, 2012 at 10:53 am
Depending on how often the products change, it may be worth putting this data into a permanent, indexed location so that it does not have to be requeried on the fly every time any user wants to query the data. Updating the data once a day or once a week and using the compiled data may reduce server stress and improve response time.
February 7, 2012 at 11:37 am
I can't do that unfortunately as this is to be driven from user searches which I can't predict.
February 7, 2012 at 5:28 pm
cdh-727733 (2/7/2012)
I can't do that unfortunately as this is to be driven from user searches which I can't predict.
What I am suggesting is that you create a complete hierarchy that includes all products. When a user requests a search, you use the hierarchy from the point they request, progressing down from there.
We currently do that with our distributor and customer list, which runs to 200,000 people. We rebuild the hierarchy once an hour. When a distributor searches for distributors and customers in his sales organization, that data is already precompiled so we don't have to dynamically build the hierarchy every time to return the results.
For our purpose, once an hour is sufficient. If your product hierarchy changes less frequently, you could rebuild the complete hierarchy once a day.
February 7, 2012 at 6:05 pm
fahey.jonathan (2/7/2012)
cdh-727733 (2/7/2012)
I can't do that unfortunately as this is to be driven from user searches which I can't predict.What I am suggesting is that you create a complete hierarchy that includes all products. When a user requests a search, you use the hierarchy from the point they request, progressing down from there.
We currently do that with our distributor and customer list, which runs to 200,000 people. We rebuild the hierarchy once an hour. When a distributor searches for distributors and customers in his sales organization, that data is already precompiled so we don't have to dynamically build the hierarchy every time to return the results.
For our purpose, once an hour is sufficient. If your product hierarchy changes less frequently, you could rebuild the complete hierarchy once a day.
I'm just curious. What type of hierarchy are you using? Adjacency list, Hierarchical Path, HierarchyID, Nested Sets, cfomposite of 2 or more of those? Also, how long does it take you to rebuild your 220k node hierarchy?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply