SQL Querying Sitecore Database Directly: Ordered Content Tree

Unleash the Potential of Power Platform With a Center of Excellence

Business innovation often comes from within. Discover how to empower innovation from non-traditional developers with the Microsoft Power Platform.

Learn More

In my last blog post, I gave you a SQL function that will return the FullPath of a given Sitecore Item.  Another common set of information one might want to retrieve from the Sitecore DB is the descendent items in the proper order as they show in the Sitecore Content Tree.  The trick to this query is not necessarily retrieving the right set of Items, but ensuring that they are in the proper order.  Keep reading to see the query and some notes about the order.

USE Sitecore_Master
DECLARE @ItemID UNIQUEIDENTIFIER
-- this is the starting point you want in your recursion
SET @ItemID = '{11111111-1111-1111-1111-111111111111}';
WITH ItemHierarchy AS (
    SELECT ID AS ItemID, ParentID, Name AS ItemName, 0 AS ItemLevel,
        CAST('000' AS VARCHAR(MAX)) AS ItemRank
    FROM Items
    WHERE ID = @ItemID
    UNION ALL
    SELECT i.ID AS ItemID, i.ParentID, i.Name AS Itemname, ItemLevel + 1 AS ItemLevel,
        ih.ItemRank + '_' +  RIGHT('000'+ CAST(rank() OVER (ORDER BY (
            SELECT ISNULL(f.Value,100)
            FROM Fields AS f
            WHERE f.ItemID = i.ID
                AND f.FieldID = 'BA3F86A2-4A1C-4D78-B63D-91C2779C1B5E'
                --ID of __Sortorder Field
        ), i.Name) AS VARCHAR(MAX)),3) AS ItemRank
    FROM Items AS i
    INNER JOIN ItemHierarchy AS ih ON ih.ItemID = i.ParentID  -- this is the recursion
)
SELECT ih.ItemID, ih.ItemName, dbo.GetItemPath(ih.ItemID, '') AS FullPath, ih.ItemRank, ih.ItemLevel, ih.ParentID
FROM ItemHierarchy AS ih
ORDER BY ItemRank

The ItemRank field is the key to making sure the results return in the proper order.  One note around the way the query is currently written is that it only handles 999 or less items in any node.  Of course, if you have more than 99 items in any given node of Sitecore, you should be using an Item Bucket for that node to avoid performance degradation.  Therefore, I really, really hope you’re using Item Buckets for any nodes that contain 1000 or more items.  If you are using Item Buckets, then there really is no “order” for that particular node anyway.
An example where we’ve used a query similar to the above is on Content Audit Reports.  The Content Author (or whomever has the appropriate permissions to view the report) chooses a spot in their Content Tree and starts the report, which shows all the descendants of the starting point (inclusive) and when the items were created / last updated.