Microsoft

SQL Querying Sitecore Database Directly: Ordered Content Tree

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.

Thoughts on “SQL Querying Sitecore Database Directly: Ordered Content Tree”

  1. Pingback: relational database

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Jamie Stump

My name is Jamie Stump, and I am a Senior Sitecore Consultant at Perficient. I was honored to be named one of only 42 2013 Sitecore MVP’s worldwide. I specialize in Sitecore Architecture and Development and my broad Sitecore experience includes Sitecore installation, configuration and CEP development, including custom DMS implementations for clients. I have implemented Sitecore solutions for a number of industry verticals including manufacturing, healthcare, financial services, advertising and retail. In addition to architecting and implementing Sitecore sites and eCommerce solutions, I also work with other Microsoft Technologies, including the .NET platform and SQL Server. You can read through my older Sitecore related blog posts here and my newer ones here. I graduated with a Bachelor of Science in Information Systems Development from York College of PA. I am originally from the suburbs of Philadelphia, PA, and still reside there with my wife, son, English bulldog and 2 cats.

More from this Author

Subscribe to the Weekly Blog Digest:

Sign Up
Categories
Follow Us
TwitterLinkedinFacebookYoutubeInstagram