Retrieve Classified Items for Multiple Keywords

Using the standard Content Delivery API, it is possible to retrieve related content (i.e. items that are classified against Keywords) only for one Keyword at a time. If you find yourself with a requirement to read classified items for all Keywords in a Taxonomy, then you'll quickly realize that approach is a performance killer.

To better express what I'm looking for is the following:

Keyword A -- classified with Component 1, Component 2
    Keyword B -- classified with Component 3, Page 1
    Keyword C -- classified with Component 1, Page 2

I want to read the classified items for all Keywords in a Taxonomy and still maintain the 'classified' relationship between each Keyword and its corresponding list of Components & Pages.

The way I did it was to write my own Spring/Hibernate query and piggy-back on the existing DAO objects and methods available in the CD Storage API.

The key is the bean com.tridion.storage.RelatedKeyword, which basically maps the records in DB table ITEM_CATEGORIES_AND_KEYWORDS. This table contains all information needed in the following columns:

• publicationId
• itemId
• taxonomyId
• keywordId

This provides the mapping from TaxonomyId, KeywordId to PublicationId, ItemId. The only unknown is the ItemType, which is available however in table ITEMS. So it's enough to make a join on PublicationId and ItemId and retrieve items by type. We are only interested in Components and Pages, so we'll make 2 queries -- one for each type.

The Hibernate query looks like this:

select distinct(rk) from RelatedKeyword rk, ItemMeta im

    where rk.publicationId = :publicationId and rk.taxonomyId = :taxonomyId and

    im.itemType = :itemType and rk.itemId = im.itemId and rk.publicationId = im.publicationId

We execute the Hibernate query in the following way:

public List<RelatedKeyword> getRelatedItems(String taxonomyURI, int itemType) {

    TCMURI taxonomyTcmUri = new TCMURI(taxonomyURI);

    int publicationId = taxonomyTcmUri.getPublicationId();

    Map<String, Object> queryParams = new HashMap<>();

    queryParams.put("publicationId", publicationId);

    queryParams.put("taxonomyId", taxonomyTcmUri.getItemId());

    queryParams.put("itemType", itemType);

    JPABaseDAO itemDAO = (JPABaseDAO) StorageManagerFactory.getDAO(
        publicationId, StorageTypeMapping.ITEM_META);

    return itemDAO.executeQueryListResult(THE_QUERY, queryParams);

}

From the calling code, we need to call getRelatedItems twice -- once for Components and once for Pages:

List<RelatedKeyword> components = getRelatedItems(taxonomyURI, ItemTypes.COMPONENT);

List<RelatedKeyword> pages = getRelatedItems(taxonomyURI, ItemTypes.PAGE);

Finally, what I want to do is to merge the components and pages lists into one single Map where the Keyword TCMURI is a key and the value is a Set of TCMURIs of the items directly classified against the said Keyword. The following method accomplishes just that:

private void mergeRelatedItems(List<RelatedKeyword> relatedKeywords,
        Map<String, Set<TCMURI>> result, int itemType) {

    for (RelatedKeyword keyword : relatedKeywords) {

        int publicationId = keyword.getPublicationId();

        String key = String.format("tcm:%d-%d-1024", publicationId, keyword.getKeywordId());

        Set<TCMURI> itemList = result.get(key);

        if (itemList == null) {

            itemList = new TreeSet<>();

            result.put(key, itemList);

        }

        TCMURI itemURI = new TCMURI(publicationId, keyword.getItemId(), itemType, 0);

        itemList.add(itemURI);

    }

}

We call the method with the following code:

Map<String, Set<TCMURI>> result = new HashMap<>();

mergeRelatedItems(components, result, ItemTypes.COMPONENT);

mergeRelatedItems(pages, result, ItemTypes.PAGE);