DynamicQuery "group by" and "having"

[Tarkan Corak, geändert vor 7 Jahren.]

Hi,

I need to implement following SQL statement as DynamicQuery:

select documentId, count(*)  from myNamespace_MyService
group by documentId
having count(*) > 1

The first part works pretty fine, but I couldn't anything for the "having count(*) > 1" statement.

This is my DynamicQuery so far:

DynamicQuery dynamicQuery = MyServiceLocalServiceUtil.dynamicQuery();
ProjectionList projectionList = ProjectionFactoryUtil.projectionList();
projectionList.add(ProjectionFactoryUtil.groupProperty("documentId"));
projectionList.add(ProjectionFactoryUtil.rowCount());
dynamicQuery.setProjection(projectionList);

Thanks in advance
Tarkan

[David H Nebinger, geändert vor 7 Jahren.]

Tarkan Corak:

I need to implement following SQL statement as DynamicQuery

Why? DQ is not always the right answer. you've got a custom query outlined there, why not run with it?

That said, usually you'll do something like this either with a Restrictions criteria:

projectionList.add(RestrictionsFactoryUtil.sqlRestriction("having count(*) > 1"));

or even a Projection:

String groupBy = "grpCount having count(*) > 1";
String[] alias = new String[] {"grpCount"};
com.liferay.portal.kernel.dao.orm.Type[] types = new Type[] {Type.INTEGER};

projectionList.add(ProjectionFactory.sqlGroupProjection("grpCount", groupBy, alias, types));

[Tarkan Corak, geändert vor 7 Jahren.]

David H Nebinger:

Tarkan Corak:

I need to implement following SQL statement as DynamicQuery

Why? DQ is not always the right answer. you've got a custom query outlined there, why not run with it?

Because I don't like the Service Builder way how custom queries are implemented. and I was wondering if there is a simple solution with DQ.

Thank you for your suggestions. But they don't work. I think I will switch to custom query.

[David H Nebinger, geändert vor 7 Jahren.]

Sorry the examples didn't work directly, was basically pulling them from memory.

Anyway they both offer ways of supporting "having", but they'd probably require some tinkering to get them doing what you'd expect.

Note that for the "having" support you'll have better luck googling for hibernate examples instead of ServiceBuilder. SB is based upon Hibernate, especially for DQ, so those examples will help, you just have to worry about package differences and some other tweaks.






Come meet me at the LSNA!

[Carmine Pagliaro, geändert vor 7 Jahren.]

Is there any update?

[Abhishek Jain, geändert vor 7 Jahren.]

You can use custom sql instead of dynamic queries.