DynamicQuery "group by" and "having"

[Tarkan Corak, modifié il y a 7 années.]

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, modifié il y a 7 années.]

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, modifié il y a 7 années.]

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, modifié il y a 7 années.]

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, modifié il y a 7 années.]

Is there any update?

[Abhishek Jain, modifié il y a 7 années.]

You can use custom sql instead of dynamic queries.