Labels

Thursday, August 2, 2012

dynamic queries liferay group by and count


Here's a example for a SQL query with GROUP BY and COUNT function.
This is how it works with the liferay hibernate api.

SQL syntax: 
SELECT classPK, COUNT(*) AS _count
FROM MBMessage
ORDER BY _count DESC

Dynamic Query:
ProjectionList plist = ProjectionFactoryUtil.projectionList();
plist.add(ProjectionFactoryUtil.groupProperty("classPK"));
plist.add(ProjectionFactoryUtil.rowCount(), "_count");

DynamicQuery querySub = DynamicQueryFactoryUtil.forClass(MBMessage.class, PortalClassLoaderUtil.getClassLoader())
 .setProjection(plist)
 .addOrder(OrderFactoryUtil.desc("_count"));

DynamicQuery query = DynamicQueryFactoryUtil.forClass(AssetEntry.class, PortalClassLoaderUtil.getClassLoader())
 .add(PropertyFactoryUtil.forName("classPK").in(querySub));

List results = AssetEntryLocalServiceUtil.dynamicQuery(querySub);
LOG.info(results);
for (Object[] r : (List) results) {

 LOG.info(r[0]+"//"+r[1]);
}

Posted by Jonas Gehring at 10:48 AM
Labels: ,

2 comments:

  1. Thanks for sharing these information. I find it difficult to understand how application development melbourne works. With your post, I was able to get it a bit.

    ReplyDelete

  2. Looking at all these confusing codes and numbers, one would be thankful that there are people who are into such complex process for the public to enjoy flawless mobile phone applications.

    Theresa King

    ReplyDelete

Subscribe to: Post Comments (Atom)