Advice on generating SQL with GROUP BY and HAVING

[jay.l.fisher]

I have a data source that can return results using a different set of GROUP BY fields based on the user's choice from a dropdown. I'm using a Velocity expressions to customize the SQL SELECT clause. Fields that aren't to be included in the GROUP BY list I wrap in a SQL aggregate function like SUM or MIN. That all works fine.

The user can also create criteria using a FilterBuilder. That's where the problem comes in. If a field referenced in the criteria has been wrapped in an aggregate function I need to move it from the WHERE clause to a HAVING clause and use the aggregate function instead of the simple field name.

Do you have any suggestions on how to do that? Are there any utility methods in SQLDataSource that might help me accomplish this?

[Isomorphic]

Not sure how you're telling the server that a given field has an aggregate applied, but one possible approach would be to use the server-side API dsRequest.getAdvancedCriteria() and use the resulting AdvancedCriteria object to remove criteria that need to be moved to the HAVING clause. Then you could take that subset of the criteria and expose it to Velocity via dsRequest.addToTemplateContext() (as either a ready-to-use SQL string, or as a Collection of some kind you intend to process in Velocity).

See also SQLDataSource.getClause() - this could allow you have some parts of the HAVING clause still generated by SQLDataSource, by creating dsRequests that have subsets of the AdvancedCriteria and asking for the where clause that would result.