RedBlog

A bit of background: Liferay is a portal for JSR-168 portlets with some added services. Unfortunately those additional services and APIs are not documented at all - the official JavaDoc is little more than a class browser.

One of the more unobvious APIs is the DynamicQuery API to do more elaborate SQL queries against the database. Liferays database layer is a wrapper on top of Hibernate. A lot of the APIs are wrappers uppon wrappers on other APIs, usually hiding the bits you need. Fortunately you can actually get most of the Hibernate functionality through the Liferay API.

The following is my attempt to document the usage of projections, which is what Hibernate calls SQL aggregate functions.

To build the query is relatively simple, you just need to create a ProjectionList, add a few columns to it and then add it to the query just like you add constraints (just with another method). This I could sort of figure out by the method names and types only.

import com.liferay.portlet.tags.model.TagsEntry;
import com.liferay.portal.kernel.dao.orm.DynamicQueryFactoryUtil;
import com.liferay.portal.kernel.dao.orm.ProjectionFactoryUtil;
import com.liferay.portal.kernel.dao.orm.DynamicQuery;

DynamicQuery query = DynamicQueryFactoryUtil.forClass(TagsEntry.class)
    .setProjection(
	ProjectionFactoryUtil.projectionList()
	    .add(ProjectionFactoryUtil.groupProperty("userName"))
	    .add(ProjectionFactoryUtil.count("entryId")));

Here, we group by the column "userName" and count all "entryId" of the TagsEntry table. As you can see, count does actually take a column name argument. I'm not sure why; it may be just a strange documentation feature, or to match the API of countDistinct...

The really hard part to figure out without proper API documentation was the type of the returned data however. There was really only one way - handle it as Object and do data.getClass().toString() and print that (and get a hint from the Hibernate documentation). So that's what I did, and here's the result: When you run TagsEntryLocalServiceUtil.dynamicQuery on a query that doesn't have a projection, it returns a list of TagsEntry. When run on a query with a projection however, it returns List<Object[]>, where Object is really of each column type:

for (Object rowObj : TagsEntryServiceUtil.dynamicQuery(query)) {
       Object row[] = (Object[]) rowObj;
       String userName = (String) row[0];
       Integer count = (Integer) row[1];

};

So, to sum it up, complicated queries aren't that hard, if there'd only been proper API documentation :)