Part 3 – The Multi-Meta Query Solution

We’ve actually implemented the solution described below for a client, and with it were able to do geo-location targeting, PRIZM segment targeting, time of day and season targeting without having to purchase additional products.

So let’s look at what we learned from the previous parts of this article:

1. As stated in Part 1, we have a limitation of being able to query only one CustomMetas entry at a time.

2. OData has query string functions like startswith, endswith and substringof.

With the help of point 2 above we can make partial matches on any one field.  So if CustomMeta fields are hashed into one field to result in one CustomMetas entry, then we can query multiple parameters at the same time.

Here is the procedure:

• We add a Metadata text field to all the Component schemas called ‘TargetParamIndex’.  

• We provide a clear description to this field: “Automatically Updated and Maintained by System”.  This way content editors will know not to bother with it because the system will always overwrite their changes in this field.

• We implement an Component Save in Initiated Phase event which takes all the Component Metadata fields and creates a hash string out of them, e.g. {schema=article;color=blue;shape=round;}.  So there is no chance that a content editor can screw it up because no matter what they type into this field, as soon as they save the component, our event will always overwrite it with the latest and greatest hash.

• When you dynamically publish your component, you’ll query it with the help of the substringof function.

The OData query now looks like this:

/odata.svc/CustomMetas?$filter=KeyName eq 'TargetParamIndex' and substringof('Schema{article}',StringValue) and substringof('color=blue',StringValue) and substringof('shape=round')&$expand=Component/ComponentPresentations

The resultset we’re going to get is going to be quite close.  There is one caveat, we will get redundant results for each Component Template.  We can’t control this at the Component level with the event system.  Schema is the best we can do here.  However, there is typically a small handful of dynamic CTs that render the same schema in the same publication – so to me, this is OK.

The other caveat to consider is how the substringof function is actually implemented in the blackbox part of Tridion.  Does it translate to the SQL LIKE operator, and if it does, what is the impact on SQL query performance in regards to the Index? (the use of indexes becomes limited with the LIKE operator.  See here for reference: http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning)

On the other hand, a powerful DB like ORACLE or SQL SERVER is designed to deal with millions of records, so a few hundred thousand CustomMetas should be a walk in the park – even without the full use the index.  This is especially prominent because OData caches the queries (and hopefully the results).  So the smaller resultset returned via the HTTP Response with a slighly slower DB query, which is cached on subsequent runs, is better than transmitting unnecessary entries over HTTP only to discard them on the application side.

In my real life scenario I had created an embeddable schema called “Targeting”.  This schema contained the following fields in addition to the “TargetParamsIndex” field described above:

The Save event looks for this embedded schema to be present and creates a hash from all the fields.  Here is the code:

using System;
using System.Collections.Generic;
using System.Text;
using Tridion.ContentManager;
using Tridion.ContentManager.Extensibility;
using Tridion.ContentManager.ContentManagement;
using Tridion.ContentManager.CommunicationManagement;
using Tridion.ContentManager.Extensibility.Events;
using Tridion.Logging;
using Tridion.ContentManager.Publishing;
using System.Linq;
using Tridion.ContentManager.ContentManagement.Fields;
using System.Text.RegularExpressions;
using ContentBloom.Tridion.Common;
using System.Xml;

namespace ContentBloom.Tridion.Events
{
	[TcmExtension("Content Creation Events")]
	public class ContentCreationEvents : TcmExtension
	{
        public ContentCreationEvents()
		{
			EventSystem.Subscribe<Component, SaveEventArgs>(SetTargetingParameters, EventPhases.Initiated);
		}


        public void SetTargetingParameters(Component component, SaveEventArgs args, EventPhases phases)
        {
            EmbeddedSchemaField targetingFields = component.GetTargetingMetadataField();

            if (targetingFields != null)
            {
                string targetParamString = CreateTargetParamStringFromMetadata(targetingFields); 

                targetParamString = string.Format("Schema{{{0}}};{1}", component.Schema.Title, targetParamString);

                //"TargetParamIndex";
                component.Metadata = SetTargetParamsIndexField(component.Metadata, targetingFields, targetParamString);
            }
        }

        private XmlElement SetTargetParamsIndexField(XmlElement metadataXml, EmbeddedSchemaField embeddedField, string targetParamString)
        {
            XmlNamespaceManager nsMgr = Utility.GetTridionXmlNamespaceManager();
            nsMgr.AddNamespace("prefix", metadataXml.NamespaceURI);
            XmlNode node = metadataXml.SelectSingleNode("//prefix:"+Constants.METAFIELD_TARGET_PARAM_INDEX, nsMgr);
            if (node != null)
                node.InnerText = targetParamString;
            else
            {
                EmbeddedSchemaFieldDefinition embeddedFieldDefinition = (EmbeddedSchemaFieldDefinition)embeddedField.Definition;
                XmlNode parentNode = metadataXml.SelectSingleNode("//prefix:" + embeddedFieldDefinition.Name, nsMgr);
                if (parentNode == null)
                {
                    parentNode = metadataXml.OwnerDocument.CreateElement(embeddedFieldDefinition.Name, metadataXml.NamespaceURI);
                    metadataXml.AppendChild(parentNode);
                }
                XmlElement targetParamIndexNode = parentNode.OwnerDocument.CreateElement(Constants.METAFIELD_TARGET_PARAM_INDEX, metadataXml.NamespaceURI);
                targetParamIndexNode.InnerText = targetParamString;
                parentNode.AppendChild(targetParamIndexNode);
            }

            return metadataXml;
        }

