How to use function in Dynamic Query

[Pankaj Kumar, modificado hace 8 años.]

Hi List[],

I want to add below filter condition in Dynamic Query in Journal Article Table.

DynamicQueryFactoryUtil.forClass(JournalArticle.class).add(PropertyFactoryUtil.forName("structureId").
eq(structureId)).add(PropertyFactoryUtil.forName("status").eq(status)).add(PropertyFactoryUtil.forName("userId").in(userID)).
add(PropertyFactoryUtil.forName("TRUNC(sysdate - status_date) <= 30
addOrder(OrderFactoryUtil.desc("modifiedDate"));


But i am getting hibernate exception.

Please tell me can i do using Dynamic Query or should i use Custom SQL for this.

Thanks & Regards,
Pankaj Semwal

[Bijan Vakili, modificado hace 8 años.]

Hello Pankaj Semwal,
Thanks for posting.

Attach relevant logs for more details.

Most likely the issue is following:

Caused by: org.hibernate.QueryException: could not resolve property: TRUNC(sysdate - status_date) <= 30  of: com.liferay.portlet.journal.model.impl.JournalArticleImpl

Here is working code for similar:

import java.util.*;
import com.liferay.portal.kernel.dao.orm.*;
import com.liferay.portlet.journal.model.*;
import com.liferay.portlet.journal.service.*;
import com.liferay.portal.model.*;
import com.liferay.portal.service.*;

try {
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(JournalArticle.class);
String structureId = 86456 + "";
int status = 0;
long[] userIds = new long[1];
userIds[0] = 18902;
List results = null;
Calendar calendar = Calendar.getInstance();
Date statusDate = calendar.getTime();
calendar.add(Calendar.DATE, -30);
Date fromDate = calendar.getTime();

Property structureIdProperty = PropertyFactoryUtil.forName("structureId");
Property statusProperty = PropertyFactoryUtil.forName("status");
Property userIdProperty = PropertyFactoryUtil.forName("userId");
Property statusDateProperty = PropertyFactoryUtil.forName("createDate");

dynamicQuery.add(structureIdProperty.eq(structureId));
dynamicQuery.add(statusProperty.eq(status));
dynamicQuery.add(userIdProperty.in(userIds));
dynamicQuery.add(statusDateProperty.between(fromDate, statusDate));
dynamicQuery.addOrder(OrderFactoryUtil.desc("modifiedDate"));
results = JournalArticleLocalServiceUtil.dynamicQuery(dynamicQuery);
out.println(results);

} catch (Exception e) {
e.printStackTrace();
}

Gist for copying and pasting the code:
https://gist.github.com/bmvakili/d9afddca884e86d78a13


So you can try rewriting as above.

[Pankaj Kumar, modificado hace 8 años.]

Thanks its resolved my issue.