X

Register now for unlimited access to Sitecore resources.


Already have an account? Log in now

*{0} must be filled in.
*{0} must be filled in.
*{0} must be filled in.
*{0} must be filled in.
*{0} must be filled in.
*{0} must be filled in.
*{0} must be filled in.
X

Request a demo

It’s easy to get started. Sign up for a personalized demo.

*{0} must be filled in.
*{0} must be filled in.
*{0} must be filled in.
*{0} must be filled in.
*{0} must be filled in.
*{0} must be filled in.
*{0} must be filled in.
Sitecore Blog: @sitecorejohn blog

Sitecore Query Cheat Sheet

By John West, May 07, 2012 | Rating:  | Comments (7)

This blog post contains information to help you use Sitecore query to select items in the Sitecore ASP.NET web Content Management System (CMS) and provides a cheat sheet that contains shortcuts to reduce the number of times you may need to refer to the documentation. For more information about Sitecore query, see Using Sitecore Query on the Sitecore Developer Network (SDN).

You can use Sitecore query to select items that meet specific criteria from a Sitecore database. Sitecore query uses a syntax based on XPath to access something like an XML representation of a Sitecore database. Unlike SQL queries, this technique abstracts the underlying database technology; like most things in Sitecore, Sitecore queries that you develop with one database technology (such as Microsoft SQL Server) work without modification in Sitecore solutions that use other database technologies (such as Oracle).

There are two primary use cases for Sitecore query. You can use a query to select items for your code to process, and you can use a query in the Source property of a selection fields in data templates. In your code, you can use the SelectSingleItem() method of the Sitecore.Data.Database class to select a single item that matches a query, or the SelectItems() method to access any number of items that match the query. You can use the same methods of the Axes property of the Sitecore.Data.Items.Item class to execute queries, which can be relative to that Sitecore.Data.Items.Item, such as ..//* to select all descendants of the parent of that item. In either case, the Query.MaxItems setting in the Web.config file specifies the maximum number of items that either SelectItems() method can return. You can update the value of this setting to 0 to remove any limit, though this may reduce performance in some cases. You can also use the Sitecore.Data.Query.Query class to construct queries. The Max property of this class lets you override the Query.MaxItems setting for individual queries. For an example that uses this class and sets this property, see the blog post Apply Rules to All Items in the Sitecore ASP.NET CMS, which also shows how to query for a large number of items without creating a large array of Sitecore.Data.Items.Item objects.

To specify a query in the source property of a data template field, use the query: prefix, for example query:/sitecore/content/home//*[@templatekey='my template']. For more information about the Source property of data template fields, see The Sitecore Data Definition Reference and The Sitecore Data Definition Cookbook. Sitecore uses the getLookupSourceItems pipeline to determine the items to include in selection fields. Therefore, to extend Sitecore query for use in the Source property of selection, you can add and override processors in this pipeline. For an example of such a processor, see the blog post Reference the Context Item and System Date in the Source Property of Sitecore ASP.NET CMS Fields. For more information about pipelines, see the blog post All About Pipelines in the Sitecore ASP.NET CMS.

In addition to evaluating queries in code and in the Source property of data template fields, you can evaluate queries with the browser-based Developer Center applicaiton and the Sitecore Rocks plugin for Visual Studio. I highly recommend Sitecore Rocks over Developer Center. For more information about Sitecore Rocks, see the blog post Sitecore Differentiating Factors Blog Series: Sitecore Rocks. For more information about the query analyzer in Sitecore Rocks, see the blog post Sitecore Rocks Queries.

To evaluate a query in the Developer Center:

  1. Log in to the Sitecore desktop as a user with sufficient access.
  2. To access the Developer Center, click the Sitecore button, click Development Tools from the menu that appears, and then click Developer Center from the submenu that appears. The Developer Center appears.
  3. Click the Tools menu, and then click XPath Builder.
  4. Select Sitecore Query Notation, enter your query in the XPath Expression field, and then click Evaluate. Sitecore evaluates the query relative to the item selected in the Context Node field.

