From 23e56526739ee92501be045d801c0854fb544e28 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Wed, 30 Sep 2020 14:56:59 -0400 Subject: [PATCH] Search ext: Extend api4 smart groups to work with HAVING --- CRM/Contact/BAO/GroupContactCache.php | 16 ++++++---- CRM/Utils/API/HTMLInputCoder.php | 2 ++ ext/search/ang/search/SaveSmartGroup.ctrl.js | 4 +-- ext/search/ang/search/crmSearch.component.js | 4 +-- ext/search/ang/search/saveSmartGroup.html | 1 - .../phpunit/api/v4/Entity/SavedSearchTest.php | 32 +++++++++++++++++++ 6 files changed, 48 insertions(+), 11 deletions(-) diff --git a/CRM/Contact/BAO/GroupContactCache.php b/CRM/Contact/BAO/GroupContactCache.php index d4700184f8..ce08275766 100644 --- a/CRM/Contact/BAO/GroupContactCache.php +++ b/CRM/Contact/BAO/GroupContactCache.php @@ -9,6 +9,8 @@ +--------------------------------------------------------------------+ */ +use Civi\Api4\Query\SqlExpression; + /** * * @package CRM @@ -701,16 +703,18 @@ ORDER BY gc.contact_id, g.children */ protected static function getApiSQL(array $savedSearch, string $addSelect, string $excludeClause) { $apiParams = $savedSearch['api_params'] + ['select' => ['id'], 'checkPermissions' => FALSE]; - list($idField) = explode(' AS ', $apiParams['select'][0]); - $apiParams['select'] = [ - $addSelect, - $idField, - ]; + $idField = SqlExpression::convert($apiParams['select'][0], TRUE)->getAlias(); + // Unless there's a HAVING clause, we don't care about other columns + if (empty($apiParams['having'])) { + $apiParams['select'] = array_slice($apiParams['select'], 0, 1); + } $api = \Civi\API\Request::create($savedSearch['api_entity'], 'get', $apiParams); $query = new \Civi\Api4\Query\Api4SelectQuery($api); $query->forceSelectId = FALSE; $query->getQuery()->having("$idField $excludeClause"); - return $query->getSql(); + $sql = $query->getSql(); + // Place sql in a nested sub-query, otherwise HAVING is impossible on any field other than contact_id + return "SELECT $addSelect, `$idField` AS contact_id FROM ($sql) api_query"; } /** diff --git a/CRM/Utils/API/HTMLInputCoder.php b/CRM/Utils/API/HTMLInputCoder.php index fa5de2b964..50634d22bc 100644 --- a/CRM/Utils/API/HTMLInputCoder.php +++ b/CRM/Utils/API/HTMLInputCoder.php @@ -109,6 +109,8 @@ class CRM_Utils_API_HTMLInputCoder extends CRM_Utils_API_AbstractFieldCoder { 'header', // https://lab.civicrm.org/dev/core/issues/1286 'footer', + // SavedSearch entity + 'api_params', ]; $custom = CRM_Core_DAO::executeQuery('SELECT id FROM civicrm_custom_field WHERE html_type = "RichTextEditor"'); while ($custom->fetch()) { diff --git a/ext/search/ang/search/SaveSmartGroup.ctrl.js b/ext/search/ang/search/SaveSmartGroup.ctrl.js index ece503cf28..47837b2c8c 100644 --- a/ext/search/ang/search/SaveSmartGroup.ctrl.js +++ b/ext/search/ang/search/SaveSmartGroup.ctrl.js @@ -45,8 +45,7 @@ } // Pick the first applicable column for contact id - model.api_params.select[0] = _.intersection(model.api_params.select, _.pluck($scope.columns, 'id'))[0] || $scope.columns[0].name; - model.api_params.select.length = 1; + model.api_params.select.unshift(_.intersection(model.api_params.select, _.pluck($scope.columns, 'id'))[0] || $scope.columns[0].id); if (!CRM.checkPerm('administer reserved groups')) { $scope.groupEntityRefParams.api.params.is_reserved = 0; @@ -76,6 +75,7 @@ group.visibility = model.visibility; group.group_type = model.group_type; group.saved_search_id = '$id'; + model.api_params.select = _.unique(model.api_params.select); var savedSearch = { api_entity: model.api_entity, api_params: model.api_params diff --git a/ext/search/ang/search/crmSearch.component.js b/ext/search/ang/search/crmSearch.component.js index 33ed7f3ca9..cda1af0d57 100644 --- a/ext/search/ang/search/crmSearch.component.js +++ b/ext/search/ang/search/crmSearch.component.js @@ -568,8 +568,8 @@ api_params: _.cloneDeep(angular.extend({}, ctrl.params, {version: 4})) }; delete model.api_params.orderBy; - if (ctrl.load && ctrl.load.api_params) { - model.api_params.select = ctrl.load.api_params.select; + if (ctrl.load && ctrl.load.api_params && ctrl.load.api_params.select && ctrl.load.api_params.select[0]) { + model.api_params.select.unshift(ctrl.load.api_params.select[0]); } var options = CRM.utils.adjustDialogDefaults({ autoOpen: false, diff --git a/ext/search/ang/search/saveSmartGroup.html b/ext/search/ang/search/saveSmartGroup.html index a2ef056047..3589ff2234 100644 --- a/ext/search/ang/search/saveSmartGroup.html +++ b/ext/search/ang/search/saveSmartGroup.html @@ -1,6 +1,5 @@
-
diff --git a/tests/phpunit/api/v4/Entity/SavedSearchTest.php b/tests/phpunit/api/v4/Entity/SavedSearchTest.php index 8b0b7ee4ed..859e89f2d4 100644 --- a/tests/phpunit/api/v4/Entity/SavedSearchTest.php +++ b/tests/phpunit/api/v4/Entity/SavedSearchTest.php @@ -83,4 +83,36 @@ class SavedSearchTest extends UnitTestCase { $this->assertArrayNotHasKey($out['id'], $ins['values']); } + public function testSmartGroupWithHaving() { + $in = Contact::create(FALSE)->addValue('first_name', 'yes')->addValue('last_name', 'siree')->execute()->first(); + $in2 = Contact::create(FALSE)->addValue('first_name', 'yessir')->addValue('last_name', 'ee')->execute()->first(); + $out = Contact::create(FALSE)->addValue('first_name', 'yess')->execute()->first(); + + $savedSearch = civicrm_api4('SavedSearch', 'create', [ + 'values' => [ + 'api_entity' => 'Contact', + 'api_params' => [ + 'version' => 4, + 'select' => ['id', 'CONCAT(first_name, last_name) AS whole_name'], + 'where' => [ + ['id', '>=', $in['id']], + ], + 'having' => [ + ['whole_name', '=', 'yessiree'], + ], + ], + ], + 'chain' => [ + 'group' => ['Group', 'create', ['values' => ['title' => 'Having Test', 'saved_search_id' => '$id']], 0], + ], + ])->first(); + + // Oops we don't have an api4 syntax yet for selecting contacts in a group. + $ins = civicrm_api3('Contact', 'get', ['group' => $savedSearch['group']['name'], 'options' => ['limit' => 0]]); + $this->assertCount(2, $ins['values']); + $this->assertArrayHasKey($in['id'], $ins['values']); + $this->assertArrayHasKey($in2['id'], $ins['values']); + $this->assertArrayNotHasKey($out['id'], $ins['values']); + } + } -- 2.25.1