Need to find Max Value in Liferay table using Service Builder

Solution 1:

In the following example, we query the JournalArticle table to find all the articles that match a certain criteria, and then only get the newest version of each of those (the max).

As Pankaj said, you need to create two dynamic queries. The first one is used to specify that you need the max to be returned:

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

articleSub is just an alias you assign to the query. ProjectionFactoryUtil.max will return the max.

The second one is the actual query. It will take the value returned from the first query:

DynamicQuery query = DynamicQueryFactoryUtil.forClass(JournalArticle.class, "articleParent", PortalClassLoaderUtil.getClassLoader())
            .add(PropertyFactoryUtil.forName("id").eq(subQuery))
            .add(PropertyFactoryUtil.forName("type").eq("Slider"));

The execution of this DynamicQuery will return a List<JournalArticle> with all the matches returned by the generated SQL sentence.

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