Note that XPath queries, which are not within the scope of this blog post, access the XML representation of the database used by XSL renderings, which differs from that used by Sitecore query.

To evaluate a query in Sitecore Rocks:

  1. In Sitecore Explorer, right-click a database or item, click Tools from the menu that appears, and then click Query Analyzer from the submenu that appears.
  2. Enter a Sitecore Rocks select statement, and after in the from clause, specify your query. Then click Evaluate. For example:
select @@path, @@name as Name, @@templatekey as TemplateKey, @@id as ID, @Title, @Text
from /sitecore/content//*[@@templatekey = 'sample item']

To access a tool equivalent to Developer Center’s XPath Builder in Sitecore Rocks, in Sitecore Explorer, right-click a database or item, click Tools from the menu that appears, and then click XPath Builder from the submenu that appears.

You can use the solution described in the blog post Apply Rules to All Items in the Sitecore ASP.NET CMS to log all items selected by a query. For more information about logging with Sitecore, see the blog post All About Logging with the Sitecore ASP.NET CMS.

As listed in the cheat sheet, Sitecore query supports various axes, functions, and operators similar to those used in XPath. The contains() method lets you determine if a field that contains a list of item IDs contains the ID of a specific item:

.//*[contains(@ChecklistMultiselectTreelistOrTreelistExFieldName, '<ID>')]

