From 39deabd60329563c9808673f3c30d25883960f2e Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Fri, 28 Aug 2020 20:56:16 -0400 Subject: [PATCH] APIv4 - Add CONTAINS operator and add to search extension --- .../Generic/Traits/ArrayQueryActionTrait.php | 9 ++++ Civi/Api4/Query/Api4SelectQuery.php | 34 ++++++++++++--- Civi/Api4/Utils/CoreUtil.php | 4 +- ext/search/ang/search/crmSearch.component.js | 3 ++ .../ang/search/crmSearchValue.directive.js | 2 +- .../api/v4/Action/BasicActionsTest.php | 43 +++++++++++++++++++ .../api/v4/Action/PseudoconstantTest.php | 26 +++++++++++ 7 files changed, 114 insertions(+), 7 deletions(-) diff --git a/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php b/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php index 4ae6096c50..fd515b9a4b 100644 --- a/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php +++ b/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php @@ -158,6 +158,15 @@ trait ArrayQueryActionTrait { case 'NOT IN': return !in_array($value, $expected); + case 'CONTAINS': + if (is_array($value)) { + return in_array($expected, $value); + } + elseif (is_string($value) || is_numeric($value)) { + return strpos((string) $value, (string) $expected) !== FALSE; + } + return $value == $expected; + 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 6198b93603..e4f8ceb187 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -26,8 +26,8 @@ use Civi\Api4\Utils\SelectUtil; * Leaf operators are one of: * * * '=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=", - * * "NOT LIKE", 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', - * * 'IS NOT NULL', or 'IS NULL'. + * * 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', + * * 'IS NOT NULL', or 'IS NULL', 'CONTAINS'. */ class Api4SelectQuery { @@ -379,7 +379,8 @@ class Api4SelectQuery { $fieldAlias = $expr; // Attempt to format if this is a real field if (isset($this->apiFieldSpec[$expr])) { - FormattingUtil::formatInputValue($value, $expr, $this->apiFieldSpec[$expr]); + $field = $this->getField($expr); + FormattingUtil::formatInputValue($value, $expr, $field); } } // Expr references a non-field expression like a function; convert to alias @@ -392,7 +393,8 @@ class Api4SelectQuery { foreach ($this->selectAliases as $selectAlias => $selectExpr) { list($selectField) = explode(':', $selectAlias); if ($selectAlias === $selectExpr && $fieldName === $selectField && isset($this->apiFieldSpec[$fieldName])) { - FormattingUtil::formatInputValue($value, $expr, $this->apiFieldSpec[$fieldName]); + $field = $this->getField($fieldName); + FormattingUtil::formatInputValue($value, $expr, $field); $fieldAlias = $selectAlias; break; } @@ -415,7 +417,29 @@ class Api4SelectQuery { return sprintf('%s %s %s', $fieldAlias, $operator, $valExpr->render($this->apiFieldSpec)); } elseif ($fieldName) { - FormattingUtil::formatInputValue($value, $fieldName, $this->apiFieldSpec[$fieldName]); + $field = $this->getField($fieldName); + FormattingUtil::formatInputValue($value, $fieldName, $field); + } + } + + if ($operator === 'CONTAINS') { + switch ($field['serialize'] ?? NULL) { + case \CRM_Core_DAO::SERIALIZE_JSON: + $operator = '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'; + $value = '%' . \CRM_Core_DAO::VALUE_SEPARATOR . $value . \CRM_Core_DAO::VALUE_SEPARATOR . '%'; + break; + + default: + $operator = 'LIKE'; + $value = '%' . $value . '%'; + break; } } diff --git a/Civi/Api4/Utils/CoreUtil.php b/Civi/Api4/Utils/CoreUtil.php index 9bc7ec9b6a..b9090c97de 100644 --- a/Civi/Api4/Utils/CoreUtil.php +++ b/Civi/Api4/Utils/CoreUtil.php @@ -75,7 +75,9 @@ class CoreUtil { * @return string[] */ public static function getOperators() { - return \CRM_Core_DAO::acceptedSQLOperators(); + $operators = \CRM_Core_DAO::acceptedSQLOperators(); + $operators[] = 'CONTAINS'; + return $operators; } } diff --git a/ext/search/ang/search/crmSearch.component.js b/ext/search/ang/search/crmSearch.component.js index e2d8c564d1..51f2532bc4 100644 --- a/ext/search/ang/search/crmSearch.component.js +++ b/ext/search/ang/search/crmSearch.component.js @@ -340,6 +340,9 @@ else if (type === 'Money') { return CRM.formatMoney(value); } + if (_.isArray(value)) { + return value.join(', '); + } return value; } diff --git a/ext/search/ang/search/crmSearchValue.directive.js b/ext/search/ang/search/crmSearchValue.directive.js index 0fb1600884..ad93d2b878 100644 --- a/ext/search/ang/search/crmSearchValue.directive.js +++ b/ext/search/ang/search/crmSearchValue.directive.js @@ -39,7 +39,7 @@ if (_.includes(['=', '!=', '<>', '>', '>=', '<', '<='], op)) { $el.crmDatepicker({time: (field.input_attrs && field.input_attrs.time) || false}); } - } else if (_.includes(['=', '!=', '<>', 'IN', 'NOT IN'], op) && (field.fk_entity || field.options || dataType === 'Boolean')) { + } else if (_.includes(['=', '!=', '<>', 'IN', 'NOT IN', 'CONTAINS'], op) && (field.fk_entity || field.options || dataType === 'Boolean')) { if (field.options) { if (field.options === true) { $el.addClass('loading'); diff --git a/tests/phpunit/api/v4/Action/BasicActionsTest.php b/tests/phpunit/api/v4/Action/BasicActionsTest.php index 99d704a18f..8df90eb877 100644 --- a/tests/phpunit/api/v4/Action/BasicActionsTest.php +++ b/tests/phpunit/api/v4/Action/BasicActionsTest.php @@ -255,6 +255,49 @@ class BasicActionsTest extends UnitTestCase { $this->assertEquals(['shape', 'size', 'weight'], array_keys($result)); } + public function testContainsOperator() { + MockBasicEntity::delete()->addWhere('id', '>', 0)->execute(); + + $records = [ + ['group' => 'one', 'fruit:name' => ['apple', 'pear'], 'weight' => 11], + ['group' => 'two', 'fruit:name' => ['pear', 'banana'], 'weight' => 12], + ]; + MockBasicEntity::save()->setRecords($records)->execute(); + + $result = MockBasicEntity::get() + ->addWhere('fruit:name', 'CONTAINS', 'apple') + ->execute(); + $this->assertCount(1, $result); + $this->assertEquals('one', $result->first()['group']); + + $result = MockBasicEntity::get() + ->addWhere('fruit:name', 'CONTAINS', 'pear') + ->execute(); + $this->assertCount(2, $result); + + $result = MockBasicEntity::get() + ->addWhere('group', 'CONTAINS', 'o') + ->execute(); + $this->assertCount(2, $result); + + $result = MockBasicEntity::get() + ->addWhere('weight', 'CONTAINS', 1) + ->execute(); + $this->assertCount(2, $result); + + $result = MockBasicEntity::get() + ->addWhere('fruit:label', 'CONTAINS', 'Banana') + ->execute(); + $this->assertCount(1, $result); + $this->assertEquals('two', $result->first()['group']); + + $result = MockBasicEntity::get() + ->addWhere('weight', 'CONTAINS', 2) + ->execute(); + $this->assertCount(1, $result); + $this->assertEquals('two', $result->first()['group']); + } + public function testPseudoconstantMatch() { MockBasicEntity::delete()->addWhere('id', '>', 0)->execute(); diff --git a/tests/phpunit/api/v4/Action/PseudoconstantTest.php b/tests/phpunit/api/v4/Action/PseudoconstantTest.php index 9f7e363941..248073ab8d 100644 --- a/tests/phpunit/api/v4/Action/PseudoconstantTest.php +++ b/tests/phpunit/api/v4/Action/PseudoconstantTest.php @@ -27,6 +27,7 @@ use Civi\Api4\CustomGroup; use Civi\Api4\Email; use Civi\Api4\EntityTag; use Civi\Api4\OptionValue; +use Civi\Api4\Participant; use Civi\Api4\Tag; /** @@ -273,4 +274,29 @@ class PseudoconstantTest extends BaseCustomValueTest { $this->assertEquals($tag, $options[$tag]['label']); } + public function testParticipantRole() { + $event = $this->createEntity(['type' => 'Event']); + $contact = $this->createEntity(['type' => 'Individual']); + $participant = Participant::create() + ->addValue('contact_id', $contact['id']) + ->addValue('event_id', $event['id']) + ->addValue('role_id:label', ['Attendee', 'Volunteer']) + ->execute()->first(); + + $search1 = Participant::get() + ->addSelect('role_id', 'role_id:label') + ->addWhere('role_id:label', 'CONTAINS', 'Volunteer') + ->addOrderBy('id') + ->execute()->last(); + + $this->assertEquals(['Attendee', 'Volunteer'], $search1['role_id:label']); + $this->assertEquals(['1', '2'], $search1['role_id']); + + $search2 = Participant::get() + ->addWhere('role_id:label', 'CONTAINS', 'Host') + ->execute()->indexBy('id'); + + $this->assertArrayNotHasKey($participant['id'], (array) $search2); + } + } -- 2.25.1