Tridion 2011 Broker Queries – Goodbye SearchFilter, Hello Criteria

March 14th, 2012 | Posted by Robert Curlette in Tridion | Tridion Broker

Working with Tridion 2011 is a breath of fresh air – and sometimes from unexpected places. Today I was working with the new Content Delivery API and had a refreshingly light feeling. I was immediately comfortable with the Criteria objects and the new possibilities. It just feels right.

Tridion Broker Query – Get item based on Metadata from all Publications

In Tridion 5.3 I have a query that finds a product based on the SKU in the Metadata for 1 Publication. Now I have a need to get all instances of the Component for all Publications. Using Tridion 5.3 we solved this for 1 Publication with a SearchQuery like the one below, but I could not find a way in Tridion 5.3 to query the Broker and all Publications for 1 Component using a Metadata query. Using Tridion 2011 for getting all published instances, regardless of Publication, is very easy.

Tridion 5.3 Broker Query:

<%@ page import="com.tridion.dcp.*"%>
<%@ page import="com.tridion.dcp.filters.*"%>
<%@ page import="com.tridion.dcp.filters.query.*"%>
<%@ page import="com.tridion.broker.components.meta.*"%>
<%@ page import="com.tridion.util.*"%>
<%@ page import="com.tridion.meta.*"%>
<%
SearchFilter filt = new SearchFilter(publicationURI);
Query q = new Query();
String[] results = null;
strCustomQuery = "(KEY_NAME = '" + key + "' AND KEY_STRING_VALUE = '" + value + "')");
results = filt.match(q.toString(), strCustomQuery, ComponentMetaHome.FIELD_CREATION_DATE + "=asc", 100);

ComponentPresentationFactory cpFactory = new ComponentPresentationFactory(publicationURI);
ComponentMetaFactory componentMetaFactory = new ComponentMetaFactory(publicationURI);
if (results != null) {
	int i = 0;
	for (String result : results) {
		tcmURI = new TCMURI(result);
		ComponentPresentation cp = cpFactory.getComponentPresentationWithLowestPriority(tcmURI.getItemId());
		out.println(cp.getContent());
	}
}
%>

Tridion 2011, All Publications:

<%@ page language="java" contentType="texthtml; charset=UTF-8"%>
<%@page import="com.tridion.broker.StorageException,
com.tridion.broker.querying.*,
com.tridion.broker.querying.criteria.*,
com.tridion.broker.querying.criteria.categorization.*,
com.tridion.broker.querying.criteria.content.*,
com.tridion.broker.querying.criteria.metadata.*,
com.tridion.broker.querying.criteria.operators.*,
com.tridion.broker.querying.criteria.taxonomy.*,
com.tridion.broker.querying.filter.LimitFilter,
com.tridion.broker.querying.sorting.SortParameter"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
     <head>
          <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
          <title></title>
     </head>
<%
String sku = "1EX0.031.03";
String fieldName = "article_number";

//Create query
Query myQuery = new Query();

Criteria myCriteria = null;
CustomMetaKeyCriteria metaField = new CustomMetaKeyCriteria(fieldName);
CustomMetaValueCriteria customMeta = new CustomMetaValueCriteria(sku);

// glue the metadata together
AndCriteria fieldCriteria = new AndCriteria(customMeta, metaField);
myCriteria = fieldCriteria;
myQuery.setCriteria(myCriteria);

// Sort it
SortParameter sortParameter = new SortParameter(SortParameter.ITEMS_TITLE, SortParameter.ASCENDING);
myQuery.addSorting(sortParameter);

// Get results
myQuery.setResultFilter(new LimitFilter(100));

// Display
String[] itemURIs = myQuery.executeQuery();
String strOutput = "";
for (int i = 0; i < itemURIs.length; i++) {
	strOutput += itemURIs[i] + ", ";
}
%>
<body>
	output = <%=strOutput%>
</body>
</html>

Tridion 2011, 1 Publication (Code borrowed from Tridion Live Documentation with slight modifications)

<%@ page language="java" contentType="texthtml; charset=UTF-8"%>
<%@page import="com.tridion.broker.StorageException,
com.tridion.broker.querying.*,
com.tridion.broker.querying.criteria.*,
com.tridion.broker.querying.criteria.categorization.*,
com.tridion.broker.querying.criteria.content.*,
com.tridion.broker.querying.criteria.metadata.*,
com.tridion.broker.querying.criteria.operators.*,
com.tridion.broker.querying.criteria.taxonomy.*,
com.tridion.broker.querying.filter.LimitFilter,
com.tridion.broker.querying.sorting.SortParameter"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
     <head>
          <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
          <title></title>
     </head>
<%
int iPublicationID = 129;
String sku = "1EX0.031.03";
String fieldName = "article_number";

//Create query
Query myQuery = new Query();

Criteria myCriteria = null;
PublicationCriteria pubCriteria = new PublicationCriteria(iPublicationID);
CustomMetaKeyCriteria metaField = new CustomMetaKeyCriteria(fieldName);
CustomMetaValueCriteria customMeta = new CustomMetaValueCriteria(sku);

// glue the metadata together
AndCriteria fieldCriteria = new AndCriteria(customMeta, metaField);
AndCriteria allCriteria = new AndCriteria(fieldCriteria, pubCriteria);
myCriteria = allCriteria;
myQuery.setCriteria(myCriteria);

// Sort it
SortParameter sortParameter = new SortParameter(SortParameter.ITEMS_TITLE, SortParameter.ASCENDING);
myQuery.addSorting(sortParameter);

// Get results
myQuery.setResultFilter(new LimitFilter(100));

// Display
String[] itemURIs = myQuery.executeQuery();
String strOutput = "";
for (int i = 0; i < itemURIs.length; i++) {
	strOutput += itemURIs[i] + ", ";
}%>
<body>
	output = <%=strOutput%>
</body>
</html>

Helpful links:

Summary:

Tridion has opened a can of awesome sauce and spread it liberally over the new Tridion 2011 Content Delivery API. Marvel at the amount of Criteria available and prepare for the new possibilities it provides. Definitely worth a serious look when you upgrade – don’t leave your SearchFilter queries in there because you can – do yourself a favor and migrate that code right now to use the new API.

You can follow any responses to this entry through the RSS 2.0 You can leave a response, or trackback.

2 Responses

Alvin says:

Thursday, 15th March 2012 at 14:20

Awesome sauce. I like it.
Vikas Kumar says:

Monday, 1st December 2014 at 10:59

Hi Robert,

I have been following your blog for sometime.
I am curious about CDE side Broker DB and the commonly used database tables in both CME and CDE. Could you please give some info or any appropriate link so that i can have a look.

Thanks in advance
Vikas