Jeremy Davis
Jeremy Davis
Sitecore, C# and web development
Article printed from: https://blog.jermdavis.dev/posts/2016/getting-your-queries-in-order

Getting your queries in order

Published 02 May 2016
Updated 25 August 2016
Sitecore ~4 min. read

There are lots of scenarios where sorting the results of Sitecore API or search queries is easy. But there's one scenario that I've come across a couple of times can be a bit trickier than the usual "sort by date" scenario.

The scenario

ContentStructure Imagine you have a folder structure holding a set of content items. Maybe something like the tree on the left here. They're ordered and organised into folders based on something that makes sense to your content editors. This content is going to be processed using a sort order that should match the content-tree order.

I've come across this requirement for a search interface for training courses – where the default sort needed to be in "marketing importance" order. But it could equally well be used in the case of ordering the redirect rules I discussed recently.

Out of the box, Sitecore has an approach to sorting content in its tree – the Standard Fields for each item have a __sortorder field defined, and the default sort for the content tree orders by that field. When you move items up and down to change their order, that value is updated to try and ensure all the items in that folder sort correctly. This approach works ok for sorting the results of a query if all the items are in the same folder. However with our data in this example, the query starts from /sitecore/content/Home/Sortable Content Items and looks down the tree – hence it is looking at many folders. So it could return multiple items with the same __sortorder because these values are calculated per-folder, and different folders may duplicate the same value.

And if the values in the sort field can repeat, you can't sort the result of your query by that field and expect to get items in a predictable order. So, what can you do about it?

One possible solution

If you start at the top of the tree, and go down through each item, you can concatenate together the __sortorder values into a string. If you format each one so it's the same number of digits, and ensure all the strings are the same length, then you end up with something that's sortable no matter what folder the item is in. For example, with the content tree above: (The __sortorder values for the individual items are in bold to show how they fit in to the overall tree)

For example:

  • Sortable Content Items: 000000000000
    • First Item: 000150000000
    • Folder One: 000250000000
      • Second Item: 000250150000
      • Third Item: 000250175000
      • Fourth Item: 000250200000
    • Folder Two: 000350000000
      • Fifth Item: 000350200000
      • Folder Three: 000350300000
        • Sixth Item: 000350300300
        • Seventh Item: 000350300400

So if we run a query for items under /sitecore/content/Home//* that are pages rather than folders and then order it by this calculated value, we get:

  • First Item: 000150000000
  • Second Item: 000250150000
  • Third Item: 000250175000
  • Fourth Item: 000250200000
  • Fifth Item: 000350200000
  • Sixth Item: 000350300300
  • Seventh Item: 000350300400

And as long as the value gets re-calculated whenever items are moved around in the content tree, this should keep working.

So how can this be calculated?

For each item item we need a fixed length for the sort string. That's made up of two parts: the depth of the content tree that we're going to process, and the number of digits that will be used to format each __sortorder. These values probably need tuning to match the scale of the content being processed, so making them easy to vary seems sensible:

private int MaxLevelDepth = 5;

private int FormatStringDigits = 3;
private string FormatString;
private string DefaultString;

public ItemEventHandler()
{
    DefaultString = new string('0', FormatStringDigits);
    FormatString = "{0:" + DefaultString + "}";
}

					

Each time an item needs processing, it needs to do four things:

private void updateItemSortString(Item item)
{
    string newSortString = generateSortString(item);

    newSortString = ensureCorrectLength(newSortString);

    updateItem(item, newSortString);

    processChildren(item);
}

					

First, it needs to generate the sorting string for the current item. Since this string requires the sort information of the parent items, it's a recursive function:

private string generateSortString(Item itm)
{
    if(!itm.Template.BaseTemplates.Any(i => i.ID == CrossFolderSortingTemplateID))
    {
        return string.Empty;
    }

    Field srt = itm.Fields[Sitecore.FieldIDs.Sortorder];

    string result = DefaultString;
    int sortValue = 0;
    if(int.TryParse(srt.Value, out sortValue))
    {
        result = string.Format(FormatString, Math.Abs(sortValue));
    }

    result = generateSortString(itm.Parent) + result;

    return result;
}

					

First the code needs to check if the item being processed has the field we're going to record the sort order into. That's a string field in a base template that has been added to the content and folders items in our tree:

Sort Template

