From 2ca319b3b11c431ff2f0fd222106e4641a08359c Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Sat, 19 Dec 2020 19:48:45 -0500
Subject: [PATCH] Add an explicit alias for sql functions

---
 ext/search/ang/crmSearchAdmin.module.js       | 33 +++++++++++--------
 .../crmSearchAdmin.component.js               |  5 ++-
 .../crmSearchFunction.component.js            |  7 +++-
 3 files changed, 27 insertions(+), 18 deletions(-)

diff --git a/ext/search/ang/crmSearchAdmin.module.js b/ext/search/ang/crmSearchAdmin.module.js
index e06b22339d..7e737d1096 100644
--- a/ext/search/ang/crmSearchAdmin.module.js
+++ b/ext/search/ang/crmSearchAdmin.module.js
@@ -162,26 +162,31 @@
         }
       }
       function parseExpr(expr) {
-        var result = {fn: null, modifier: ''},
-          fieldName = expr,
-          bracketPos = expr.indexOf('(');
+        if (!expr) {
+          return;
+        }
+        var splitAs = expr.split(' AS '),
+          info = {fn: null, modifier: ''},
+          fieldName = splitAs[0],
+          bracketPos = splitAs[0].indexOf('(');
         if (bracketPos >= 0) {
-          var parsed = expr.substr(bracketPos).match(/[ ]?([A-Z]+[ ]+)?([\w.:]+)/);
+          var parsed = splitAs[0].substr(bracketPos).match(/[ ]?([A-Z]+[ ]+)?([\w.:]+)/);
           fieldName = parsed[2];
-          result.fn = _.find(CRM.crmSearchAdmin.functions, {name: expr.substring(0, bracketPos)});
-          result.modifier = _.trim(parsed[1]);
+          info.fn = _.find(CRM.crmSearchAdmin.functions, {name: expr.substring(0, bracketPos)});
+          info.modifier = _.trim(parsed[1]);
         }
-        var fieldAndJoin = expr ? getFieldAndJoin(fieldName, searchEntity) : undefined;
-        if (fieldAndJoin.field) {
+        var fieldAndJoin = getFieldAndJoin(fieldName, searchEntity);
+        if (fieldAndJoin) {
           var split = fieldName.split(':'),
             prefixPos = split[0].lastIndexOf(fieldAndJoin.field.name);
-          result.path = split[0];
-          result.prefix = prefixPos > 0 ? result.path.substring(0, prefixPos) : '';
-          result.suffix = !split[1] ? '' : ':' + split[1];
-          result.field = fieldAndJoin.field;
-          result.join = fieldAndJoin.join;
+          info.path = split[0];
+          info.prefix = prefixPos > 0 ? info.path.substring(0, prefixPos) : '';
+          info.suffix = !split[1] ? '' : ':' + split[1];
+          info.field = fieldAndJoin.field;
+          info.join = fieldAndJoin.join;
+          info.alias = splitAs[1] || (info.fn ? info.fn.name + ':' + info.path + info.suffix : split[0]);
         }
-        return result;
+        return info;
       }
       return {
         getEntity: getEntity,
diff --git a/ext/search/ang/crmSearchAdmin/crmSearchAdmin.component.js b/ext/search/ang/crmSearchAdmin/crmSearchAdmin.component.js
index a162ca4ed6..36578e224e 100644
--- a/ext/search/ang/crmSearchAdmin/crmSearchAdmin.component.js
+++ b/ext/search/ang/crmSearchAdmin/crmSearchAdmin.component.js
@@ -373,7 +373,7 @@
         if (ctrl.savedSearch.api_params.groupBy.length) {
           _.each(ctrl.savedSearch.api_params.select, function(col, pos) {
             if (!_.contains(col, '(') && ctrl.canAggregate(col)) {
-              ctrl.savedSearch.api_params.select[pos] = ctrl.DEFAULT_AGGREGATE_FN + '(' + col + ')';
+              ctrl.savedSearch.api_params.select[pos] = ctrl.DEFAULT_AGGREGATE_FN + '(DISTINCT ' + col + ')';
             }
           });
         }
@@ -578,8 +578,7 @@
 
       $scope.formatResult = function(row, col) {
         var info = searchMeta.parseExpr(col),
-          key = info.fn ? (info.fn.name + ':' + info.path + info.suffix) : col,
-          value = row[key];
+          value = row[info.alias];
         if (info.fn && info.fn.name === 'COUNT') {
           return value;
         }
diff --git a/ext/search/ang/crmSearchAdmin/crmSearchFunction.component.js b/ext/search/ang/crmSearchAdmin/crmSearchFunction.component.js
index ff009085f8..0f0e5d2b90 100644
--- a/ext/search/ang/crmSearchAdmin/crmSearchFunction.component.js
+++ b/ext/search/ang/crmSearchAdmin/crmSearchFunction.component.js
@@ -37,8 +37,13 @@
         ctrl.writeExpr();
       };
 
+      // Make a sql-friendly alias for this expression
+      function makeAlias() {
+        return (ctrl.fn + '_' + (ctrl.modifier ? ctrl.modifier + '_' : '') + ctrl.path).replace(/[.:]/g, '_');
+      }
+
       this.writeExpr = function() {
-        ctrl.expr = ctrl.fn ? (ctrl.fn + '(' + (ctrl.modifier ? ctrl.modifier + ' ' : '') + ctrl.path + ')') : ctrl.path;
+        ctrl.expr = ctrl.fn ? (ctrl.fn + '(' + (ctrl.modifier ? ctrl.modifier + ' ' : '') + ctrl.path + ') AS ' + makeAlias()) : ctrl.path;
       };
     }
   });
-- 
2.25.1