        private string CreateTargetParamStringFromMetadata(EmbeddedSchemaField targetingEmbeddableField)
        {
            StringBuilder metaParams = new StringBuilder();
            if (targetingEmbeddableField != null)
            {
                ItemFields targetingFields = targetingEmbeddableField.Value;

                metaParams.Append("MarketSegments{");
                if (targetingFields != null && targetingFields.Contains("MarketSegments"))
                {
                    KeywordField marketSegmentationField = (KeywordField)targetingFields["MarketSegments"];
                    IList<Keyword> targetedMarketSegments = marketSegmentationField.Values;

                    int i = 0;
                    foreach (Keyword targetedMarketSegment in targetedMarketSegments)  
                    {
                        metaParams.Append(Constants.DELIM_MARKET_SEGMENT).Append(targetedMarketSegment.Title);
                        i++;
                    }
                }
                metaParams.Append("};");

                metaParams.Append("Location{");
                if (targetingFields != null && targetingFields.Contains("Location"))
                {

                    KeywordField locationField = (KeywordField)targetingFields["Location"];
                    IList<Keyword> locations = locationField.Values;
                    int i = 0;
                    foreach (Keyword location in locations)
                    {
                        metaParams.Append(Constants.DELIM_LOCATION).Append(location.Title);
                        i++;
                    }
                }
                metaParams.Append("};");

                metaParams.Append("TimeOfDay{");
                if (targetingFields != null && targetingFields.Contains("TimeOfDay"))
                {
                    KeywordField todField = (KeywordField)targetingFields["TimeOfDay"];
                    IList<Keyword> todDefinitions = todField.Values;
                    int i = 0;
                    foreach (Keyword tod in todDefinitions)
                    {
                        metaParams.Append(Constants.DELIM_TIME_OF_DAY).Append(tod.Title);
                        i++;
                    }
                }
                metaParams.Append("};");

                metaParams.Append("DateRange{");
                if (targetingFields != null && targetingFields.Contains("DateRange"))
                {
                    DateTime startDate = DateTime.MinValue,
                                endDate = DateTime.MinValue;

                    EmbeddedSchemaField dateRangeEmbeddedField = (EmbeddedSchemaField)targetingFields["DateRange"];
                    IList<ItemFields> dateRangeFieldsList = dateRangeEmbeddedField.Values;
                    if (dateRangeFieldsList != null)
                    {
                        foreach (ItemFields dateRangeFields in dateRangeFieldsList)
                        {
                            if (dateRangeFields != null)
                            {
                                if (dateRangeFields.Contains("StartDate"))
                                {
                                    DateField dateStartField = dateRangeFields["StartDate"] as DateField;
                                    if (dateStartField != null)
                                    {
                                        startDate = dateStartField.Value;
                                    }
                                }
                                if (dateRangeFields.Contains("EndDate"))
                                {
                                    DateField dateEndField = dateRangeFields["EndDate"] as DateField;
                                    if (dateEndField != null)
                                    {
                                        endDate = dateEndField.Value;
                                    }
                                }

                                int i = 0;
                                //print each date from the start until the end..
                                foreach (DateTime day in Utility.EachDay(startDate, endDate))
                                {
                                    metaParams.Append(day.ToString("yyMMdd") + ",");
                                    i++;
                                }
                            }
                        }
                    }
                }
                metaParams.Append("};");
            }
            return metaParams.ToString();
        }
    }
}

There are also a couple of extension methods used:

<br />
        public static EmbeddedSchemaField GetTargetingMetadataField(this Component comp)<br />
        {<br />
            EmbeddedSchemaField targetingFields = null;<br />
            ItemFields meta = comp.GetMetadataFields();<br />
            if (meta != null)<br />
            {<br />
                targetingFields = GetTargetingMetadataField(meta);<br />
            }<br />
            return targetingFields;<br />
        }</p>
<p>        public static EmbeddedSchemaField GetTargetingMetadataField(this ItemFields metaFields)<br />
        {<br />
            EmbeddedSchemaField targetingFields = null;<br />
            foreach (ItemField metaField in metaFields)<br />
            {<br />
                // Unfortunately c# switch cases do not allow cases based on object types!<br />
                if (metaField.Definition.ToString() == "Tridion.ContentManager.ContentManagement.Fields.EmbeddedSchemaFieldDefinition")<br />
                {<br />
                    EmbeddedSchemaFieldDefinition embeddedFieldDefinition = (EmbeddedSchemaFieldDefinition)metaField.Definition;<br />
                    if (embeddedFieldDefinition.EmbeddedSchema.Title == Constants.SCHEMA_TITLE_TARGETING)<br />
                    {<br />
                        EmbeddedSchemaField embeddableFields = (EmbeddedSchemaField)metaField;<br />
                        targetingFields = embeddableFields;<br />
                        break;<br />
                    }<br />
                }<br />
            }<br />
            return targetingFields;<br />
        }<br />