All geek questions in one place

Parent child tree SQL BY DEPTH parent sort and display order

I have this query that creates a hierarchy of categories. It works great. Now I am trying to control the display order based on depth. For example, I have a category called "Cat." It has two subcategories called "Sub Cat 1" and "Sub Cat 2". Can subcategories be sorted by display order and depth? Thus, the order will be "Sub Cat 2 then" Sub Cat 1, based on the display order. I want to be able to control the order of categories. Based on its location in depth and display order.

Here is my request:

DECLARE @CategoryID INT; DECLARE @ExcludeInactive BIT; SET @CategoryID = 2; SET @ExcludeInactive = 0; Declare @Categories Table ( ID INT, Name VARCHAR(500), ParentID INT, Depth INT, Active BIT, DisplayOrder INT ); INSERT INTO @Categories SELECT 2, 'Main', 0, 0, 1, 0 UNION ALL SELECT 6, 'Cat', 2, 0, 1, 0 UNION ALL SELECT 13, 'Sub Cat 1', 6, 1, 1, 2 UNION ALL SELECT 14, 'Sub Cat 2', 6, 1, 1, 1 UNION ALL SELECT 5, 'Cat 2', 2,0, 1, 0 UNION ALL SELECT 15, 'Sub Cat 1', 5, 1, 1, 2 UNION ALL SELECT 16, 'Sub Cat 2', 5, 1, 1, 1; WITH Tree (ID, Name, ParentID, Depth, Sort, Active, DisplayOrder) AS ( SELECT ID, Name, ParentID, 0 AS Depth, CONVERT(varchar(255), Name) AS Sort, Active, DisplayOrder FROM @Categories WHERE ParentID = @CategoryID UNION ALL SELECT CT.ID, CT.Name, CT.ParentID, Parent.Depth + 1 AS Depth, CONVERT(varchar(255), Parent.Sort + ' > ' + CT.Name) AS Sort, CT.Active, CT.DisplayOrder FROM @Categories CT INNER JOIN Tree as Parent ON Parent.ID = CT.ParentID WHERE (@ExcludeInactive = 0 OR (CT.Active = 1 )) ) SELECT ID, Name, ParentID, Depth, Sort, Active, DisplayOrder FROM Tree ORDER BY SORT

Current output:

 ID Name ParentID Depth Sort Active DisplayOrder 6 Cat 2 0 Cat 1 2 13 Sub Cat 1 6 1 Cat > Sub Cat 1 1 2 14 Sub Cat 2 6 1 Cat > Sub Cat 2 1 1 5 Cat 2 2 0 Cat 2 1 1 15 Sub Cat 1 5 1 Cat 2 > Sub Cat 1 1 2 16 Sub Cat 2 5 1 Cat 2 > Sub Cat 2 1 1

Desired Result:

 ID Name ParentID Depth Sort Active DisplayOrder 5 Cat 2 2 0 Cat 2 1 1 16 Sub Cat 2 5 1 Cat 2 > Sub Cat 2 1 1 15 Sub Cat 1 5 1 Cat 2 > Sub Cat 1 1 2 6 Cat 2 0 Cat 1 2 14 Sub Cat 2 6 1 Cat > Sub Cat 2 1 1 13 Sub Cat 1 6 1 Cat > Sub Cat 1 1 2
+4
source share
1 answer

There is probably a more elegant way to do this, but you can do something like:

 DECLARE @CategoryID INT; DECLARE @ExcludeInactive BIT; SET @CategoryID = 2; SET @ExcludeInactive = 0; Declare @Categories Table ( ID INT, Name VARCHAR(500), ParentID INT, Depth INT, Active BIT, DisplayOrder INT ); INSERT INTO @Categories SELECT 2, 'Main', 0, 0, 1, 0 UNION SELECT 6, 'Cat', 2, 0, 1, 0 UNION SELECT 13, 'Sub Cat 1', 6, 1, 1, 2 UNION SELECT 14, 'Sub Cat 2', 6, 1, 1, 1 UNION SELECT 5, 'Cat 2', 2,0, 1, 0 UNION SELECT 15, 'Sub Cat 1', 5, 1, 1, 2 UNION SELECT 16, 'Sub Cat 2', 5, 1, 1, 1; WITH Tree (ID, Name, ParentID, Depth, Sort, Active, DisplayOrder) AS ( SELECT ID, Name, ParentID, 0 AS Depth, CONVERT(varchar(255), Name) AS Sort, Active, DisplayOrder FROM @Categories WHERE ParentID = @CategoryID UNION SELECT CT.ID, CT.Name, CT.ParentID, Parent.Depth + 1 AS Depth, CONVERT(varchar(255), Parent.Sort + ' > ' + CT.Name) AS Sort, CT.Active, CT.DisplayOrder FROM @Categories CT INNER JOIN Tree as Parent ON Parent.ID = CT.ParentID WHERE (@ExcludeInactive = 0 OR (CT.Active = 1 )) ) SELECT ID, Name, ParentID, Depth, Sort, Active, DisplayOrder FROM Tree ORDER BY SUBSTRING(Sort,1,CHARINDEX(' >',Sort+' >')) DESC, DisplayOrder

Please note that all those UNIONS must be ALL , but I cannot publish them due to the firewall.

0
source

Source: https://habr.com/ru/post/1491526/

More articles:


All Articles