From c973f3d7ff6932add79dc672a0a93c0961b48ced Mon Sep 17 00:00:00 2001 From: larssandergreen Date: Sun, 28 May 2023 13:16:38 -0600 Subject: [PATCH] Add NOT CONTAINS to API and SK --- .../Generic/Traits/ArrayQueryActionTrait.php | 7 ++-- Civi/Api4/Query/Api4SelectQuery.php | 20 +++++------ Civi/Api4/Utils/CoreUtil.php | 1 + .../elements/afGuiField.component.js | 1 + ext/search_kit/Civi/Search/Admin.php | 1 + .../crmSearchCondition.component.js | 2 +- .../api/v4/Action/BasicActionsTest.php | 35 ++++++++++++++++++- 7 files changed, 52 insertions(+), 15 deletions(-) diff --git a/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php b/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php index dc33d11885..e1763f4c02 100644 --- a/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php +++ b/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php @@ -174,14 +174,15 @@ trait ArrayQueryActionTrait { return !in_array($value, $expected); case 'CONTAINS': + case 'NOT CONTAINS': if (is_array($value)) { - return in_array($expected, $value); + return in_array($expected, $value) == ($operator == 'CONTAINS'); } elseif (is_string($value) || is_numeric($value)) { // Lowercase check if string contains string - return strpos(strtolower((string) $value), strtolower((string) $expected)) !== FALSE; + return (strpos(strtolower((string) $value), strtolower((string) $expected)) !== FALSE) == ($operator == 'CONTAINS'); } - return $value == $expected; + return ($value == $expected) == ($operator == 'CONTAINS'); default: throw new NotImplementedException("Unsupported operator: '$operator' cannot be used with array data"); diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php index 7c93362e28..29fbd18fb8 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -29,8 +29,8 @@ use Civi\Api4\Utils\SelectUtil; * * * '=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=", * * 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', - * * 'IS NOT NULL', 'IS NULL', 'CONTAINS', 'IS EMPTY', 'IS NOT EMPTY', - * * 'REGEXP', 'NOT REGEXP'. + * * 'IS NOT NULL', 'IS NULL', 'CONTAINS', 'NOT CONTAINS', + * * 'IS EMPTY', 'IS NOT EMPTY', 'REGEXP', 'NOT REGEXP'. */ class Api4SelectQuery { @@ -593,27 +593,27 @@ class Api4SelectQuery { return $sql ? implode(' AND ', $sql) : NULL; } - // The CONTAINS operator matches a substring for strings. For arrays & serialized fields, - // it only matches a complete (not partial) string within the array. - if ($operator === 'CONTAINS') { + // The CONTAINS and NOT CONTAINS operators match a substring for strings. + // For arrays & serialized fields, they only match a complete (not partial) string within the array. + if ($operator === 'CONTAINS' || $operator === 'NOT CONTAINS') { $sep = \CRM_Core_DAO::VALUE_SEPARATOR; switch ($field['serialize'] ?? NULL) { case \CRM_Core_DAO::SERIALIZE_JSON: - $operator = 'LIKE'; + $operator = ($operator === 'CONTAINS') ? 'LIKE' : 'NOT LIKE'; $value = '%"' . $value . '"%'; // FIXME: Use this instead of the above hack once MIN_INSTALL_MYSQL_VER is bumped to 5.7. // return sprintf('JSON_SEARCH(%s, "one", "%s") IS NOT NULL', $fieldAlias, \CRM_Core_DAO::escapeString($value)); break; case \CRM_Core_DAO::SERIALIZE_SEPARATOR_BOOKEND: - $operator = 'LIKE'; + $operator = ($operator === 'CONTAINS') ? 'LIKE' : 'NOT LIKE'; // This is easy to query because the string is always bookended by separators. $value = '%' . $sep . $value . $sep . '%'; break; case \CRM_Core_DAO::SERIALIZE_SEPARATOR_TRIMMED: - $operator = 'REGEXP'; + $operator = ($operator === 'CONTAINS') ? 'REGEXP' : 'NOT REGEXP'; // This is harder to query because there's no bookend. // Use regex to match string within separators or content boundary // Escaping regex per https://stackoverflow.com/questions/3782379/whats-the-best-way-to-escape-user-input-for-regular-expressions-in-mysql @@ -621,14 +621,14 @@ class Api4SelectQuery { break; case \CRM_Core_DAO::SERIALIZE_COMMA: - $operator = 'REGEXP'; + $operator = ($operator === 'CONTAINS') ? 'REGEXP' : 'NOT REGEXP'; // Match string within commas or content boundary // Escaping regex per https://stackoverflow.com/questions/3782379/whats-the-best-way-to-escape-user-input-for-regular-expressions-in-mysql $value = '(^|,)' . preg_quote($value, '&') . '(,|$)'; break; default: - $operator = 'LIKE'; + $operator = ($operator === 'CONTAINS') ? 'LIKE' : 'NOT LIKE'; $value = '%' . $value . '%'; break; } diff --git a/Civi/Api4/Utils/CoreUtil.php b/Civi/Api4/Utils/CoreUtil.php index 24c4fb62b1..918e2d56d1 100644 --- a/Civi/Api4/Utils/CoreUtil.php +++ b/Civi/Api4/Utils/CoreUtil.php @@ -113,6 +113,7 @@ class CoreUtil { public static function getOperators() { $operators = \CRM_Core_DAO::acceptedSQLOperators(); $operators[] = 'CONTAINS'; + $operators[] = 'NOT CONTAINS'; $operators[] = 'IS EMPTY'; $operators[] = 'IS NOT EMPTY'; $operators[] = 'REGEXP'; diff --git a/ext/afform/admin/ang/afGuiEditor/elements/afGuiField.component.js b/ext/afform/admin/ang/afGuiEditor/elements/afGuiField.component.js index d5fa1149a1..087b27832e 100644 --- a/ext/afform/admin/ang/afGuiEditor/elements/afGuiField.component.js +++ b/ext/afform/admin/ang/afGuiEditor/elements/afGuiField.component.js @@ -353,6 +353,7 @@ '>=': '≥', '<=': '≤', 'CONTAINS': ts('Contains'), + 'NOT CONTAINS': ts("Doesn't Contain"), 'IN': ts('Is One Of'), 'NOT IN': ts('Not One Of'), 'LIKE': ts('Is Like'), diff --git a/ext/search_kit/Civi/Search/Admin.php b/ext/search_kit/Civi/Search/Admin.php index 4bd9114294..4fedccb7f2 100644 --- a/ext/search_kit/Civi/Search/Admin.php +++ b/ext/search_kit/Civi/Search/Admin.php @@ -87,6 +87,7 @@ class Admin { '>=' => '≥', '<=' => '≤', 'CONTAINS' => E::ts('Contains'), + 'NOT CONTAINS' => E::ts("Doesn't Contain"), 'IN' => E::ts('Is One Of'), 'NOT IN' => E::ts('Not One Of'), 'LIKE' => E::ts('Is Like'), diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchCondition.component.js b/ext/search_kit/ang/crmSearchAdmin/crmSearchCondition.component.js index 44f9a7fa32..aa90a53f26 100644 --- a/ext/search_kit/ang/crmSearchAdmin/crmSearchCondition.component.js +++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchCondition.component.js @@ -65,7 +65,7 @@ allowedOps = ['=', '!=', '<', '>', '<=', '>=', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', 'IS EMPTY', 'IS NOT EMPTY']; } if (!allowedOps && (field.data_type === 'Array' || field.serialize)) { - allowedOps = ['CONTAINS', 'IS EMPTY', 'IS NOT EMPTY']; + allowedOps = ['CONTAINS', 'NOT CONTAINS', 'IS EMPTY', 'IS NOT EMPTY']; } if (!allowedOps) { return CRM.crmSearchAdmin.operators; diff --git a/tests/phpunit/api/v4/Action/BasicActionsTest.php b/tests/phpunit/api/v4/Action/BasicActionsTest.php index fad0c8f079..de193ed1a4 100644 --- a/tests/phpunit/api/v4/Action/BasicActionsTest.php +++ b/tests/phpunit/api/v4/Action/BasicActionsTest.php @@ -337,7 +337,7 @@ class BasicActionsTest extends Api4TestBase implements HookInterface, Transactio $this->assertArrayHasKey($records[2]['identifier'], (array) $result); } - public function testContainsOperator() { + public function testContainsOperators() { $records = [ ['group' => 'one', 'fruit:name' => ['apple', 'pear'], 'weight' => 11], ['group' => 'two', 'fruit:name' => ['pear', 'banana'], 'weight' => 12], @@ -350,32 +350,65 @@ class BasicActionsTest extends Api4TestBase implements HookInterface, Transactio $this->assertCount(1, $result); $this->assertEquals('one', $result->first()['group']); + $result = MockBasicEntity::get() + ->addWhere('fruit:name', 'NOT CONTAINS', 'apple') + ->execute(); + $this->assertCount(1, $result); + $this->assertEquals('two', $result->first()['group']); + $result = MockBasicEntity::get() ->addWhere('fruit:name', 'CONTAINS', 'pear') ->execute(); $this->assertCount(2, $result); + $result = MockBasicEntity::get() + ->addWhere('fruit:name', 'NOT CONTAINS', 'pear') + ->execute(); + $this->assertCount(0, $result); + $result = MockBasicEntity::get() ->addWhere('group', 'CONTAINS', 'o') ->execute(); $this->assertCount(2, $result); + $result = MockBasicEntity::get() + ->addWhere('group', 'NOT CONTAINS', 'w') + ->execute(); + $this->assertCount(1, $result); + $result = MockBasicEntity::get() ->addWhere('weight', 'CONTAINS', 1) ->execute(); $this->assertCount(2, $result); + $result = MockBasicEntity::get() + ->addWhere('weight', 'NOT CONTAINS', 2) + ->execute(); + $this->assertCount(1, $result); + $result = MockBasicEntity::get() ->addWhere('fruit:label', 'CONTAINS', 'Banana') ->execute(); $this->assertCount(1, $result); $this->assertEquals('two', $result->first()['group']); + $result = MockBasicEntity::get() + ->addWhere('fruit:label', 'NOT CONTAINS', 'Banana') + ->execute(); + $this->assertCount(1, $result); + $this->assertEquals('one', $result->first()['group']); + $result = MockBasicEntity::get() ->addWhere('weight', 'CONTAINS', 2) ->execute(); $this->assertCount(1, $result); $this->assertEquals('two', $result->first()['group']); + + $result = MockBasicEntity::get() + ->addWhere('weight', 'NOT CONTAINS', 2) + ->execute(); + $this->assertCount(1, $result); + $this->assertEquals('one', $result->first()['group']); } public function testRegexpOperators() { -- 2.25.1