From 266e8debd8fc472fff784b81191c1d3e1b24ff00 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Sun, 21 Mar 2021 16:32:54 -0400 Subject: [PATCH] SearchKit - Add "EXCLUDE" join type, to search for entities that do not have a relationship to another entity --- Civi/Api4/Generic/DAOGetAction.php | 6 ++--- Civi/Api4/Query/Api4SelectQuery.php | 13 +++++++++- ang/api4Explorer/Explorer.js | 4 +-- ext/search/CRM/Search/Upgrader.php | 23 +++++++++++++++++ .../ang/crmSearchAdmin/compose/criteria.html | 6 ++--- .../crmSearchAdmin.component.js | 25 +++++++++++++++---- tests/phpunit/api/v4/Action/FkJoinTest.php | 12 +++++++-- 7 files changed, 73 insertions(+), 16 deletions(-) diff --git a/Civi/Api4/Generic/DAOGetAction.php b/Civi/Api4/Generic/DAOGetAction.php index 055eb72370..778cc3de40 100644 --- a/Civi/Api4/Generic/DAOGetAction.php +++ b/Civi/Api4/Generic/DAOGetAction.php @@ -164,16 +164,16 @@ class DAOGetAction extends AbstractGetAction { /** * @param string $entity - * @param bool $required + * @param string|bool $type * @param string $bridge * @param array ...$conditions * @return DAOGetAction */ - public function addJoin(string $entity, bool $required = FALSE, $bridge = NULL, ...$conditions): DAOGetAction { + public function addJoin(string $entity, $type = 'LEFT', $bridge = NULL, ...$conditions): DAOGetAction { if ($bridge) { array_unshift($conditions, $bridge); } - array_unshift($conditions, $entity, $required); + array_unshift($conditions, $entity, $type); $this->join[] = $conditions; return $this; } diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php index c97d07fb78..efaba5c296 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -595,7 +595,18 @@ class Api4SelectQuery { $alias = $alias ? \CRM_Utils_String::munge($alias, '_', 256) : strtolower($entity); // First item in the array is a boolean indicating if the join is required (aka INNER or LEFT). // The rest are join conditions. - $side = array_shift($join) ? 'INNER' : 'LEFT'; + $side = array_shift($join); + // If omitted, supply default (LEFT); and legacy support for boolean values + if (!is_string($side)) { + $side = $side ? 'INNER' : 'LEFT'; + } + if (!in_array($side, ['INNER', 'LEFT', 'EXCLUDE'])) { + throw new \API_Exception("Illegal value for join side: '$side'."); + } + if ($side === 'EXCLUDE') { + $side = 'LEFT'; + $this->api->addWhere("$alias.id", 'IS NULL'); + } // Add all fields from joined entity to spec $joinEntityGet = \Civi\API\Request::create($entity, 'get', ['version' => 4, 'checkPermissions' => $this->getCheckPermissions()]); $joinEntityFields = $joinEntityGet->entityFields(); diff --git a/ang/api4Explorer/Explorer.js b/ang/api4Explorer/Explorer.js index 87ad2fe32f..f1918b7e40 100644 --- a/ang/api4Explorer/Explorer.js +++ b/ang/api4Explorer/Explorer.js @@ -56,7 +56,7 @@ $scope.loading = false; $scope.controls = {}; $scope.langs = ['php', 'js', 'ang', 'cli']; - $scope.joinTypes = [{k: false, v: 'FALSE (LEFT JOIN)'}, {k: true, v: 'TRUE (INNER JOIN)'}]; + $scope.joinTypes = [{k: 'LEFT', v: 'LEFT JOIN'}, {k: 'INNER', v: 'INNER JOIN'}, {k: 'EXCLUDE', v: 'EXCLUDE'}]; $scope.bridgeEntities = _.filter(schema, function(entity) {return _.includes(entity.type, 'EntityBridge');}); $scope.code = { php: [ @@ -529,7 +529,7 @@ $timeout(function() { if (field) { if (name === 'join') { - $scope.params[name].push([field + ' AS ' + _.snakeCase(field), false]); + $scope.params[name].push([field + ' AS ' + _.snakeCase(field), 'LEFT']); ctrl.buildFieldList(); } else if (typeof objectParams[name] === 'undefined') { diff --git a/ext/search/CRM/Search/Upgrader.php b/ext/search/CRM/Search/Upgrader.php index f8fbc76f22..3f6de4d5ee 100644 --- a/ext/search/CRM/Search/Upgrader.php +++ b/ext/search/CRM/Search/Upgrader.php @@ -110,4 +110,27 @@ class CRM_Search_Upgrader extends CRM_Search_Upgrader_Base { return TRUE; } + /** + * Upgrade 1003 - update APIv4 join syntax in saved searches + * @return bool + */ + public function upgrade_1003() { + $this->ctx->log->info('Applying 1003 - update APIv4 join syntax in saved searches.'); + $savedSearches = \Civi\Api4\SavedSearch::get(FALSE) + ->addSelect('id', 'api_params') + ->addWhere('api_params', 'IS NOT NULL') + ->execute(); + foreach ($savedSearches as $savedSearch) { + foreach ($savedSearch['api_params']['join'] ?? [] as $i => $join) { + $savedSearch['api_params']['join'][$i][1] = empty($join[1]) ? 'LEFT' : 'INNER'; + } + if (!empty($savedSearch['api_params']['join'])) { + \Civi\Api4\SavedSearch::update(FALSE) + ->setValues($savedSearch) + ->execute(); + } + } + return TRUE; + } + } diff --git a/ext/search/ang/crmSearchAdmin/compose/criteria.html b/ext/search/ang/crmSearchAdmin/compose/criteria.html index 3185d4cb86..7eb92ef116 100644 --- a/ext/search/ang/crmSearchAdmin/compose/criteria.html +++ b/ext/search/ang/crmSearchAdmin/compose/criteria.html @@ -3,9 +3,8 @@
- + - @@ -16,7 +15,8 @@
- + +
diff --git a/ext/search/ang/crmSearchAdmin/crmSearchAdmin.component.js b/ext/search/ang/crmSearchAdmin/crmSearchAdmin.component.js index a26a5b4066..feac4a41e1 100644 --- a/ext/search/ang/crmSearchAdmin/crmSearchAdmin.component.js +++ b/ext/search/ang/crmSearchAdmin/crmSearchAdmin.component.js @@ -24,8 +24,12 @@ // Have the filters (WHERE, HAVING, GROUP BY, JOIN) changed? this.stale = true; - $scope.controls = {tab: 'compose'}; - $scope.joinTypes = [{k: false, v: ts('Optional')}, {k: true, v: ts('Required')}]; + $scope.controls = {tab: 'compose', joinType: 'LEFT'}; + $scope.joinTypes = [ + {k: 'LEFT', v: ts('With (optional)')}, + {k: 'INNER', v: ts('With (required)')}, + {k: 'EXCLUDE', v: ts('Without')}, + ]; // Try to create a sensible list of entities one might want to search for, // excluding those whos primary purpose is to provide joins or option lists to other entities var primaryEntities = _.filter(CRM.crmSearchAdmin.schema, function(entity) { @@ -245,7 +249,7 @@ ctrl.savedSearch.api_params.join = ctrl.savedSearch.api_params.join || []; var join = searchMeta.getJoin($scope.controls.join), entity = searchMeta.getEntity(join.entity), - params = [$scope.controls.join, false]; + params = [$scope.controls.join, $scope.controls.joinType || 'LEFT']; _.each(_.cloneDeep(join.conditions), function(condition) { params.push(condition); }); @@ -253,7 +257,7 @@ params.push(condition); }); ctrl.savedSearch.api_params.join.push(params); - if (entity.label_field) { + if (entity.label_field && $scope.controls.joinType !== 'EXCLUDE') { ctrl.savedSearch.api_params.select.push(join.alias + '.' + entity.label_field); } loadFieldOptions(); @@ -266,6 +270,10 @@ this.removeJoin = function(index) { var alias = searchMeta.getJoin(ctrl.savedSearch.api_params.join[index][0]).alias; ctrl.clearParam('join', index); + removeJoinStuff(alias); + }; + + function removeJoinStuff(alias) { _.remove(ctrl.savedSearch.api_params.select, function(item) { var pattern = new RegExp('\\b' + alias + '\\.'); return pattern.test(item.split(' AS ')[0]); @@ -274,10 +282,17 @@ return clauseUsesJoin(clause, alias); }); _.eachRight(ctrl.savedSearch.api_params.join, function(item, i) { - if (searchMeta.getJoin(item[0]).alias.indexOf(alias) === 0) { + var joinAlias = searchMeta.getJoin(item[0]).alias; + if (joinAlias !== alias && joinAlias.indexOf(alias) === 0) { ctrl.removeJoin(i); } }); + } + + this.changeJoinType = function(join) { + if (join[1] === 'EXCLUDE') { + removeJoinStuff(searchMeta.getJoin(join[0]).alias); + } }; $scope.changeGroupBy = function(idx) { diff --git a/tests/phpunit/api/v4/Action/FkJoinTest.php b/tests/phpunit/api/v4/Action/FkJoinTest.php index faed114075..fe5f4e8363 100644 --- a/tests/phpunit/api/v4/Action/FkJoinTest.php +++ b/tests/phpunit/api/v4/Action/FkJoinTest.php @@ -113,6 +113,14 @@ class FkJoinTest extends UnitTestCase { $this->assertEquals('US', $contacts[0]['address.country.iso_code']); } + public function testExcludeJoin() { + $contacts = Contact::get(FALSE) + ->addJoin('Address AS address', 'EXCLUDE', ['id', '=', 'address.contact_id'], ['address.location_type_id', '=', 1]) + ->addSelect('id') + ->execute()->column('id'); + $this->assertNotContains($this->getReference('test_contact_1')['id'], $contacts); + } + public function testJoinToTheSameTableTwice() { $cid1 = Contact::create(FALSE) ->addValue('first_name', 'Aaa') @@ -137,8 +145,8 @@ class FkJoinTest extends UnitTestCase { $contacts = Contact::get(FALSE) ->addSelect('id', 'first_name', 'any_email.email', 'any_email.location_type_id:name', 'any_email.is_primary', 'primary_email.email') ->setJoin([ - ['Email AS any_email', TRUE, NULL], - ['Email AS primary_email', FALSE, ['primary_email.is_primary', '=', TRUE]], + ['Email AS any_email', 'INNER', NULL], + ['Email AS primary_email', 'LEFT', ['primary_email.is_primary', '=', TRUE]], ]) ->addWhere('id', 'IN', [$cid1, $cid2, $cid3]) ->addOrderBy('any_email.id') -- 2.25.1