Monday, September 8, 2014

Querying Items from Sitecore

Sitecore data can be retrieved using following approaches
  •          Sitecore Query
  •          Fast Query
  •          Lucene

Sitecore Query: Sitecore Query is most flexible in terms of filtering items right in the query using XPath functions. However, the more complex your query is, the longer it will take to run. This should be used when we try to query less than 100 items.

Fast Query: Sitecore fast query is designed for retrieving and filtering items from the Sitecore database. Sitecore Fast Query uses the database engine to execute queries. Sitecore Fast Query has the following benefits compared to the standard Sitecore Query
Improved performance – queries are executed by the SQL engine and as a result the scalability and performance of the SQL engine is not limited by .NET or by Sitecore.
 Consumes less memory – Sitecore Query loads every item that it touches into memory (cache) and this can fill the cache with unnecessary information. Sitecore Fast Query only loads the items from the result set and this minimizes the pressure on the cache.
Sitecore Fast Query is similar to XPath and Sitecore Query statements in many ways. The most notable difference in using Fast Query is that you use the fast: keyword. Sitecore fast query should be used when we try to query more between 100 to 1000 items.

Lucene: Lucene uses search indexes to query content and filter. This requires additional configuration upfront and maintenance of search index.

General syntax for Sitecore Query

Symbol
Meaning
/
The root of the content tree or parent-child relationship
//
Selects items from descendants of the specified item
..
Selects parent of the specified item
*
Wildcard, match items of any name
[ ]
Search criteria related to fields and XML element attributes
@
A field defined in the item’s base template
@@
An XML element attribute. All Sitecore items contain following attributes
·         name : The item’s name
·         key : The item’s name in all lower-case characters
·         id : The item’s GUID
·         tid : The item’s base template’s GUID
·         mid : The branch template used to create item, if any
·         sortorder: The item’s sort order
·         templatename : The item’s base template’s name
·         parented : The item’s parent’s GUID

To work with Sitecore queries create following structure based on two templates Category & MobileCategory having 3 fields.


/sitecore/content/Common/Categories/*
Grabs all children under Categories item

/sitecore/content/Common/Categories/*[@@templatename='Category']
Grabs all children of template Category under Categories item

/sitecore/content/Common/*/*[@@templatename='Category']
Checks all subfolders under folder Common and grabs all items of templatename Category

/sitecore/content/Common/Categories/*[@@templateid='{3B0461BF-9ABC-4AF1-B937-C8D225FC2313}']
/sitecore/content/Common/Categories/*[@@tid='{3B0461BF-9ABC-4AF1-B937-C8D225FC2313}']
Grabs all children of a specific template ID under Categories item
tid is the item's base template's GUID

/sitecore/content/Common/*/*[@@name='Electronics']
Grabs all items with name Electronics (exact match).

/sitecore/content/Common/*/*[@@key='electronics']
Grabs all items with name Electronics (items name in lower case characters)

/sitecore/content/Common/*/*[@@id='{E00B1D51-B118-480A-A10F-0CE4BF560B5B}']
Grabs an element with item id under Common item

fast:/sitecore/content/common/*/*[@@parentid='{A85663D5-B794-4B27-A4F9-B1A62241E8E1}']
Grabs all the descendants of the Common item whose parent item has the specified ID
@@parentid only works in fast query.

/sitecore/content/common//Electronics/..
Grabs the parent of the Electronics item that is stored under Common item

/sitecore/content/common/*/*[@Category Name='Electronics']
Grabs all items whose field "Category Name" has a value "Electronics"

/sitecore/content/common/*/*[@IsAvailable='1']
Grabs all items under Common item which have the IsAvailable check box set to true

/sitecore/content/common/*/*[@@templatename='Category' and @IsAvailable='1']
Grabs all items under Common item which template is Category and IsAvailable is set to true

fast:/sitecore/content/common/*/*[@Category Name='%Elec%']
Grabs all items under Common item whose Category Name contains string "Elec"

/sitecore/content/common/#Old-Categories#/*
"#" is used to escape special characters or special words like "and" "or".
Grabs all items under "Old-Categories" item

/sitecore/content/common/Categories/*[startswith(@Category Name,'E')]
fast:/sitecore/content/common/Categories/*[@Category Name = 'E%']
Grabs immediate sub items under Categories whose field "Category Name" starts with letter "E"
fast query doesnt support XPath functions

XPath functions
  •          position () Determine the numeric position of the node
  •         last () Determine the last node in the node set
  •          concat () Concatenate two strings together
  •          starts-with () Determine if a string begins with another string
  •          contains () Determine if a string contains another string
  •          not () Negate a Boolean value

/sitecore/content/common/Categories/*[position () > 2]
Grabs immediate sub items under Categories whose position is greater than 2

/sitecore/content/common/Categories/*[last ()]
Grabs the last node in Categories list

/sitecore/content/common/Categories/*[contains (@Category Name,'E')]
Grabs immediate sub items under Categories whose Category Name contains character ‘E’

/sitecore/content/common/Categories/*[not (startswith (@Category Name,'E'))]

Grabs immediate sub items under Categories whose field "Category Name" does not starts with letter "E"

AXES

ancestor
Returns all the ancestors of the specified item (same as XPath)
parent
(..) Returns the parent item of the  specified item
child
(/*) Returns all the children of the specified item
descendant
(//*) Returns all the descendants of the specified item

The axis component of a query determines the direction of the node selection in relation to the context node.
/sitecore/content//*[@Category Name='Electronics']/ancestor::*
Returns all the ancestors of specified item

/sitecore/content//*[@Category Name='Electronics']/parent::*
Returns the parent Item of the specified item. (..) Similar in XPath

/sitecore/content//*[@Category Name='Electronics']/child::*
Returns all the children of the specified item. (/*) similar in XPath

/sitecore/content//*[@Category Name='Electronics']/descendant::*
Returns all the descendants (child or child of a child) of the specified item. (//*) similar in XPath

8 comments:

  1. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  2. informative article but, When I am using the following query based on your article I am not able to pull products by field "Discontinue Date" for treelistex sitecore field:
    /sitecore/content/Product Catalog/All-Products/*[@Discontinue Date < '20150330T000000']
    Can you please tell me what's wrong in this query.

    ReplyDelete
  3. very helpful ,thanks...

    ReplyDelete
  4. As per the above query I'm not able to get all children with specific template id. It only gives the 1st occurrence and not all of them.

    ReplyDelete
  5. how do i get Top 10 results in the query

    ReplyDelete