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

21 Responses to Liferay 6: querying webcontent dynamically

  1. adstickr says:

    Hello,

    I investigated this a bit and found that in case when we use

    DynamicQueryFactoryUtil.forClass(JournalArticle.class, PortalClassLoaderUtil.getClassLoader())

    is parsed like

    select max(this_.id_) as y0_ from JournalArticle this_ where this_.articleId=this_.articleId

    so we’ll get only one result (journal article with max version number)

    In other case

    DynamicQueryFactoryUtil.forClass(JournalArticle.class, “articleSub”, PortalClassLoaderUtil.getClassLoader())

    is parsed like

    select max(articleSub_.id_) as y0_ from JournalArticle articleSub_ where articleSub_.articleId=this_.articleId

    So we get the desired results.

    by the way nice finding and thanks for sharing…

  2. Great article. This helped me out quite a bit.

  3. Interesting, but I get de following exception:

    com.liferay.portal.kernel.exception.SystemException: org.hibernate.MappingException: Unknown entity: null

    • mvmn says:

      Hmm…
      Can you show a bit of your code, tell values of variables you put into DynamicQuery, and also mention which version of Liferay you use?

      • the code is a copy/paste from this site:

        ClassLoader cl= PortalClassLoaderUtil.getClassLoader();

        DynamicQuery subQuery= DynamicQueryFactoryUtil.forClass(JournalArticle.class, “articleSub”, cl);
        subQuery.add(PropertyFactoryUtil.forName(“articleId”).eqProperty(“articleParent.articleId”));
        subQuery.setProjection(ProjectionFactoryUtil.max(“id”));

        DynamicQuery dq= DynamicQueryFactoryUtil.forClass(JournalArticle.class, cl);
        dq.add(PropertyFactoryUtil.forName(“id”).eq(subQuery));
        dq.add(PropertyFactoryUtil.forName(“groupId”).eq(themeDisplay.getScopeGroupId()));
        dq.add(PropertyFactoryUtil.forName(“type”).in(new String[] { “Proyectos de Innovación” }));
        dq.addOrder(OrderFactoryUtil.desc(“createDate”));
        dq.add(RestrictionsFactoryUtil.eq(“approved”, true));
        dq.setLimit(0, 6);

  4. […] using the journal article "type" you should try with dynamic queriescheck this out: Liferay 6:querying webcontet dynamically Flag Please sign in to flag this as […]

  5. AMST says:

    tried to use this, but it only returns a long value, which coincidentally is the same as the dynamicqueryCount returned value.

    Copied all the code, and just changed the value of groupId to what’s needed – and it’s confirmed to be that value, coz when accessing with AssetEntry, there are results. The only thing is that dynamicquery is needed to be able to filter by asset type.

    • mvmn says:

      Share your code

      • AMST says:

        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(themeDisplay.getCompanyGroupId()))
        .add(PropertyFactoryUtil.forName(“type”).in(new String[] {“blogs”, “news”}))
        .addOrder(OrderFactoryUtil.desc(“createDate”));
        query.setLimit(0, 100);

        • AMST says:

          //for the expected results (tried to cast to AssetEntry, or use JournalEntry) and gives the same result
          List results = AssetEntryLocalServiceUtil.dynamicQuery(query);
          int groupTotal = (int) AssetEntryLocalServiceUtil.dynamicQueryCount(query);

          • mvmn says:

            The code seems ok (I use JournalArticleLocalServiceUtil.dynamicQuery(query) to run dynamic query, and you use AssetEntryLocalServiceUtil.dynamicQuery(query), but this should be essentially same call inside Liferay).

            What do you get in the List then? One object of Long type? Or something else too?
            Same core worked ok for people in LR6.0, and it’s hard to believe DynamicQueryFactoryUtil.forClass(JournalArticle.class) will just return Long.

  6. AMST says:

    yeah. the “list” just returns one value – which is of type long. I was wondering what it was and tried to print the result of the count and saw the same value. I also tried to cast the result to a JournalArticle (using JournalArticleLocalServiceUtil) and it gives the same result (a Long). :(

    • mvmn says:

      “tried to print the result of the count”
      I wasn’t talking about result of the count – that returns int.
      I was asking about List.

      “tried to cast the result to a JournalArticle (using JournalArticleLocalServiceUtil) and it gives the same result (a Long).”
      ? Cast is a cast.
      Can you just iterate over objects in list and print them out?
      List results = AssetEntryLocalServiceUtil.dynamicQuery(query);
      for(Object result : results) { System.out.println(result+” – class: “+result.getClass()); }

      • AMST says:

        this is weird…now, it’s printing a structure – but the class is com.liferay.portlet.journal.model.impl.JournalArticleImpl. One progress then – can’t this be casted to AssetEntry? Thanks!

        • mvmn says:

          Why would this be asset entry? In code we explicitly ask for JournalArticle (“DynamicQueryFactoryUtil.forClass(JournalArticle.class,…”).

          • AMST says:

            yeah… thing is, the AssetEntry structure is needed… but we need to filter by asset type “news” and that can’t be done with the getEntries method – reason for us to use the dynamicquery. And seems that the way to filter by asset type is using the JournalArticle class. Is there an alternative to this? Thanks!

  7. The query was very slow with 30k+ rows in JournalArticle table. Adding an index on articleId made is lightning fast:

    create index idx_journalarticle_article_id on journalarticle (articleId);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: