From b5599ad61a288ac74e567cb910fb3b6d4bfc3c9b Mon Sep 17 00:00:00 2001 From: Patrick Figel Date: Mon, 6 Sep 2021 12:27:12 +0200 Subject: [PATCH] dev/core#2793 - Add REGEXP/NOT REGEXP SQL operators in API4 This adds the REGEXP and NOT REGEXP operators to API4 and SearchKit. --- .../Generic/Traits/ArrayQueryActionTrait.php | 5 +++ Civi/Api4/Query/Api4SelectQuery.php | 8 ++++- Civi/Api4/Utils/CoreUtil.php | 2 ++ ext/search_kit/Civi/Search/Admin.php | 2 ++ .../api/v4/Action/BasicActionsTest.php | 22 +++++++++++++ .../phpunit/api/v4/Action/ContactGetTest.php | 31 +++++++++++++++++++ .../api/v4/Action/GetFromArrayTest.php | 10 ++++++ 7 files changed, 79 insertions(+), 1 deletion(-) diff --git a/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php b/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php index f11966f227..467fd6054c 100644 --- a/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php +++ b/Civi/Api4/Generic/Traits/ArrayQueryActionTrait.php @@ -149,6 +149,11 @@ trait ArrayQueryActionTrait { $pattern = '/^' . str_replace('%', '.*', preg_quote($expected, '/')) . '$/i'; return !preg_match($pattern, $value) == ($operator != 'LIKE'); + case 'REGEXP': + case 'NOT REGEXP': + $pattern = '/' . str_replace('/', '\\/', $expected) . '/'; + return !preg_match($pattern, $value) == ($operator != 'REGEXP'); + case 'IN': return in_array($value, $expected); diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php index 119abe49a9..b2502fe011 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -28,7 +28,8 @@ use Civi\Api4\Utils\SelectUtil; * * * '=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=", * * 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', - * * 'IS NOT NULL', or 'IS NULL', 'CONTAINS'. + * * 'IS NOT NULL', 'IS NULL', 'CONTAINS', 'IS EMPTY', 'IS NOT EMPTY', + * * 'REGEXP', 'NOT REGEXP'. */ class Api4SelectQuery { @@ -575,6 +576,11 @@ class Api4SelectQuery { return "($fieldAlias $isEmptyClause $fieldAlias $operator)"; } } + + if ($operator == 'REGEXP' || $operator == 'NOT REGEXP') { + return sprintf('%s %s "%s"', $fieldAlias, $operator, \CRM_Core_DAO::escapeString($value)); + } + if (is_bool($value)) { $value = (int) $value; } diff --git a/Civi/Api4/Utils/CoreUtil.php b/Civi/Api4/Utils/CoreUtil.php index efb11f48e3..9643585769 100644 --- a/Civi/Api4/Utils/CoreUtil.php +++ b/Civi/Api4/Utils/CoreUtil.php @@ -114,6 +114,8 @@ class CoreUtil { $operators[] = 'CONTAINS'; $operators[] = 'IS EMPTY'; $operators[] = 'IS NOT EMPTY'; + $operators[] = 'REGEXP'; + $operators[] = 'NOT REGEXP'; return $operators; } diff --git a/ext/search_kit/Civi/Search/Admin.php b/ext/search_kit/Civi/Search/Admin.php index 963b02c44d..e75f06e330 100644 --- a/ext/search_kit/Civi/Search/Admin.php +++ b/ext/search_kit/Civi/Search/Admin.php @@ -53,7 +53,9 @@ class Admin { 'IN' => E::ts('Is One Of'), 'NOT IN' => E::ts('Not One Of'), 'LIKE' => E::ts('Is Like'), + 'REGEXP' => E::ts('Matches Regexp'), 'NOT LIKE' => E::ts('Not Like'), + 'NOT REGEXP' => E::ts('Not Regexp'), 'BETWEEN' => E::ts('Is Between'), 'NOT BETWEEN' => E::ts('Not Between'), 'IS EMPTY' => E::ts('Is Empty'), diff --git a/tests/phpunit/api/v4/Action/BasicActionsTest.php b/tests/phpunit/api/v4/Action/BasicActionsTest.php index 9ff1b3bd11..4dae4f5f9d 100644 --- a/tests/phpunit/api/v4/Action/BasicActionsTest.php +++ b/tests/phpunit/api/v4/Action/BasicActionsTest.php @@ -356,6 +356,28 @@ class BasicActionsTest extends UnitTestCase { $this->assertEquals('two', $result->first()['group']); } + public function testRegexpOperators() { + $records = [ + ['color' => 'red'], + ['color' => 'blue'], + ['color' => 'brown'], + ]; + $this->replaceRecords($records); + + $result = MockBasicEntity::get() + ->addWhere('color', 'REGEXP', '^b') + ->execute(); + $this->assertCount(2, $result); + $this->assertEquals('blue', $result[0]['color']); + $this->assertEquals('brown', $result[1]['color']); + + $result = MockBasicEntity::get() + ->addWhere('color', 'NOT REGEXP', '^b') + ->execute(); + $this->assertCount(1, $result); + $this->assertEquals('red', $result[0]['color']); + } + public function testPseudoconstantMatch() { $records = [ ['group:label' => 'First', 'shape' => 'round', 'fruit:name' => 'banana'], diff --git a/tests/phpunit/api/v4/Action/ContactGetTest.php b/tests/phpunit/api/v4/Action/ContactGetTest.php index 2e5075ea03..3eb43afd47 100644 --- a/tests/phpunit/api/v4/Action/ContactGetTest.php +++ b/tests/phpunit/api/v4/Action/ContactGetTest.php @@ -197,6 +197,37 @@ class ContactGetTest extends \api\v4\UnitTestCase { $this->assertArrayHasKey($jan['id'], (array) $result); } + public function testRegexpOperators() { + $last_name = uniqid(__FUNCTION__); + + $alice = Contact::create() + ->setValues(['first_name' => 'Alice', 'last_name' => $last_name]) + ->execute()->first(); + + $alex = Contact::create() + ->setValues(['first_name' => 'Alex', 'last_name' => $last_name]) + ->execute()->first(); + + $jane = Contact::create() + ->setValues(['first_name' => 'Jane', 'last_name' => $last_name]) + ->execute()->first(); + + $result = Contact::get(FALSE) + ->addWhere('last_name', '=', $last_name) + ->addWhere('first_name', 'REGEXP', '^A') + ->execute()->indexBy('id'); + $this->assertCount(2, $result); + $this->assertArrayHasKey($alice['id'], (array) $result); + $this->assertArrayHasKey($alex['id'], (array) $result); + + $result = Contact::get(FALSE) + ->addWhere('last_name', '=', $last_name) + ->addWhere('first_name', 'NOT REGEXP', '^A') + ->execute()->indexBy('id'); + $this->assertCount(1, $result); + $this->assertArrayHasKey($jane['id'], (array) $result); + } + public function testGetRelatedWithSubType() { $org = Contact::create(FALSE) ->addValue('contact_type', 'Organization') diff --git a/tests/phpunit/api/v4/Action/GetFromArrayTest.php b/tests/phpunit/api/v4/Action/GetFromArrayTest.php index 9663e8bafb..c41b100203 100644 --- a/tests/phpunit/api/v4/Action/GetFromArrayTest.php +++ b/tests/phpunit/api/v4/Action/GetFromArrayTest.php @@ -111,6 +111,16 @@ class GetFromArrayTest extends UnitTestCase { ->execute(); $this->assertEquals([1, 3], array_column((array) $result, 'field1')); + $result = MockArrayEntity::get() + ->addWhere('field2', 'REGEXP', '(zebra|yac[a-z]|something/else)') + ->execute(); + $this->assertEquals([1, 2], array_column((array) $result, 'field1')); + + $result = MockArrayEntity::get() + ->addWhere('field2', 'NOT REGEXP', '^[x|y|z]') + ->execute(); + $this->assertEquals([4, 5], array_column((array) $result, 'field1')); + $result = MockArrayEntity::get() ->addWhere('field3', 'IS NULL') ->execute(); -- 2.25.1