Liferay 6: querying webcontent dynamically

Liferay 6

Problem: we need to obtain webcontent items by custom query with conditions, one of them being webcontent item type for instance.

The JournalArticleLocalServiceUtil.dynamicQuery(DynamicQuery query) method seems to be just what we’re looking for.

Let’s make a custom query to get 100 latest webcontent items of types “blogs” and “news”. We also add condition on groupId, because we want only items from certain group:

DynamicQuery query = DynamicQueryFactoryUtil.forClass(JournalArticle.class, PortalClassLoaderUtil.getClassLoader())
.add(PropertyFactoryUtil.forName("groupId").eq(groupId)
.add(PropertyFactoryUtil.forName("type").in(new String[] {"blogs", "news"}))
.addOrder(OrderFactoryUtil.desc("createDate"));
query.setLimit(0, 100);

List <JournalArticles> = JournalArticleLocalServiceUtil.dynamicQuery(query);


It almost worked. Almost.

We get all versions of webcontent items. What does it mean? When you create webcontent item, it gets a version number 1.0. When you edit it for the first time and save, it gets version 1.1 and so on. This query gets versions 1.0 and 1.1 as separate records.
You cannot just filter out lower versions in your code – you’ll get less than 100 records returned! (And it’s also inefficient of-course).

So, what can we do?

We could resort to native SQL. The query would be something like this:

select * from journalarticle articleParent
where groupId = ${groupId}
and type in ('blogs', 'news')
and id_ = (select max(id_) from journalarticle where articleId = articleParent.articleId)
order by createdDate desc
limit 0, 100


But can we somehow avoid native SQL calls? Especially considering the fact that we need certain extra effort to be able to make native SQL calls in Liferay portlets.

It turns out that yes, we can. Thanks to http://issues.liferay.com/browse/LEP-6600

Dynamic query is capable of handling correlated subqueries!
So, corresponding code will look like this:

DynamicQuery subQuery = DynamicQueryFactoryUtil.forClass(JournalArticle.class, "articleSub", PortalClassLoaderUtil.getClassLoader())
.add(PropertyFactoryUtil.forName("articleId").eqProperty("articleParent.articleId"))
.setProjection(ProjectionFactoryUtil.max("id"));

DynamicQuery query = DynamicQueryFactoryUtil.forClass(JournalArticle.class, "articleParent", PortalClassLoaderUtil.getClassLoader())
.add(PropertyFactoryUtil.forName("id").eq(subQuery))
.add(PropertyFactoryUtil.forName("groupId").eq(globalGroup.getGroupId()))
.add(PropertyFactoryUtil.forName("type").in(new String[] {"blogs", "news"}))
.addOrder(OrderFactoryUtil.desc("createDate"));
query.setLimit(0, 100);

List <JournalArticle> journalArticles = JournalArticleLocalServiceUtil.dynamicQuery(query);


The subQuery is correlated with query by table alias “articleParent” that is referenced in eqProperty(“articleParent.articleId”) clause.

The results seems to work just fine. Yey!

! Important notice: subQuery also uses alias – “articleSub”. It seems unnecessary, as this alias is never referenced. However, removing it leads to weird incorrect results! (I haven"t been digging what exactly was it returning, but I’ve only got 1 record as a result when I removed “articleSub” alias).

This seems to be very weird, and very unpleasant, as it is easy to get caught by this very small change from DynamicQueryFactoryUtil.forClass(JournalArticle.class, “articleSub”, PortalClassLoaderUtil.getClassLoader()) to DynamicQueryFactoryUtil.forClass(JournalArticle.class, PortalClassLoaderUtil.getClassLoader()) that actually should have been completely legitimate.

Maybe I"ll investigate later what difference does it make in queries sent to DB.

That"s it for today.

About these ads