Need to find Max Value in Liferay table using Service Builder

Question

I have built a Liferay portlet using Service Builder and it has one table. One of the fields holds a double value called 'ZValue'. I need to add to my -LocalServiceImpl.java file a public method that will return the maximum value currently found in the field 'ZValue'. I was hoping there was a Liferay class similar to DynamicQuery that instead returns a single value. I know I can return all the records and cycle through them myself to get the maximum value, but I'm sure there is a simpler way to get the max value.

What I have found in my search of stackoverflow is:

DynamicQuery query = DynamicQueryFactoryUtil.forClass(classname);
query.setProjection(ProjectionFactoryUtil.max("ZValue"));

but I didn't understand how to actually return the value as a DynamicQuery returns a list and not a single value.

Answer 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);

Answer 2

Liferay 7.2 dxp sp5

import com.liferay.portal.kernel.dao.orm.Criterion;
import com.liferay.portal.kernel.dao.orm.DynamicQuery;
import com.liferay.portal.kernel.dao.orm.ProjectionFactoryUtil;
import com.liferay.portal.kernel.dao.orm.PropertyFactoryUtil;
import com.liferay.portal.kernel.dao.orm.RestrictionsFactoryUtil;

In my case I want to fetch maximum value of a column so I used this query

DynamicQuery dynamicQuery = MyTableLocalServiceUtil.dynamicQuery();
dynamicQuery.setProjection(ProjectionFactoryUtil.max("columnName1"));
DynamicQuery dynamicQuery2 = MyTableLocalServiceUtil.dynamicQuery();
dynamicQuery2.add(PropertyFactoryUtil.forName("columnName1").eq(dynamicQuery));
List<Object> dynamicQuery3 = MyTableLocalServiceUtil.dynamicQuery(dynamicQuery2);

This will return a list but having maximum value in column 'columnName1'. Here one thing I observed that if columnName1 having more than one value which is maximum then it will return that number of columns. Support if columnName1 having max value of 50 but 3 rows having 50 then it will return 3 objects inside the list.

Second case: find max value of columnName1 and columnName2=anyValue

DynamicQuery dynamicQuery = MyTableLocalServiceUtil.dynamicQuery();
dynamicQuery.setProjection(ProjectionFactoryUtil.max("columnName1"));
DynamicQuery dynamicQuery2 = MyTableLocalServiceUtil.dynamicQuery();
dynamicQuery2.add(PropertyFactoryUtil.forName("columnName2").eq(anyValue));
dynamicQuery2.add(PropertyFactoryUtil.forName("columnName1").eq(dynamicQuery));
List<Object> dynamicQuery3 = MyTableLocalServiceUtil.dynamicQuery(dynamicQuery2);

In this case what I found that dynamic query making and operation of max value of columnName1 and columnName2=anyValue. if a row where columnName2=anyValue doesn't have maximum value of columnName1 then it is returning empty list.