If the item doesn't include our base template, then there's nothing to do.

Otherwise it grabs the value in the __sortorder field, turns it into an integer, and formats it with the format string described above. The final result of the function is then worked out with a recursive call to process the parent item. And the recursion stops when we hit an item who does not have the right base template.

After this is calculated, the value is saved into the base template mentioned earlier:

private void updateItem(Item item, string sortFieldValue)
{
    Field fld = item.Fields[CrossFolderSortingFieldID];

    if (fld != null)
    {
        using (new EditContext(item))
        {
            fld.Value = sortFieldValue;
        }
    }
}

					

Finally, we know that these special sort order values for any children of the current item will depend on the changes to the current item, so the code needs to process those too:

private void processChildren(Item item)
{
    foreach (Item it in item.Children)
    {
        if (!it.Template.BaseTemplates.Any(i => i.ID == CrossFolderSortingTemplateID))
        {
            continue;
        }

        updateItemSortString(it);
    }
}

					

So how does this code get run?

The code above depends on Sitecore's __sortorder value, so we want our code to get triggered whenever that might change. Events are the answer to this, and the set that seem related to items and how they might be sorted are:

  • OnCreated
  • OnCopied
  • OnMoved
  • OnSortOrderChanged

So each of those needs an event handler method:

public void OnCreated(object sender, EventArgs args)
{
    SitecoreEventArgs sea = args as SitecoreEventArgs;
    ItemCreatedEventArgs ica = Event.ExtractParameter<ItemCreatedEventArgs>(sea, 0);

    updateItemSortString(ica.Item);
}

public void OnCopied(object sender, EventArgs args)
{
    SitecoreEventArgs sea = args as SitecoreEventArgs;
    ItemCopiedEventArgs ica = Event.ExtractParameter<ItemCopiedEventArgs>(sea, 0);

    updateItemSortString(ica.Copy);
}

public void OnMoved(object sender, EventArgs args)
{
    SitecoreEventArgs sea = args as SitecoreEventArgs;
    Item item = Event.ExtractParameter<Item>(args, 0);

    updateItemSortString(item);
}

public void OnSortOrderChanged(object sender, EventArgs args)
{
    SitecoreEventArgs sea = args as SitecoreEventArgs;
    Item item = Event.ExtractParameter<Item>(args, 0);

    updateItemSortString(item);
}

					

And then these methods can be configured to be called when the events occur with a config patch:

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <events>
      <event name="item:created">
        <handler type="DevTest.Sorting.ItemEventHandler, DevTest.Sorting" method="OnCreated"/>
      </event>

      <event name="item:copying">
        <handler type="DevTest.Sorting.ItemEventHandler, DevTest.Sorting" method="OnCopied"/>
      </event>
      
      <event name="item:moved">
        <handler type="DevTest.Sorting.ItemEventHandler, DevTest.Sorting" method="OnMoved"/>
      </event>
      
      <event name="item:sortorderchanged">
        <handler type="DevTest.Sorting.ItemEventHandler, DevTest.Sorting" method="OnSortOrderChanged"/>
      </event>
    </events>
  </sitecore>
</configuration>

					

With that lot in place, moving items around which have the special base template will cause their special sort order to be updated...

Caveats

While this can be a useful approach for content order sorting, it's not perfect, and there are some scenarios this idea doesn't cope with.

  • For starters it won't work with items in buckets, as "content order" doesn't really apply to them.
  • The default __sortorder value for a newly created item is empty - which isn't helpful. Ideally it should be the right value, based on the other items in the folder. This means that by default content editors need to make sure they move stuff to the "right" position in the tree, even when the item first appears in an acceptable location. That ensures the __sortorder field gets the correct value.
  • The code above uses Math.Abs() because this approach doesn't really cope with negative numbers. But it's quite possible that __sortorders value can be negative. That would mess up this approach.
  • It's sensitive to the depth of your content tree, and the volume of content you're sorting – due to the need to make sure all the sort strings are the same length. If the volume or depth of the content excedes the values you configure for formatting the sorting strings, the sort won't work.
  • The example code above isn't that efficient. The need to recurse up the content tree and the need to re-calculate an entire child tree when a parent changes its __sortorder lead to lots of item reads. I think it should be possible to make this code more efficient though.

But despite those points, maybe some of you out there might find it useful in your work too...

↑ Back to top