Introduction

I was recently working on a Content Delivery syndication piece for a client so that they could pull various components of varying schema’s based on a variety of metadata values. The client requested that they be able to pass in a list of metadata keys, with each key having an accompanying list of meta-values to be searched on.

We quickly realized there were two scenarios which we would be encountering

1. The first being that we would like to search for components containing the key and ALL of the values in the accompanying metadata values list,
2. The second was the scenario where we search for components containing the key and ANY of the values in the accompanying metadata values list.

I will discuss both scenarios Content Delivery querying logic, implementation and some problems we encountered.

Scenario 1 – Multiple Metadata Values per Unique Key – OR Logic

In this scenario, we are trying to find components that contain the key and any subset of the metadata values. The logic we followed was to add all key-value pairs to a special list of Criteria; this special list of Criteria is meant to be OR’d when it is finally assembled so that we get components with the searched key and any subset of the values in the list. The result of this OrCriteria (containing the results of ORing all of the CustomMetaKeys and CustomMetaValues) is added to the master list of Criteria including other Criteria’s such as PublicationCriteria, SchemaCriteria, etc.

HashMap searchedPairs; // Search criteria
ArrayList criteriasArrayList = new ArrayList();	// Master Criteria-list
// Add the meta-keys and values as criteria
for(Map.Entry keyValuePair : searchedPairs.entrySet()) {
	String metakey = keyValuePair.getKey();
	ArrayList valueList = keyValuePair.getValue();
	        	
	ArrayList orCriteriaList = new ArrayList();
	for(String metavalue : valueList) {
		orCriteriaList.add(new CustomMetaValueCriteria(new CustomMetaKeyCriteria(metakey),metavalue));
	}
	Criteria[] orMetaCriteria = orCriteriaList.toArray(new Criteria[orCriteriaList.size()]);
	OrCriteria orCriteria = new OrCriteria(orMetaCriteria);
	criteriasArrayList.add(orCriteria);
}

This worked out of the box, and returned only results containing all of the values in the list per unique key. So far, so good.

Scenario 2 – Multiple Metadata Values per Unique Key – AND Logic

In this scenario on the other hand, we ran into several problems. The problem we ran into was subtle and was based around the way custom metadata keys/values are stored in the Broker. Initially, we implemented code similar to the snippet above in the logic to OR the values changing the OR to an AND, however we quickly realized we were not getting the desired results in the initial tests, and couldn’t figure out why.

We then enabled hibernate logging (org.hibernate.SQL) so that we could see the raw SQL queries to delve a bit deeper into the actual query logic being executed through the Content Delivery API. Please see here for more information about enabling the CD Broker SQL logs. It was in those logs that we discovered the problem. Looking at the tail of the SQL query, we noticed that we are AND’ing the key ‘OfferCodesLookup’ with both values ‘456’ and ‘123’.

and custommeta4_.KEY_NAME='OfferCodesLookup' 
and custommeta4_.KEY_STRING_VALUE='456' 
and itemmeta0_.PUBLICATION_ID=custommeta4_.PUBLICATION_ID 
and itemmeta0_.ITEM_REFERENCE_ID=custommeta4_.ITEM_ID 
and itemmeta0_.ITEM_TYPE=custommeta4_.ITEM_TYPE 
and custommeta4_.KEY_NAME='OfferCodesLookup' 
and custommeta4_.KEY_STRING_VALUE='123'
order by itemmeta0_.ITEM_REFERENCE_ID ASC

From looking at the table below, it is obvious that this can never be true. We cannot have the same KEY_NAME (OfferCodesLookup) be equal to multiple KEY_STRING_VALUE’s (456,123) on an individual row.

Solutions

We kept coming back to try to solve this problem through the out of the box Content Delivery API. We were confident that we could find some way to implement the required logic, however we were unable to find a way to AND unique keys per multiple values. Well, that’s no good.

The solution I came up with was to use the same querying logic as before, essentially OR’ing the criteria so that we get a list of components that contain any of the searched values per key. This would obviously return us some false positives as we are looking for components that contain ALL of the searched values, yet the results we are looking for will always be a subset of these results. Once the query is executed and we have our list of results in TCM URI form, we can obtain the CustomMeta per individual component. With this, we are able to get the metadata key with its list of values and verify whether or not it contains the entire set of searched values instead of a subset.

While this is not optimal, it is a perfectly valid solution and worked as expected. The performance hit is also very slight; narrowing down the list of results is quite efficient, as the only additional operations required are to grab the CustomMeta from the Broker for each result and verify that the searched values (per key) are  all contained in this list (using the default Java List interfaces containsAll(Collection c) which returns a Boolean indicating the presence or lack thereof of the values). 

Advice & Conclusions

• If you are run into a scenario where you think you will require some custom filtering of the returned results from the query, extensively ensure that you cannot reach the desired results using the out of the box Content Delivery API. If you are confident that you cannot, then possibly reach out for help on Stack Exchange; the bottom line, custom filtering will require more trips to the Broker and should only be done in rare circumstances after exhausting all options in the API.
• Are the results returned via executing the query not as expected? Enable the hibernate SQL logging and parse through the query. More often than not this will give insight as to where your query logic is incorrect, and also a better understanding of how information is stored in the Broker.

Links

• How to query for Components with Multi-value custom metas

• How to enable Content Delivery API/Broker DB logging to show raw SQL

I would love to hear about any other possible solutions to the problem; perhaps someone can even achieve the AND’ing of the custom meta values per unique key with the CD Criteria without resorting to manual filtering using CustomMeta.