OData – How to Query Multiple Custom Metas

Part 1 – Examining the Problem

Let’s examine the problem in a little more detail:

We have a schema with 2 custom meta fields:
1) color
2) shape

We then have 4 components using this schema:
Component A: color=blue; shape=round
Component B: color=blue; shape=square
Component C: color=red; shape=round
Component D: color=red; shape=square

When publishing these as Dynamic Component Presentations, we get 8 CustomMetas entries in the OData feed:

KeyName=color; StringValue=blue (for Component A)
KeyName=color; StringValue=blue (for Component B)
KeyName=color; StringValue=red (for Component C)
KeyName=color; StringValue=red (for Component D)
KeyName=shape; StringValue=round (for Component A)
KeyName=shape; StringValue=round (for Component C)
KeyName=shape; StringValue=square (for Component B)
KeyName=shape; StringValue=square (for Component D)

Here is the feed so you can examine it more closely:

Now let’s say we want to return the set of components that are Blue and Round. In our example above, this should just be entries related to Component A. You might try a query like this:

/odata.svc/CustomMetas?$filter=(KeyName eq ‘color’ and StringValue eq ‘blue’) OR (KeyName eq ‘shape’ and StringValue eq ’round’)&$expand=Component/ComponentPresentations

So that seems to work when examined with the naked eye:

KeyName=color; StringValue=blue
KeyName=color; StringValue=blue
KeyName=shape; StringValue=round
KeyName=shape; StringValue=round

but when we look at the resultset under the microscope we see that we have CustomMeta entries linked to unwanted components:

Component A: color=blue; shape=round → good
Component B: color=blue; shape=square → wrong
Component C: color=red; shape=round → wrong

The above is actually a redundant query that will return more data than desired. You should simply do:

/odata.svc/CustomMetas?$filter=(KeyName eq 'color' and StringValue eq 'blue')&$expand=Component/ComponentPresentations

OR separately do this:

/odata.svc/CustomMetas?$filter=(KeyName eq 'shape' and StringValue eq 'round')&$expand=Component/ComponentPresentations

We might naively try to switch the OR to an AND like this:

/odata.svc/CustomMetas?$filter=(KeyName eq 'color' and StringValue eq 'blue') AND (KeyName eq 'shape' and StringValue eq 'round')&$expand=Component/ComponentPresentations

but that returns an empty set. Why do you ask? Because there is no single entry that is both ‘blue’ AND ‘red‘ at the same time.

So we conclude that querying on multiple custom metas is not formally supported. This is due in part to a limitation of the OData protocol and in part with the Content Delivery data model.

5 thoughts on “OData – How to Query Multiple Custom Metas

  1. Nice approach, Nick! I personally would have just executed multiple queries and then intersected the results in app-logic. But that would be lots more bytes over the wire.

    As you know, I recently experimented with OData Query Extension (http://yatb.mitza.net/2013/06/url-syntax-for-odata-query-extension.html) and your query could be re-written as:

    /odata.svc/QueryComponentPresentations(SchemaTitle=Article)?$filter=ComponentMetaValue(ComponentMetaKey(‘color’), ‘red’) and ComponentMetaValue(ComponentMetaKey(‘shape’), ’round’)

    … which would be parsed into a Criteria object and performed by CD API on the OData server.

  2. Thanks for the comment Mihai. I really like your approach. I do hope that future releases of the CD Web Service will have this capability built-in.

    As a side note, I recall having a conversation with someone from R&D, and he mentioned that OData on the back side does not wrap the standard CD API as I had assumed. It uses a totally redesigned set of DB queries which are much more performant than those of the classic CD API.

  3. Hi Nickoli,
    Is this issue solved in Web 8 or 8.5 with Content Service microservice?

  4. That’s a great question. The new version of Tridion supports GraphQL, which is supposed to be a lot more flexible for querying. I’ve not done a POC on this specifically yet. Please share your findings with us if you get there first.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>