fixing Group By SQL for aggregating by date functions

The Group By SQL generated when aggregating on a Date field with date function (e.g. YEAR or MONTH) is incorrect.

Error in DataFlow query. Data flow: combined_sql_data_flow Query: SELECT DISTINCT YEAR(`activity_Discount_Code_Credit`.`used_on_175`) AS `used_on_month`
... irrelevant SQL omitted ...
GROUP BY (YEAR),  <- this is what breaks
... more SQL ...

In this case we want the GROUP BY to use the alias used_on_month which has already applied the YEAR() or other date aggregation function.

I was able to get this to work by editing the getSqlGroupByStatement() function in Civi/DataProcessor/DataSpecification/FieldSpecification.php to return $this-> alias:

117   public function getSqlGroupByStatement($table_alias) {
118     if ($this->sqlValueFormatFunction) {
119       if (isset($this->alias)) { // added
120         return $this->alias; // added
121       } else if (stripos($this->sqlValueFormatFunction, '%1') >= 0 && stripos($this->sqlValueFormatFunction, '%2') >= 0) { // existing clause but changed if to else if

(Line numbers relate to v 1.29 - I was on edge briefly but had unrelated issues so downgraded.)

This has not been properly tested - I will update as I work on this if I encounter any issues.

changed the description

Screencap of field config:

changed title from fixing group by SQL for aggregating by month + year functions to fixing Group By SQL for aggregating by date functions

mentioned in commit 2f28873e

You were close but actually this line was wrong

   if (stripos($this->sqlValueFormatFunction, '%1') >= 0 && stripos($this->sqlValueFormatFunction, '%2') >= 0) 

it should be

   if (stripos($this->sqlValueFormatFunction, '%1') !== false && stripos($this->sqlValueFormatFunction, '%2') !== false) 

It was testing for the wrong return values from stripos.

ok - that logic was already there - I just changed it from "if" to "else if"

Maybe the fix to the striposlogic will address the issue - to clarify, the change that I made was to return alias if it was set.