Hi Danny,
Using dynamic queries, in order to do count or other advanced queries, you can use:
- count(*) => ProjectionFactoryUtil.rowCount()
- sum or other other aggregations => ProjectionFactoryUtil.sqlProjection("sum(column) as sumColumn") , new String[] {"sumColumn"}, new Type[] {Type.BIG_DECIMAL});
In your example, perhaps you could do this. IMPORTANT!! I didn't compile or test this code!!
Projection groupProjection = ProjectionFactoryUtil.groupProperty("constituentId")
Projection sizeProjection = ProjectionFactoryUtil.sqlProjection("sum(Amount) as sumAmount") , new String[] {"sumAmount"}, new Type[] {Type.BIG_DECIMAL});
Projection projection = ProjectionFactoryUtil.projectionList().add(groupProjection).add(sizeProjection).add(ProjectionFactoryUtil.rowCount());
DynamicQuery query = ContributionLocalServiceUtil.dynamicQuery();
query.add(RestrictionsFactoryUtil.eq("companyId",companyId));
query.setProjection(projection);
query.addOrder(OrderFactoryUtil.desc("totalByDonor"));
I have never used both count and sum with "group by" in a dynamic query so perhaps you can have problems.
Hope it helps