You must use hash characters (#) to escape these tokens when they appear in item and field names, for example /sitecore/content//*[@#field-name#='value']. In the Sitecore Developer Network (SDN) forum thread Reserved words in sitecore query, Benjamin Vangansewinkel provides a method to escape Sitecore queries automatically, but I have not tried that method.

Unlike XPath, Sitecore query designates attribute names with two at characters (@@), such as @@id for the ID of an item. A single at character (@) designates a field name, such as @Title for a field named Title. You can access fields defined in the standard template just like those that you define in your own data templates, such as @@__Updated to access the last modification date for an item. You can also use a single at character (@) to specify the ID of a field, but because IDs contain dash characters (-), you must escape the ID with hash characters (#), such as @#{D9CF14B1-FA16-4BA6-9288-E8A174D4D522}#. While you can determine the names and IDs of fields defined by the standard template from the Inheritance tab that appears when you select a template definition item in the Content Editor, for convenience, the cheat sheet list the names of some standard template fields that developers frequently use in queries. For more information about the standard template, see The Sitecore Data Definition Reference.

To compare dates with Sitecore query, convert the date to the "ISO" format used by Sitecore (yyyyMMddTHHmmss; for more information about date format specification, see Custom Date and Time Format Strings). You can use the Sitecore.DateUtil.ToIsoDate() static method to convert a System.DateTime object to the required format. This format allows sorting of dates as strings. For example, to retrieve children of the /sitecore/content item updated on May 13, 2012:

/*/content/*[@__Updated >= '20120513T000000' and @__Updated < '20120514T000000']

Sitecore query is case-sensitive in value comparisons, but not item and field name comparisons. For example, the queries /sitecore/content/*/@__updated > '20120505T080000'] and /Sitecore/Content/*/@__Updated > '20120505T080000'] are equivalent, but /sitecore/content/*[@__updated by = 'sitecore\admin'] and /sitecore/content/*[@__updated by = SITECORE\admin'] are not.

In some cases, Sitecore query is not the most efficient technique to select items. Whenever possible, select items by ID or explicit path using the GetItem() method of the Sitecore.Data.Databse class rather than using queries. To minimize the number of items that Sitecore must evaluate, use relative paths whenever possible or include the longest path that you can in the query. In cases where Sitecore query does not perform well, consider using a search index to select items instead. For information about Sitecore’s Lucene search indexing solution, see The Sitecore Search and Indexing Guide.

In some cases, Sitecore fast query may be more efficient than query both in terms of Central Processing Unit (CPU) usage and memory consumption, though fast query presents some limitations and may be slower than Sitecore query for some purposes. Sitecore translates fast queries directly into database queries. For more information about fast query, see The Sitecore Guide to Fast Query. To specify fast query to an API, use the fast: prefix, such as fast:/sitecore/content/*[@__Updated < '20120514T000000']. To specify a fast query in the Source property of a data template field, use the query:fast: prefix, such as query:fast:/sitecore/content/*. Limitations of fast query include:

  • Fast query does not account for the context language (results include items with versions that match the query in any language)
  • Fast query does not support sorting (sort results after querying)
  • In a fast query, attributes should not appear at the beginning of a predicate, directly after the opening square bracket character ([)
  • Fast query supports only the parent, child, ancestor, and descendant axes, the latter two only when the FastQueryDescendantsDisabled setting is false
  • Fast query does not support standard values (and I doubt clones either; for more information about clones, see the blog post Sitecore CMS 6.4 Cloning)
  • Fast query does not support functions, though it provides an alternative to the contains and therefore I assume the startswith() and endswith() functions
  • Fast query does not support operators
  • Fast query does not support subqueries, which are queries within predicates (square brackets – [] characters)
  • Fast Query works for data providers that inherit from the SqlDataProvider class and that support SQL queries generated by the QueryToSqlTranslator class
  • Fast query does not support query catenation

In addition to the attributes supported by Sitecore query, fast query supports the @@parentid attribute, which can be useful in cases where you might otherwise use a subquery. In summary, I would avoid fast query Sitecore query underperforms and you have very specific requirements met despite these limitations. I believe Sitecore query may be especially poor for queries that process small branches of items, as I assume it queries the entire database and then filters the results by branch.

While this post is all about Sitecore query, and all I could find about Sitecore query, I didn’t title this post all about Sitecore query because I don’t think it contains everything one could know about Sitecore query (though I did add it to the All About the Sitecore ASP.NET CMS series). Please comment on this blog post if you spot an error, have a good example of a query, a suggestion or the cheat sheet, know of a good library to use with Sitecore query, or have any questions on this topic or additional information to add.

Tags: API, Architecture, Information Architecture, Infrastructure

Comments

  • See also:

    http://www.sitecore.net/Community/Technical-Blogs/John-West-Sitecore-Blog/Posts/2012/05/Extending-Sitecore-Query.aspx

    - John West
    May 07, 2012 at 12:37 PM

  • In some cases, you may need to escape field names that contain spaces, such as @#Updated by#.

    - John West
    May 08, 2012 at 2:22 PM

  • Example queries for Sitecore Rocks that you can adapt to Sitecore query:

    http://vsplugins.sitecore.net/Sitecore-Query-Analyzer-Samples.ashx

    - John West
    May 08, 2012 at 2:26 PM

  • Great post, but I think you missed the point of a "Cheat Sheet". Traditionally, it should be about the size of a 3 x 5 recipe card - something that you can easily sneak into an exam (not that I'm advocating such behavior).

    In my case, 8 x 11 would also be acceptable. The key is that it should be very concise information that I could pin up on my wall and look up at a glance.

    - Philip Vaartstra
    October 30, 2012 at 7:58 AM

  • http://www.sitecore.net/Community/Technical-Blogs/John-West-Sitecore-Blog/Posts/2013/02/Numerical-Comparison-in-Sitecore-Query.aspx

    /sitecore/content/home/*[number('0' + @NumberField) >= 1 and number('0' + @NumberField) <= 5]

    That actually belongs in the cheat sheet, which I intend to update if there are enough comments.

    - John West
    February 06, 2013 at 12:11 PM

  • Hi John. So you cheated on the size of the cheat sheet, it's a cheat cheat sheet :)

    Anyway, I use the following to escape my paths to use in a query:
    string startPath = Regex.Replace(myItem.Paths.FullPath, "[^/]*-[^/]*", "#$0#");

    Doesn't work on field names, though. Should probably be possible, but didn't have the time or need for it atm.

    - Matthijs Wensveen
    October 16, 2013 at 2:17 AM

  • Sorry. Regex needs to be "[^/]*[- ][^/]*", because spaces are also forbidden.

    - Matthijs Wensveen
    October 16, 2013 at 2:22 AM

Commenting is temporary disabled due to maintenance work.

*{0} must be filled in.
*{0} must be filled in.
*{0} must be filled in.