I am trying to create a table to manage parent/child relationships for a website. This is not too difficult if it were for one site, however I would like to use this table for multiple sites, as well as subsites.
For example, I have a website ALPHA, a website BETA as well as a Website GAMMA, but the root of website GAMMA is also a page in website ALPHA.
| ALPHA |
BETA |
GAMMA |
A
|-B
|-|-C
|-|-D
|-|-|-E
|-|-F
|-G
|-H
|
M
|-N
|-O
|-P
|-|-Q
|-|-R
|
B
|-C
|-D
|-|-E
|-F
|
As you can see above, site GAMMA’s root of B is also a child of site ALPHA’s A.
This is my schema
IF OBJECT_ID (N'Sites_GetTreeFromPage') IS NOT NULL
DROP FUNCTION Sites_GetTreeFromPage
GO
CREATE FUNCTION dbo.Sites_GetTreeFromPage (
@Page_id uniqueidentifier
, @Site_id uniqueidentifier = NULL
, @StartDepth int = 0
, @Depth int = NULL
) RETURNS @PageTree TABLE (
Page_id uniqueidentifier PRIMARY KEY NOT NULL
, L int NOT NULL
, R int NOT NULL
, D int NOT NULL
, Site_id uniqueidentifier NOT NULL
) AS
BEGIN
SELECT TOP 1 @Site_id = ISNULL(@Site_id, Site_id)
FROM Sites_Pages
WHERE Page_id = @Page_id
ORDER BY D ASC
INSERT INTO @PageTree (Page_id, L, R, D, Site_id)
SELECT
p.Page_id
, tree.L
, tree.R
, tree.D
, s.Site_id
FROM
Sites_Pages s_p
JOIN Sites_Pages tree ON tree.Site_id = s_p.Site_id AND tree.L >= s_p.L AND tree.R <= s_p.R
JOIN Pages p ON tree.Page_id = p.Page_id
JOIN Sites s ON s_p.Site_id = s.Site_id
WHERE
s_p.Page_id = @Page_id
AND s_p.Site_id = @Site_id
AND tree.D >= s_p.D+@startDepth
AND (@Depth IS NULL OR tree.D <= s_p.D+@Depth+@startDepth)
RETURN
END
GO
IF OBJECT_ID (N'Sites_GetPageTree') IS NOT NULL
DROP PROCEDURE Sites_GetPageTree
GO
CREATE PROCEDURE Sites_GetPageTree
@Page_id uniqueidentifier
, @Site_id uniqueidentifier = NULL
, @StartDepth int = 0
, @Depth int = NULL
AS
BEGIN
SELECT
p.Page_id
, p.Title
, REPLICATE('| - ', tree.D-@startDepth) + p.Title AS Tree
, tree.L
, tree.R
, tree.D
, s.Title AS Site
, CASE WHEN tree.L = 1 THEN 1 ELSE 0 END AS IsRoot
, CASE WHEN tree.R = tree.L + 1 THEN 1 ELSE 0 END AS IsLeaf
, CASE WHEN tree.R > tree.L + 1 THEN 1 ELSE 0 END AS IsParent
FROM
dbo.Sites_GetTreeFromPage(@Page_id, @Site_id, @StartDepth, @Depth) tree
JOIN Pages p ON tree.Page_id = p.Page_id
JOIN Sites s ON tree.Site_id = s.Site_id
ORDER BY
tree.L
, tree.R
END
GO
IF OBJECT_ID (N'Sites_GetPageChildren') IS NOT NULL
DROP PROCEDURE Sites_GetPageChildren
GO
CREATE PROCEDURE Sites_GetPageChildren
@Page_id uniqueidentifier
AS
BEGIN
EXEC Sites_GetPageTree @Page_id = @Page_id, @StartDepth = 1, @Depth = 0
END
GO
IF OBJECT_ID (N'Sites_GetSiteTree') IS NOT NULL
DROP PROCEDURE Sites_GetSiteTree
GO
CREATE PROCEDURE Sites_GetSiteTree
@Site_id uniqueidentifier
AS
BEGIN
DECLARE @Page_id uniqueidentifier
SELECT
@Page_id = RootPage_id
FROM
Sites
WHERE
Site_id = @Site_id
EXEC Sites_GetPageTree @Page_id = @Page_id, @Site_id = @Site_id
END
GO
I am trying to write stored procedures to interact with the Sites_Pages table, but it has been hurting my brain.
The complication is that I would like to perform all operations without having to run different queries for different Sites. Also, I don’t want to use a temporary table to store rows if I want to move Pages from one parent to another.
