From 2246b4fa9d5db221206f117ef561578f0c10b40e Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Mon, 8 Aug 2022 17:35:27 -0400 Subject: [PATCH] SearchKit - Allow aggreation by partial dates Supports the EXTRACT() sql function so that partial dates can be extracted. See dev/core#3700 --- Civi/Api4/Query/SqlExpression.php | 2 +- Civi/Api4/Query/SqlFunction.php | 6 +- Civi/Api4/Query/SqlFunctionAVG.php | 2 +- Civi/Api4/Query/SqlFunctionCOUNT.php | 2 +- Civi/Api4/Query/SqlFunctionEXTRACT.php | 66 +++++++++++++++++++ Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php | 2 +- Civi/Api4/Query/SqlFunctionMAX.php | 2 +- Civi/Api4/Query/SqlFunctionMIN.php | 2 +- Civi/Api4/Query/SqlFunctionSUM.php | 2 +- ext/search_kit/ang/crmSearchAdmin.module.js | 14 +++- .../crmSearchClause.component.js | 3 +- .../crmSearchFunction.component.js | 28 +++++--- .../ang/crmSearchAdmin/crmSearchFunction.html | 17 ++--- .../crmSearchFunctionFlag.component.js | 27 ++++++++ .../crmSearchAdmin/crmSearchFunctionFlag.html | 13 ++++ .../phpunit/api/v4/Action/SqlFunctionTest.php | 8 +++ 16 files changed, 162 insertions(+), 34 deletions(-) create mode 100644 Civi/Api4/Query/SqlFunctionEXTRACT.php create mode 100644 ext/search_kit/ang/crmSearchAdmin/crmSearchFunctionFlag.component.js create mode 100644 ext/search_kit/ang/crmSearchAdmin/crmSearchFunctionFlag.html diff --git a/Civi/Api4/Query/SqlExpression.php b/Civi/Api4/Query/SqlExpression.php index fca26025b3..d52c0a2d41 100644 --- a/Civi/Api4/Query/SqlExpression.php +++ b/Civi/Api4/Query/SqlExpression.php @@ -192,7 +192,7 @@ abstract class SqlExpression { * @return mixed|null */ protected function captureKeyword($keywords, &$arg) { - foreach ($keywords as $key) { + foreach (array_filter($keywords, 'strlen') as $key) { // Match keyword followed by a space or eol if (strpos($arg, $key . ' ') === 0 || rtrim($arg) === $key) { $arg = ltrim(substr($arg, strlen($key))); diff --git a/Civi/Api4/Query/SqlFunction.php b/Civi/Api4/Query/SqlFunction.php index 7cdb358aac..32843bafa9 100644 --- a/Civi/Api4/Query/SqlFunction.php +++ b/Civi/Api4/Query/SqlFunction.php @@ -163,7 +163,7 @@ abstract class SqlFunction extends SqlExpression { $params = []; foreach (static::params() as $param) { // Merge in defaults to ensure each param has these properties - $params[] = $param + [ + $param += [ 'name' => NULL, 'label' => ts('Select'), 'min_expr' => 1, @@ -174,6 +174,10 @@ abstract class SqlFunction extends SqlExpression { 'must_be' => ['SqlField', 'SqlFunction', 'SqlString', 'SqlNumber', 'SqlNull'], 'api_default' => NULL, ]; + if (!$param['max_expr']) { + $param['must_be'] = []; + } + $params[] = $param; } return $params; } diff --git a/Civi/Api4/Query/SqlFunctionAVG.php b/Civi/Api4/Query/SqlFunctionAVG.php index d385b29eb9..3d951be66f 100644 --- a/Civi/Api4/Query/SqlFunctionAVG.php +++ b/Civi/Api4/Query/SqlFunctionAVG.php @@ -23,7 +23,7 @@ class SqlFunctionAVG extends SqlFunction { protected static function params(): array { return [ [ - 'flag_before' => ['DISTINCT' => ts('Distinct')], + 'flag_before' => ['' => NULL, 'DISTINCT' => ts('Distinct')], 'must_be' => ['SqlField'], ], ]; diff --git a/Civi/Api4/Query/SqlFunctionCOUNT.php b/Civi/Api4/Query/SqlFunctionCOUNT.php index 360ce7d8af..2f58ce09b6 100644 --- a/Civi/Api4/Query/SqlFunctionCOUNT.php +++ b/Civi/Api4/Query/SqlFunctionCOUNT.php @@ -23,7 +23,7 @@ class SqlFunctionCOUNT extends SqlFunction { protected static function params(): array { return [ [ - 'flag_before' => ['DISTINCT' => ts('Distinct')], + 'flag_before' => ['' => NULL, 'DISTINCT' => ts('Distinct')], 'max_expr' => 1, 'must_be' => ['SqlField', 'SqlWild'], ], diff --git a/Civi/Api4/Query/SqlFunctionEXTRACT.php b/Civi/Api4/Query/SqlFunctionEXTRACT.php new file mode 100644 index 0000000000..ff555ec46b --- /dev/null +++ b/Civi/Api4/Query/SqlFunctionEXTRACT.php @@ -0,0 +1,66 @@ + ts('Unit'), + 'flag_before' => [ + 'SECOND' => ts('Seconds'), + 'MINUTE' => ts('Minutes'), + 'HOUR' => ts('Hours'), + 'DAY' => ts('Days'), + 'WEEK' => ts('Weeks'), + 'MONTH' => ts('Months'), + 'QUARTER' => ts('Quarters'), + 'YEAR' => ts('Years'), + 'MINUTE_SECOND' => ts('Minutes:Seconds'), + 'HOUR_SECOND' => ts('Hours:Minutes:Seconds'), + 'HOUR_MINUTE' => ts('Hours:Minutes'), + 'DAY_SECOND' => ts('Days Hours:Minutes:Seconds'), + 'DAY_MINUTE' => ts('Days Hours:Minutes'), + 'DAY_HOUR' => ts('Days Hours'), + 'YEAR_MONTH' => ts('Years-Months'), + ], + 'max_expr' => 0, + 'optional' => FALSE, + ], + [ + 'name' => 'FROM', + 'must_be' => ['SqlField'], + ], + ]; + } + + /** + * @return string + */ + public static function getTitle(): string { + return ts('Partial Date'); + } + + /** + * @return string + */ + public static function getDescription(): string { + return ts('The numeric month (1-12) of a date.'); + } + +} diff --git a/Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php b/Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php index 3c90f1a323..e9f72907cc 100644 --- a/Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php +++ b/Civi/Api4/Query/SqlFunctionGROUP_CONCAT.php @@ -23,7 +23,7 @@ class SqlFunctionGROUP_CONCAT extends SqlFunction { protected static function params(): array { return [ [ - 'flag_before' => ['DISTINCT' => ts('Distinct')], + 'flag_before' => ['' => NULL, 'DISTINCT' => ts('Distinct')], 'max_expr' => 1, 'must_be' => ['SqlField', 'SqlFunction'], 'optional' => FALSE, diff --git a/Civi/Api4/Query/SqlFunctionMAX.php b/Civi/Api4/Query/SqlFunctionMAX.php index 24ad68af4a..c1aabcd9ff 100644 --- a/Civi/Api4/Query/SqlFunctionMAX.php +++ b/Civi/Api4/Query/SqlFunctionMAX.php @@ -23,7 +23,7 @@ class SqlFunctionMAX extends SqlFunction { protected static function params(): array { return [ [ - 'flag_before' => ['DISTINCT' => ts('Distinct')], + 'flag_before' => ['' => NULL, 'DISTINCT' => ts('Distinct')], 'must_be' => ['SqlField'], ], ]; diff --git a/Civi/Api4/Query/SqlFunctionMIN.php b/Civi/Api4/Query/SqlFunctionMIN.php index 0883ee4c8f..663915ff61 100644 --- a/Civi/Api4/Query/SqlFunctionMIN.php +++ b/Civi/Api4/Query/SqlFunctionMIN.php @@ -23,7 +23,7 @@ class SqlFunctionMIN extends SqlFunction { protected static function params(): array { return [ [ - 'flag_before' => ['DISTINCT' => ts('Distinct')], + 'flag_before' => ['' => NULL, 'DISTINCT' => ts('Distinct')], 'must_be' => ['SqlField'], ], ]; diff --git a/Civi/Api4/Query/SqlFunctionSUM.php b/Civi/Api4/Query/SqlFunctionSUM.php index 71c56611b8..879baf9d79 100644 --- a/Civi/Api4/Query/SqlFunctionSUM.php +++ b/Civi/Api4/Query/SqlFunctionSUM.php @@ -21,7 +21,7 @@ class SqlFunctionSUM extends SqlFunction { protected static function params(): array { return [ [ - 'flag_before' => ['DISTINCT' => ts('Distinct')], + 'flag_before' => ['' => NULL, 'DISTINCT' => ts('Distinct')], 'must_be' => ['SqlField'], ], ]; diff --git a/ext/search_kit/ang/crmSearchAdmin.module.js b/ext/search_kit/ang/crmSearchAdmin.module.js index 729be45d64..0f1238d5b0 100644 --- a/ext/search_kit/ang/crmSearchAdmin.module.js +++ b/ext/search_kit/ang/crmSearchAdmin.module.js @@ -184,8 +184,8 @@ function getKeyword(whitelist) { var keyword; - _.each(whitelist, function(flag) { - if (argString.indexOf(flag) === 0) { + _.each(_.filter(whitelist), function(flag) { + if (argString.indexOf(flag + ' ') === 0) { keyword = flag; argString = _.trim(argString.substr(flag.length)); return false; @@ -216,16 +216,18 @@ var exprCount = 0, expr, flagBefore; argString = _.trim(argString); - if (!argString.length || (param.name && !getKeyword(param.name))) { + if (!argString.length || (param.name && !_.startsWith(argString, param.name + ' '))) { return false; } if (param.max_expr) { while (++exprCount <= param.max_expr && argString.length) { flagBefore = getKeyword(_.keys(param.flag_before || {})); + var name = getKeyword(param.name ? [param.name] : []); expr = getExpr(); if (expr) { expr.param = param.name || index; expr.flag_before = flagBefore; + expr.name = name; info.args.push(expr); } // Only continue if an expression was found and followed by a comma @@ -237,6 +239,12 @@ if (expr && !_.isEmpty(expr.flag_after)) { _.last(info.args).flag_after = getKeyword(_.keys(param.flag_after)); } + } else if (param.flag_before && !param.optional) { + flagBefore = getKeyword(_.keys(param.flag_before)); + info.args.push({ + value: '', + flag_before: flagBefore + }); } }); if (!info.data_type && info.args.length) { diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchClause.component.js b/ext/search_kit/ang/crmSearchAdmin/crmSearchClause.component.js index 4b7b4b05ee..9bd637ab3c 100644 --- a/ext/search_kit/ang/crmSearchAdmin/crmSearchClause.component.js +++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchClause.component.js @@ -36,7 +36,8 @@ // Gets the first arg of type "field" function getFirstArgFromExpr(expr) { if (!(expr in meta)) { - meta[expr] = _.findWhere(searchMeta.parseExpr(expr).args, {type: 'field'}); + var args = searchMeta.parseExpr(expr).args; + meta[expr] = _.findWhere(args, {type: 'field'}); } return meta[expr] || {}; } diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js index 251afd3d50..86e063834f 100644 --- a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js +++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.component.js @@ -48,7 +48,8 @@ var param = ctrl.getParam(ctrl.args.length); ctrl.args.push({ type: ctrl.exprTypes[exprType].type, - flag_before: _.keys(param.flag_before)[0], + flag_before: _.filter(_.keys(param.flag_before))[0], + name: param.name, value: exprType === 'SqlNumber' ? 0 : '' }); }; @@ -62,7 +63,7 @@ while ( (ctrl.args.length - index < param.min_expr) && // TODO: Handle named params like "ORDER BY" - !param.name && + !(param.name && param.optional) && (!param.optional || param.must_be.length === 1) ) { ctrl.addArg(param.must_be[0]); @@ -71,7 +72,9 @@ } this.getParam = function(index) { - return ctrl.fn.params[index] || _.last(ctrl.fn.params); + if (ctrl.fn) { + return ctrl.fn.params[index] || _.last(ctrl.fn.params); + } }; this.canAddArg = function() { @@ -80,8 +83,8 @@ } var param = ctrl.getParam(ctrl.args.length), index = ctrl.fn.params.indexOf(param); - // TODO: Handle named params like "ORDER BY" - if (param.name) { + // TODO: Handle optional named params like "ORDER BY" + if (param.name && param.optional) { return false; } return ctrl.args.length - index < param.max_expr; @@ -123,20 +126,25 @@ this.selectFunction = function() { ctrl.fn = _.find(CRM.crmSearchAdmin.functions, {name: ctrl.fnName}); - delete ctrl.fieldArg.flag_before; ctrl.args = [ctrl.fieldArg]; if (ctrl.fn) { var exprType, pos = 0; // Add non-field args to the beginning if needed while (!_.includes(ctrl.fn.params[pos].must_be, 'SqlField')) { - exprType = ctrl.fn.params[pos].must_be[0]; + exprType = _.first(ctrl.fn.params[pos].must_be); ctrl.args.splice(pos, 0, { - type: ctrl.exprTypes[exprType].type, + type: exprType ? ctrl.exprTypes[exprType].type : null, + flag_before: _.filter(_.keys(ctrl.fn.params[pos].flag_before))[0], + name: ctrl.fn.params[pos].name, value: exprType === 'SqlNumber' ? 0 : '' }); ++pos; } + // Update fieldArg + var fieldParam = ctrl.fn.params[pos]; + ctrl.fieldArg.flag_before = _.keys(fieldParam.flag_before)[0]; + ctrl.fieldArg.name = fieldParam.name; initFunction(); } ctrl.writeExpr(); @@ -160,8 +168,8 @@ this.writeExpr = function() { if (ctrl.fnName) { var args = _.transform(ctrl.args, function(args, arg, index) { - if (arg.value) { - var prefix = arg.flag_before ? (index ? ' ' : '') + arg.flag_before + ' ' : (index ? ', ' : ''); + if (arg.value || arg.flag_before) { + var prefix = arg.flag_before || arg.name ? (index ? ' ' : '') + (arg.flag_before || arg.name) + (arg.value ? ' ' : '') : (index ? ', ' : ''); args.push(prefix + (arg.type === 'string' ? JSON.stringify(arg.value) : arg.value)); } }); diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.html b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.html index 4ffb4f1326..431c2b15d2 100644 --- a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.html +++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunction.html @@ -2,20 +2,13 @@ - -
- - + +
+ + - +
diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunctionFlag.component.js b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunctionFlag.component.js new file mode 100644 index 0000000000..cf2dc92af3 --- /dev/null +++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunctionFlag.component.js @@ -0,0 +1,27 @@ +(function(angular, $, _) { + "use strict"; + + angular.module('crmSearchAdmin').component('crmSearchFunctionFlag', { + bindings: { + arg: '<', + param: '<', + writeExpr: '&' + }, + templateUrl: '~/crmSearchAdmin/crmSearchFunctionFlag.html', + controller: function($scope) { + var ts = $scope.ts = CRM.ts('org.civicrm.search_kit'), + ctrl = this; + + this.$onInit = function() { + if (!ctrl.param || !ctrl.param.flag_before) { + this.widget = null; + } else if (_.keys(ctrl.param.flag_before).length === 2 && '' in ctrl.param.flag_before) { + this.widget = 'checkbox'; + } else { + this.widget = 'select'; + } + }; + } + }); + +})(angular, CRM.$, CRM._); diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchFunctionFlag.html b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunctionFlag.html new file mode 100644 index 0000000000..8d9d19b4b0 --- /dev/null +++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchFunctionFlag.html @@ -0,0 +1,13 @@ + + + + + + diff --git a/tests/phpunit/api/v4/Action/SqlFunctionTest.php b/tests/phpunit/api/v4/Action/SqlFunctionTest.php index 3fb5d18dc2..fb7068c18c 100644 --- a/tests/phpunit/api/v4/Action/SqlFunctionTest.php +++ b/tests/phpunit/api/v4/Action/SqlFunctionTest.php @@ -326,6 +326,14 @@ class SqlFunctionTest extends Api4TestBase implements TransactionalInterface { ->addSelect('birth_date') ->execute()->single(); $this->assertEquals('2009-11-11', $result['birth_date']); + + // Try in GROUP_BY + $result = Contact::get(FALSE) + ->addSelect('COUNT(id) AS counted') + ->addWhere('last_name', '=', $lastName) + ->addGroupBy('EXTRACT(YEAR FROM birth_date)') + ->execute(); + $this->assertCount(2, $result); } } -- 2.25.1