Any good sources for Tridion Taxonomy query examples

Question

I have a scenario where I have several thousand Component Presentations published that are classified with a specific category/keyword, but the components also have Custom Metadata fields pertaining to define the top n. that will be displayed. Essentially, I need to filter a set of components based on keywords, sort them based on custom metadata and only select the top N.

I understand how to list the items and create basic filters (from the list of filter items in the API) but I need to filter on a metadata field (e.g. the three items with the latest metadata "priorityDate").

From what I'm reading I'd be expected to grab all records from the taxonomy search (using TaxonomyKeywordCriteria) and then manually (SQL executed against QueryRunner) retrieve all the components' metadata to filter/sort.

I must have missed something surely (it doesn't feel right have 1/2 API call and then in page SQL script)... and if not would the QueryRunner query be cached alongside the results of the Taxonomy Query.executeQuery()

Example code:

ItemTypeCriteria isComponent = new ItemTypeCriteria(16);
TaxonomyKeywordCriteria taxonomyKeywordCriteria = new TaxonomyKeywordCriteria(taxURI, taxKeywordURI, true);
Criteria[] allCriteria = {isComponent, isTDIPublication, isArticle, taxonomyKeywordCriteria}; 
AndCriteria andCriteria = CriteriaFactory.And(allCriteria);
Query query = new Query();         
query.setCriteria(andCriteria);

Answer 1

Using SQL inside your JSP doesn't seem appropriate, since it's data layer logic and should not be present on the presentation. Also, if Tridion update the DB structure with a new release, then there is a risk your custom SQL could stop working. You're better off using the Broker API and not rely on custom SQL statements into the Broker DB.

I think your best bet is to try and minimize the number of database hits required. The API allows filtering by keywords (you can filter by custom meta as well, but that won't help you, since you mentioned you want to sort by a given custom meta field). You have two options to get a list of CPs by keywords which you can then sort:

1. Query.execureQuery(). This returns a list of tcm URIs, meaning that a separate DB hit has to be made to retrieve the CustomMetadata objects and the CPs (ComponentPresentationFactory.GetComponentPresetation(tcmUri)). This could (and probably will) get heavy until everything is sufficiently cached.
2. ComponentPresentationFactory.getTaxonomyComponentPresentations(). You can pass an array of keywords to this method and get back a larger set of CPs. This means one quick DB query. On the CM side, in the component template add rendering of the Custom Meta field value as an HTML comment. Then back on the delivery side simply parse out that value from each CP of the returned set and do your sorting on the application server (rather than the DB). This will be faster than doing many DB queries.

Your question topic asks for some examples of Taxonomy query examples. Have a look at the sdllivecontent portal at the 2011 documentation. The API is pretty much the same as for 2009 (there are only subtle differences, however, there are some examples on constructing filters provided there: http://sdllivecontent.sdl.com/LiveContent/web/pub.xql?action=home&pub=SDL_Tridion_2011&lang=en-US#addHistory=true&filename=DevelopingAFilter.xml&docid=concept_0AB6D192D7AB4EC18892631F519EF1DD&inner_id=&tid=&query=&scope=&resource=&eventType=lcContent.loadDocconcept_0AB6D192D7AB4EC18892631F519EF1DD

The sdltridionworld forum is another good place if you browse through the topics. Here are a couple of threads with some example code:

1. https://forum.sdltridion.com/topic.asp?TOPIC_ID=6690&SearchTerms=taxonomy,query
2. https://forum.sdltridion.com/topic.asp?TOPIC_ID=5619&SearchTerms=taxonomy,query