From b69cbf949c271de3abd8272f26b0448f9b75eb09 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Wed, 13 Apr 2022 22:44:47 -0400 Subject: [PATCH] SearchKit - Support operators, custom fields & multiple fields in a searchSegment clause. --- CRM/Utils/API/HTMLInputCoder.php | 2 + Civi/Api4/Query/Api4SelectQuery.php | 2 +- .../CRM/Search/DAO/SearchSegment.php | 29 +-- ext/search_kit/CRM/Search/Upgrader.php | 1 - .../SearchSegmentExtraFieldProvider.php | 19 +- ext/search_kit/sql/auto_install.sql | 1 - .../v4/SearchSegment/SearchSegmentTest.php | 215 +++++++++++++++++- .../xml/schema/CRM/Search/SearchSegment.xml | 12 - 8 files changed, 221 insertions(+), 60 deletions(-) diff --git a/CRM/Utils/API/HTMLInputCoder.php b/CRM/Utils/API/HTMLInputCoder.php index ef0feed724..928a4ec7d6 100644 --- a/CRM/Utils/API/HTMLInputCoder.php +++ b/CRM/Utils/API/HTMLInputCoder.php @@ -113,6 +113,8 @@ class CRM_Utils_API_HTMLInputCoder extends CRM_Utils_API_AbstractFieldCoder { 'api_params', // SearchDisplay entity 'settings', + // SearchSegment items + 'items', ]; $custom = CRM_Core_DAO::executeQuery('SELECT id FROM civicrm_custom_field WHERE html_type = "RichTextEditor"'); while ($custom->fetch()) { diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php index c1cd9cbc35..146ce1dcc0 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -441,7 +441,7 @@ class Api4SelectQuery { * @throws \API_Exception * @throws \Exception */ - protected function composeClause(array $clause, string $type, int $depth) { + public function composeClause(array $clause, string $type, int $depth) { $field = NULL; // Pad array for unary operators [$expr, $operator, $value] = array_pad($clause, 3, NULL); diff --git a/ext/search_kit/CRM/Search/DAO/SearchSegment.php b/ext/search_kit/CRM/Search/DAO/SearchSegment.php index b914788335..1e312b2a9e 100644 --- a/ext/search_kit/CRM/Search/DAO/SearchSegment.php +++ b/ext/search_kit/CRM/Search/DAO/SearchSegment.php @@ -6,7 +6,7 @@ * * Generated from org.civicrm.search_kit/xml/schema/CRM/Search/SearchSegment.xml * DO NOT EDIT. Generated by CRM_Core_CodeGen - * (GenCodeChecksum:c8d5ecf2d873eb419f692b8b66f3fc3d) + * (GenCodeChecksum:b347dd437336ecd5880d5924b879d218) */ use CRM_Search_ExtensionUtil as E; @@ -90,15 +90,6 @@ class CRM_Search_DAO_SearchSegment extends CRM_Core_DAO { */ public $entity_name; - /** - * Field for which this set is used (may include pseudoconstant suffix). - * - * @var string - * (SQL type: varchar(255)) - * Note that values will be retrieved from the database as a string. - */ - public $field_name; - /** * All items in set * @@ -224,24 +215,6 @@ class CRM_Search_DAO_SearchSegment extends CRM_Core_DAO { ], 'add' => NULL, ], - 'field_name' => [ - 'name' => 'field_name', - 'type' => CRM_Utils_Type::T_STRING, - 'title' => E::ts('Field'), - 'description' => E::ts('Field for which this set is used (may include pseudoconstant suffix).'), - 'required' => TRUE, - 'maxlength' => 255, - 'size' => CRM_Utils_Type::HUGE, - 'where' => 'civicrm_search_segment.field_name', - 'table_name' => 'civicrm_search_segment', - 'entity' => 'SearchSegment', - 'bao' => 'CRM_Search_DAO_SearchSegment', - 'localizable' => 0, - 'html' => [ - 'type' => 'Text', - ], - 'add' => NULL, - ], 'items' => [ 'name' => 'items', 'type' => CRM_Utils_Type::T_TEXT, diff --git a/ext/search_kit/CRM/Search/Upgrader.php b/ext/search_kit/CRM/Search/Upgrader.php index 9b737fbd48..9d5f43a5c6 100644 --- a/ext/search_kit/CRM/Search/Upgrader.php +++ b/ext/search_kit/CRM/Search/Upgrader.php @@ -203,7 +203,6 @@ CREATE TABLE `civicrm_search_segment` ( `label` varchar(255) NOT NULL COMMENT 'Label for identifying search segment (will appear as name of calculated field)', `description` varchar(255) COMMENT 'Description will appear when selecting SearchSegment in the fields dropdown.', `entity_name` varchar(255) NOT NULL COMMENT 'Entity for which this set is used.', - `field_name` varchar(255) NOT NULL COMMENT 'Field for which this set is used (may include pseudoconstant suffix).', `items` text COMMENT 'All items in set', PRIMARY KEY (`id`), UNIQUE INDEX `UI_name`(name) diff --git a/ext/search_kit/Civi/Api4/Service/Spec/Provider/SearchSegmentExtraFieldProvider.php b/ext/search_kit/Civi/Api4/Service/Spec/Provider/SearchSegmentExtraFieldProvider.php index e2e18fdabb..fc4c07a50a 100644 --- a/ext/search_kit/Civi/Api4/Service/Spec/Provider/SearchSegmentExtraFieldProvider.php +++ b/ext/search_kit/Civi/Api4/Service/Spec/Provider/SearchSegmentExtraFieldProvider.php @@ -12,6 +12,7 @@ namespace Civi\Api4\Service\Spec\Provider; +use Civi\Api4\Query\Api4SelectQuery; use Civi\Api4\SearchSegment; use Civi\Api4\Service\Spec\FieldSpec; use Civi\Api4\Service\Spec\RequestSpec; @@ -25,7 +26,7 @@ class SearchSegmentExtraFieldProvider implements Generic\SpecProviderInterface { foreach (self::getSets($spec->getEntity()) as $fullName => $set) { $field = new FieldSpec($fullName, $spec->getEntity()); $field->setLabel($set['label']); - $field->setColumnName($set['field_name']); + $field->setColumnName('id'); $field->setOptions(array_column($set['items'], 'label')); $field->setSuffixes(['label']); $field->setSqlRenderer([__CLASS__, 'renderSql']); @@ -65,19 +66,21 @@ class SearchSegmentExtraFieldProvider implements Generic\SpecProviderInterface { * Generates the sql case statement with a clause for each item. * * @param array $field + * @param Civi\Api4\Query\Api4SelectQuery $query * @return string */ - public static function renderSql(array $field): string { + public static function renderSql(array $field, Api4SelectQuery $query): string { $set = self::getSets($field['entity'])[$field['name']]; - $sqlName = $field['sql_name']; + + // Field prefix to use if entity comes from a join + $prefix = ($field['explicit_join'] ? $field['explicit_join'] . '.' : '') . ($field['implicit_join'] ? $field['implicit_join'] . '.' : ''); $cases = []; foreach ($set['items'] as $index => $item) { $conditions = []; - if (isset($item['min'])) { - $conditions[] = $sqlName . ' >= ' . (float) $item['min']; - } - if (isset($item['max'])) { - $conditions[] = $sqlName . ' < ' . (float) $item['max']; + foreach ($item['when'] ?? [] as $clause) { + // Add field prefix + $clause[0] = $prefix . $clause[0]; + $conditions[] = $query->composeClause($clause, 'WHERE', 0); } // If no conditions, this is the ELSE clause if (!$conditions) { diff --git a/ext/search_kit/sql/auto_install.sql b/ext/search_kit/sql/auto_install.sql index d1b9ef9e3b..4f5d09fd84 100644 --- a/ext/search_kit/sql/auto_install.sql +++ b/ext/search_kit/sql/auto_install.sql @@ -61,7 +61,6 @@ CREATE TABLE `civicrm_search_segment` ( `label` varchar(255) NOT NULL COMMENT 'Label for identifying search segment (will appear as name of calculated field)', `description` varchar(255) COMMENT 'Description will appear when selecting SearchSegment in the fields dropdown.', `entity_name` varchar(255) NOT NULL COMMENT 'Entity for which this set is used.', - `field_name` varchar(255) NOT NULL COMMENT 'Field for which this set is used (may include pseudoconstant suffix).', `items` text COMMENT 'All items in set', PRIMARY KEY (`id`), UNIQUE INDEX `UI_name`(name) diff --git a/ext/search_kit/tests/phpunit/api/v4/SearchSegment/SearchSegmentTest.php b/ext/search_kit/tests/phpunit/api/v4/SearchSegment/SearchSegmentTest.php index 6976123d1b..01a815ac60 100644 --- a/ext/search_kit/tests/phpunit/api/v4/SearchSegment/SearchSegmentTest.php +++ b/ext/search_kit/tests/phpunit/api/v4/SearchSegment/SearchSegmentTest.php @@ -1,16 +1,19 @@ apply(); } + public function tearDown(): void { + foreach (['Activity', 'SearchSegment', 'CustomGroup', 'Contact'] as $entity) { + civicrm_api4($entity, 'delete', [ + 'checkPermissions' => FALSE, + 'where' => [['id', '>', '0']], + ]); + } + parent::tearDown(); + } + /** * Test running a searchDisplay with a numeric range segment. */ @@ -44,23 +57,19 @@ class SearchSegmentTest extends \PHPUnit\Framework\TestCase implements HeadlessI SearchSegment::create(FALSE) ->addValue('label', 'Giving Tier') ->addValue('entity_name', 'Contribution') - ->addValue('field_name', 'total_amount') ->addValue('description', 'Tiers by donation amount') ->addValue('items', [ - // Only a max means no minimum [ 'label' => 'Low ball', - 'max' => 10, + 'when' => [['total_amount', '<', 10]], ], [ 'label' => 'Minor league', - 'min' => 10, - 'max' => 25, + 'when' => [['total_amount', '>=', 10], ['total_amount', '<', 25]], ], [ 'label' => 'Major league', - 'min' => 25, - 'max' => 40, + 'when' => [['total_amount', '>=', 25], ['total_amount', '<', 40]], ], // No conditions makes this the ELSE clause [ @@ -121,4 +130,192 @@ class SearchSegmentTest extends \PHPUnit\Framework\TestCase implements HeadlessI $this->assertEquals(2, $result[3]['data']['COUNT_total_amount']); } + /** + * Tests a segment based on custom data using a bridge join + */ + public function testSegmentCustomField() { + CustomGroup::create(FALSE) + ->addValue('title', 'TestActivitySegment') + ->addValue('extends', 'Activity') + ->execute(); + CustomField::create(FALSE) + ->addValue('label', 'ActColor') + ->addValue('custom_group_id.name', 'TestActivitySegment') + ->addValue('html_type', 'Select') + ->addValue('option_values', ['r' => 'Red', 'g' => 'Green', 'b' => 'Blue', 'k' => 'Black']) + ->execute(); + $cid = Contact::create(FALSE)->execute()->single()['id']; + + $sampleData = [ + ['TestActivitySegment.ActColor' => 'r'], + ['TestActivitySegment.ActColor' => 'g'], + ['TestActivitySegment.ActColor' => 'b'], + ['TestActivitySegment.ActColor' => 'k'], + ['TestActivitySegment.ActColor' => 'k'], + [], + ]; + Activity::save(FALSE) + ->addDefault('source_contact_id', $cid) + ->addDefault('activity_type_id:name', 'Meeting') + ->addDefault('activity_date_time', 'now') + ->setRecords($sampleData)->execute(); + + SearchSegment::create(FALSE) + ->addValue('label', 'Activity Cluster') + ->addValue('entity_name', 'Activity') + ->addValue('description', 'Clusters based on activity custom field') + ->addValue('items', [ + [ + 'label' => 'Primary Color', + 'when' => [['TestActivitySegment.ActColor:label', 'IN', ['Red', 'Blue']]], + ], + [ + 'label' => 'Secondary Color', + 'when' => [['TestActivitySegment.ActColor:label', 'NOT IN', ['Red', 'Blue', 'Black']]], + ], + [ + 'label' => 'Not a Color!', + ], + ]) + ->execute(); + + $getField = Activity::getFields(FALSE) + ->addWhere('name', '=', 'segment_Activity_Cluster') + ->setLoadOptions(TRUE) + ->execute()->single(); + $this->assertEquals('Activity Cluster', $getField['label']); + $this->assertEquals(['Primary Color', 'Secondary Color', 'Not a Color!'], $getField['options']); + + $params = [ + 'checkPermissions' => FALSE, + 'return' => 'page:1', + 'savedSearch' => [ + 'api_entity' => 'Contact', + 'api_params' => [ + 'version' => 4, + 'select' => [ + 'Activity_01.segment_Activity_Cluster:label', + 'COUNT(Activity_01.id) AS COUNT_id', + ], + 'where' => [['id', '=', $cid]], + 'groupBy' => [ + 'Activity_01.segment_Activity_Cluster', + ], + 'join' => [ + [ + 'Activity AS Activity_01', + 'LEFT', + 'ActivityContact', + ['id', '=', 'Activity_01.contact_id'], + ['Activity_01.record_type_id:name', '=', '"Activity Source"'], + ], + ], + 'having' => [], + ], + ], + 'sort' => [['Activity_01.segment_Activity_Cluster:label', 'ASC']], + ]; + + $result = civicrm_api4('SearchDisplay', 'run', $params); + $this->assertCount(3, $result); + + $this->assertEquals('Not a Color!', $result[0]['columns'][0]['val']); + $this->assertEquals(3, $result[0]['data']['COUNT_id']); + + $this->assertEquals('Primary Color', $result[1]['columns'][0]['val']); + $this->assertEquals(2, $result[1]['data']['COUNT_id']); + + $this->assertEquals('Secondary Color', $result[2]['columns'][0]['val']); + $this->assertEquals(1, $result[2]['data']['COUNT_id']); + } + + /** + * Uses a calc field as the basis for a segment, + * and fetches it via related contact join, just to test that extra bit of complexity + */ + public function testSegmentCalcField() { + $cid = Contact::create(FALSE)->execute()->single()['id']; + $sampleData = [ + ['birth_date' => 'now - 1 year - 1 month'], + ['birth_date' => 'now - 12 year - 1 month'], + ['birth_date' => 'now - 13 year - 1 month'], + ['birth_date' => 'now - 30 year - 1 month'], + ['birth_date' => 'now - 33 year - 1 month'], + [], + ]; + Contact::save(FALSE) + ->setRecords($sampleData) + ->addChain('rel', Relationship::create() + ->addValue('relationship_type_id', 1) + ->addValue('contact_id_b', $cid) + ->addValue('contact_id_a', '$id') + ) + ->execute(); + + SearchSegment::create(FALSE) + ->addValue('label', 'Age Range') + ->addValue('entity_name', 'Contact') + ->addValue('description', 'Babies, Children, Adults') + ->addValue('items', [ + [ + 'label' => 'Baby', + 'when' => [['age_years', '<', 2]], + ], + [ + 'label' => 'Child', + 'when' => [['age_years', '>=', 2], ['age_years', '<', 18]], + ], + [ + 'label' => 'Adult', + 'when' => [['age_years', '>=', 18]], + ], + ]) + ->execute(); + + $params = [ + 'checkPermissions' => FALSE, + 'return' => 'page:1', + 'savedSearch' => [ + 'api_entity' => 'Contact', + 'api_params' => [ + 'version' => 4, + 'select' => [ + 'Related_Contact.segment_Age_Range:label', + 'COUNT(Related_Contact.id) AS COUNT_id', + ], + 'where' => [['id', '=', $cid]], + 'groupBy' => [ + 'Related_Contact.segment_Age_Range', + ], + 'join' => [ + [ + 'Contact AS Related_Contact', + 'INNER', + 'RelationshipCache', + ['id', '=', 'Related_Contact.far_contact_id'], + ['Related_Contact.near_relation:name', '=', '"Child of"'], + ], + ], + 'having' => [], + ], + ], + 'sort' => [['Related_Contact.segment_Age_Range:label', 'DESC']], + ]; + + $result = civicrm_api4('SearchDisplay', 'run', $params); + $this->assertCount(4, $result); + + $this->assertEquals('Child', $result[0]['columns'][0]['val']); + $this->assertEquals(2, $result[0]['data']['COUNT_id']); + + $this->assertEquals('Baby', $result[1]['columns'][0]['val']); + $this->assertEquals(1, $result[1]['data']['COUNT_id']); + + $this->assertEquals('Adult', $result[2]['columns'][0]['val']); + $this->assertEquals(2, $result[2]['data']['COUNT_id']); + + $this->assertNull($result[3]['columns'][0]['val']); + $this->assertEquals(1, $result[3]['data']['COUNT_id']); + } + } diff --git a/ext/search_kit/xml/schema/CRM/Search/SearchSegment.xml b/ext/search_kit/xml/schema/CRM/Search/SearchSegment.xml index 128815bbec..180740802f 100644 --- a/ext/search_kit/xml/schema/CRM/Search/SearchSegment.xml +++ b/ext/search_kit/xml/schema/CRM/Search/SearchSegment.xml @@ -73,18 +73,6 @@ - - field_name - Field - Field for which this set is used (may include pseudoconstant suffix). - true - varchar - 255 - - Text - - - items text -- 2.25.1