Nitesh's blog on Tridion , Asp.Net

Custom Metadata represents the data you define using Metadata Schemas ( a Metadata schema can be associated with Templates, Pages, Components(Metadata Design tab) , Binaries ). On publishing items such as Pages, Components, their metadata, more appropriate to say associated Custom Metadata, is also published and stored in Content Delivery data store.

Refer to these blog posts of mine on Custom Metadata Overview and Querying Custom Metadata using Tom.Net API.

In this post we will see how to query Custom Metadata present at Content Delivery data store. Before that let’s have a look on the  Table(s), Database(s) which store such published Custom Metadata .

CUSTOM_META  Table:

This table is present in the Broker Database (tridion_broker) and stores the Custom Metadata published from  Content Manager. The metadata is stored in the form of Key Value pair  along with other useful data such as the Content Manager Item’s ID, Publication ID of the Item etc.

 

Below is the correspondence between the Metadata Schema Fields and the Columns of the table we are interested in:

1. Column: KEY_NAME  = Metadata field name
2. Column: KEY_STRING_VALUE = The string value of the Metadata field
3. Column: ITEM_TYPE = the type of Content Manager Item, e.g 16 for Component
4. Column: ITEM_ID =  the ID of the Content Manager item ( Middle Part of a TCM URI )
5. Column: PUBLICATION_ID = the Publication ID of the item

Thats all on the database and table details.

Note: The examples below uses a Component as a reference, however same can be used for Pages as well.

Now, suppose we develop a sample Component Schema whose Metadata design tab has a single field “Index”. Let say the value for this field will be selected from a list: True and False, as seen below::

When the above schema is used by a Component, Select one of the  values ( True or False ) thus setting a custom metadata for this component.

After publishing the component, the associated custom metadata ( Index – True) will get  published/stored to CUSTOM_META table ( Assuming proper entries in cd_storage_conf.xml are in place )

A very basic Select Query on the table directly will make things more clear, as seen below:

Note that the results are  filtered by ITEM_ID and PUBLICATION_ID.

The above SQL Select Query is just a sample. Chances are pretty good that you won’t be executing such queries to read metadata in your Application Code.

The Content Delivery API provides Classes to be used for such Metadata Querying purpose. All such classes can be found in the namespace: Tridion.ContentDelivery.DynamicContent.Query.

 In our case we will use the following classes: CustomMetaKeyCriteria, CustomMetaValueCriteria,  AndCriteria, OrCriteria. All these classes derive from the base Criteria class. The second important class we will be using is the Query class.

Code Sample 1:

The below .NET code queries items having the field: Index  value as True.

// Include the desired Namespace
using Tridion.ContentDelivery.DynamicContent.Query;

String key = "Index";
Criteria customKeyCriteria = new CustomMetaKeyCriteria(key);
Criteria customValueCriteria = new CustomMetaValueCriteria("True");
Query query = new Query(new AndCriteria(customKeyCriteria, customValueCriteria));
String[] itemURIs = query.ExecuteQuery();

Above is one of the simplest query. Recall that the value of  Custom Metadata Key ‘Index‘ can be False as well. The below sample code queries for component having the Custom Metadata Value, corresponding to Key: Index, either  True or False.

Code Sample 2: Using  OrCriteria , meaning search for components with either value ( True or False )

The OrCriteria has two overloaded Constructors:

// Include the desired Namespace
using Tridion.ContentDelivery.DynamicContent.Query; 

String key = "Index";
 String[] keyValues = {"True", "False"};
 Criteria customKeyCriteria = new CustomMetaKeyCriteria(key);
 
 ArrayList criteriaList = new ArrayList();
 foreach (String value in keyValues) 
    {
         criteriaList.Add(new CustomMetaValueCriteria(value));
    }
 Criteria customValueCriteria = new OrCriteria((Criteria[])criteriaList.ToArray(typeof( CustomMetaValueCriteria)));
 String[] itemURIs = query.ExecuteQuery();

Same way we can use AndCriteria class . This class also has two overloaded constructors:

Remarks:

1. Many times we may need to filter/limit the results of the query. In such cases, use LimitFilter class and the SetResultFilter() method of Query class  as:

   Query query = new Query(new OrCriteria(customKeyCriteria, customValueCriteria));
   query.SetResultFilter(new LimitFilter(20));
   String[] itemURIs = query.ExecuteQuery();

As a last note, do not forget to check the list of numerous other useful classes that can be used to  query broker database. Check the API Section here.

Hope you enjoyed this post. Comments and Suggestions are welcome.