9

I have a schema with a multi-value custom meta field. So when a component is published I'd like to retrieve it by the selected values in this meta field. I need to pull back the exact matches.

For instance:

  1. I have Component A with field values val1, val2 and val3.
  2. I also have Component B with values val1.
  3. And I a Component C with values val3.

I am trying to construct a query to retrieve only Component A, i.e. val1 AND val2 AND val3.

We can't AND (AndCriteria) our CustomMetaValueCriterias, as this will never yield true due to Custom Metas being separate records in the DB. It seems to be the same story as documented here: http://www.tridiondeveloper.com/odata-how-to-query-multiple-custom-metas/2

And of course, using an OrCriteria yields unwanted items, which need to be filtered out on the application requiring delivery-side CPU cycles delaying the HTTP response.

My question is, is there an elegant way of using the CD API (perhaps via the Strategy or QueryResult classes) to achieve this? Or must I fall back to filtering my resultset manually via if/else conditions or using the Event System Hashing method (described here: http://www.tridiondeveloper.com/odata-how-to-query-multiple-custom-metas/4)?

  • 1
    I think ANDs would work if using a CustomMetaValueCriteria for each match such as: CustomMetaValueCriteria value = new CustomMetaValueCriteria(new CustomMetaKeyCriteria("city"), "San Diego");. Finding A would be easy because it's more specific than B or C. The harder part is finding something that matches just val1 or just val3, right? – Alvin Reyes Oct 2 '13 at 15:18
  • 1
    Unfortunately doing something like this doesn't work: CustomMetaValueCriteria value1 = new CustomMetaValueCriteria(new CustomMetaKeyCriteria("city"), "San Diego"); CustomMetaValueCriteria value2 = new CustomMetaValueCriteria(new CustomMetaKeyCriteria("city"), "Halifax"); AndCriteria andCriteria = new AndCriteria(value1, value2); This yields 0 results because it's trying to do a join on different values for the same row. If an OrCriteria is used instead, then you get all 3 components, A, B and C. – Nickoli Roussakov Oct 2 '13 at 15:41
  • In the old CD API, we had a "group by" hack for custom SQL queries to get exact matches. Anyone know how that might translate into Custom Meta Criteria queries? – Alvin Reyes Oct 2 '13 at 16:06
  • It seems that you have done all the proper research yourself, Nick. The logical next step would be to report this to SDL as a bug. Because surely, this cannot be by design. – Quirijn Oct 3 '13 at 8:54
  • Well, they're not really the same row, at least not in the database. In 5.3 they were comma separated values. Either in 2009 or 2011, each key/value pair was its own row. The "AND'd" CustomMetaValueCriteria() calls didn't work? Wait, I guess I'll check myself. :-) – Alvin Reyes Oct 4 '13 at 0:19
7

I wasn't able to come up with a solution using the Content Delivery API. However, I did find a solution that works quite well for the client. Essentially we will form a CustomMetaValueCriteria for all of the searched offer codes, and then OR all of them. However, as described above this will give us some false positives (components that contain ANY of the offer codes and not ALL of the offer codes).

ArrayList<Criteria> metaCriteria = new ArrayList<Criteria>();
for (String offerCode: searchedOfferCodes) {   
    CustomMetaValueCriteria matchMeta = new CustomMetaValueCriteria(new CustomMetaKeyCriteria(FIELD_NAME), offerCode);
    metaCriteria.add(matchMeta);
}
// Convert the ArrayList, OR the metaCriteria list and add it to the master list of criteria which includes
// ItemTypeCriteria/PublicationCriteria to be AND'd and set to the query

We will have an ArrayList of the offer codes we are searching for in the components metadata (searchedOfferCodes below). Inside of the results loop, we construct the component meta and form a String array of offer codes in the current iterations components metadata. We can then use containsAll() method from the Java List class to ensure that all of the searched offer codes are contained in the current components metadata.

ArrayList<String> searchedOfferCodes = new ArrayList<String>();
// Query object is created and executed etc..
String[] itemURIs = query.executeQuery();
for (int i = 0; i < itemURIs.length; {
    TCMURI tcmUri = new TCMURI(itemURIs[i]);
    ComponentMeta compMeta = metaFactory.getMeta(tcmUri.getItemId() );
    CustomMeta customMeta = compMeta.getCustomMeta();
    String metaValues = customMeta.getNameValues().get(FIELD_NAME).getMultipleValues().toString();
    String[] offerCodeResults = null;
    if(StringUtils.isNotEmpty(metaValues)) {
        metaValues = metaValues.replace("[", "");
        metaValues = metaValues.replace("]", "");
        metaValues = metaValues.replace(" ", "");
        offerCodeResults = metaValues.split(",");
    }
    if(StringUtils.isNotEmpty(offerCodeResults) && Arrays.asList(offerCodeResults).containsAll(searchedOfferCodes)) {
        // If we reach this far, we have found a result.
    }
 }

Basically, execute the initial query OR'ing all of the CustomMetaValueCriteria and AND the result with the rest of the criteria. Finally filter the list down even further to get our expected results by comparing the searched offer codes all of the returned components offer codes..

  • 1
    Good solution @josh. Small thing to mention: 'getValue()' is a deprecated method. You should use getNameValues() instead. – Nickoli Roussakov Oct 6 '13 at 1:20
  • Thanks for the heads up Nick, I have updated my answer to reflect. – Josh Hebb Oct 6 '13 at 22:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.