Editing an Entire Page Hierarchy

  • By Michael Garrison One Year Ago

There are times when you need to mass edit an entire hierarchy of pages. The most common example is when someone duplicates an entire site and needs to move the new pages to a new site, or un-mark the copies as IsSystem (which I believe will be a problem of the past, once v7 is out)

Here's a SQL routine you can use to get all pages which are descendants of a given PageId:

WITH PageHierarchy ([Id], [PageTitle], [ParentPageId], [Root], [LayoutId], [SiteId]) AS
(
    SELECT
        p.[Id]
        ,p.[PageTitle]
        ,p.[ParentPageId]
        ,p.[Id]
        ,p.[LayoutId]
        ,l.[SiteId]
    FROM
        [Page] p
        INNER JOIN [Layout] l ON l.[Id] = p.[LayoutId]
    UNION ALL
    SELECT
        p.[Id]
        ,p.[PageTitle]
        ,p.[ParentPageId]
        ,ph.[Root]
        ,p.[LayoutId]
        ,l.[SiteId]
    FROM
        [Page] p
        INNER JOIN [Layout] l ON l.[Id] = p.[LayoutId]
        INNER JOIN PageHierarchy ph ON p.[ParentPageId] = ph.[Id]
)

SELECT * From PageHierarchy WHERE [Root]=1234

Set the PageId you want the descendants of in the last line, and run it- you'll get a list of the descendant Page Ids, the Page Names, the ultimate root page (which will always be the number you specified, unless you omit the where clause), and also the LayoutId and SiteId of the page. Those last two aren't necessary for just getting a list of descendants, but they can be instrumental in some of the most common reasons you might want the list.

(Here's an example from the demo site, where the root page was 5408):

Page-CTE-1.JPG

If you just want to set all the resulting pages to IsSystem=0 (so you can delete unnecessary pages, for instance), you could replace that last line with UPDATE [Page] SET [IsSystem]=0 WHERE [Id] IN (SELECT [Id] FROM PageHierarchy WHERE [Root]=1234)

Moving an entire hierarchy to a new site

If you duplicate, say, the external website, you may notice that all the new pages still *belong* to the external site. You can create a new site and set the default page to your copied external site homepage, and still nothing will have *actually* moved to the new site. We need to do this by changing the layout each page uses, which are site-specific and so determine which site the page belongs to.

So ultimately for each page in the above list, we want to look up the LayoutId for the layout on the new site whose name matches the existing layout name. (Which unfortunately isn't as easy as it should be, because sometimes the layouts have spaces in their names and sometimes they don't- it's different from site to site). So we'll just remove the spaces from all the names before running the comparison.

So here's a sample routine which will move an entire hierarchy to a new site, PLUS un-mark them as IsSystem on the way:

DECLARE @RootPage AS INT = 1234;
DECLARE @TargetSite AS INT = 7;
WITH PageHierarchy ([Id], [PageTitle], [ParentPageId], [Root], [LayoutId], [SiteId]) AS
(
    SELECT
        p.[Id]
        ,p.[PageTitle]
        ,p.[ParentPageId]
        ,p.[Id]
        ,p.[LayoutId]
        ,l.[SiteId]
    FROM
        [Page] p
        INNER JOIN [Layout] l ON l.[Id] = p.[LayoutId]
    UNION ALL
    SELECT
        p.[Id]
        ,p.[PageTitle]
        ,p.[ParentPageId]
        ,ph.[Root]
        ,p.[LayoutId]
        ,l.[SiteId]
    FROM
        [Page] p
        INNER JOIN [Layout] l ON l.[Id] = p.[LayoutId]
        INNER JOIN PageHierarchy ph ON p.[ParentPageId] = ph.[Id]
)

UPDATE [Page] SET [IsSystem]=0 [LayoutId]=gt.[TargetLayoutId] FROM [Page] AS p INNER JOIN ( SELECT ph.[Id] ,l.[Id] "TargetLayoutId" From PageHierarchy ph LEFT JOIN [Layout] l ON REPLACE(l.[Name],' ','') = (SELECT REPLACE([Name],' ','') FROM [Layout] WHERE [Id]=ph.[LayoutId]) AND l.[SiteId]=@TargetSite WHERE ph.[Root]=@RootPage ) AS gt ON gt.[Id]=p.[Id]

Whew! Set the two variables in the first two lines, and that's it!

In the common example of someone having copied the entire external site, there are a few things to be aware of: the Navbar settings still point at the original External Homepage... this has to be updated on *each* layout. Also the logo link is hardcoded to /page/1. I'm sure there are more, these were just the obvious ones I saw.

One more item which can cause some confusion: this won't change the ParentId of the root page, so in your page map they'll still show up where they were. But you can change the Parent Page from the root page's page settings, and the whole hierarchy will move along with it.

I cannot stress strongly enough that this should be TESTED on a COPY of your data before you use it in production! And make a backup before you use it in production.

Thanks to Jesse McColm and Peter Schott for their assistance in Slack in developing this!


@mikejed
Spark Development Network
Flagstaff, AZ

Michael Garrison recently left his job in Architecture to become one of the "new guys" at Spark (the "Core Team"), but he's still helping out Christ's Church of Flagstaff and other non-profits with tech needs in his off-hours, trying to make computers do what computers do best, so that people are freed to do what we do best: relate with people!