Friday, September 18, 2015

ATG QueryBuilder: How to create nested queries / subqueries

I had to deal a good amount of time finding a way to query repositoy items that would match some relationships among different item descriptors. I was trying to look for a way to perform a join over different item descriptors, but at the end was able to solved it in a nested queries fashion. You know,

the typical thing in SQL where you filter by matching a column from the result of another query:

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

This in ATG can be done using a query builder operation called: createIncludesItemQuery. You can pass as parameter another query and the colum matching expression.


// Get guide items where tabs in the guide matches some other tabs query
RepositoryItemDescriptor guideRepositoryItemDescriptor = 
                        getProductRepository().getItemDescriptor("guide");
RepositoryView guidesRepositoryView = guideRepositoryItemDescriptor.getRepositoryView();               

QueryBuilder guidesQueryBuilder = guidesRepositoryView.getQueryBuilder();
QueryExpression tabsPropertyExpression =     
        guidesQueryBuilder.createPropertyQueryExpression("tabs");                    
// productTabsQuery is another query built
Query productGuidesQuery = 
      guidesQueryBuilder.createIncludesItemQuery(tabsPropertyExpression, productTabsQuery);
RepositoryItem[] guides = guidesRepositoryView.executeQuery(productGuidesQuery);