From a6d0f90f8da1d21cd87e62f56d9bf4dc8f05d1ef Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Thu, 10 Jun 2021 21:11:03 -0400 Subject: [PATCH] APIv4 - Add filter for entity tags --- CRM/Core/BAO/EntityTag.php | 7 +- CRM/Core/DAO/EntityTag.php | 3 +- CRM/Core/PseudoConstant.php | 5 +- .../Incremental/sql/5.40.alpha1.mysql.tpl | 11 ++ Civi/Api4/Query/Api4SelectQuery.php | 2 +- .../Spec/Provider/ContactGetSpecProvider.php | 3 +- .../Provider/EntityTagFilterSpecProvider.php | 105 ++++++++++++++++++ api/v3/utils.php | 12 ++ tests/phpunit/api/v4/Action/FkJoinTest.php | 8 +- .../api/v4/Action/GetExtraFieldsTest.php | 44 ++++++++ tests/phpunit/api/v4/Entity/TagTest.php | 86 ++++++++++++++ xml/schema/Core/EntityTag.xml | 1 + xml/templates/civicrm_data.tpl | 8 +- 13 files changed, 280 insertions(+), 15 deletions(-) create mode 100644 Civi/Api4/Service/Spec/Provider/EntityTagFilterSpecProvider.php create mode 100644 tests/phpunit/api/v4/Entity/TagTest.php diff --git a/CRM/Core/BAO/EntityTag.php b/CRM/Core/BAO/EntityTag.php index 35950c5cf1..9000ade79c 100644 --- a/CRM/Core/BAO/EntityTag.php +++ b/CRM/Core/BAO/EntityTag.php @@ -456,16 +456,17 @@ class CRM_Core_BAO_EntityTag extends CRM_Core_DAO_EntityTag { $params = []; if ($fieldName == 'tag' || $fieldName == 'tag_id') { + $table = 'civicrm_contact'; if (!empty($props['entity_table'])) { - $entity = CRM_Utils_Type::escape($props['entity_table'], 'String'); - $params[] = "used_for LIKE '%$entity%'"; + $table = CRM_Utils_Type::escape($props['entity_table'], 'String'); + $params['condition'][] = "used_for LIKE '%$table%'"; } // Output tag list as nested hierarchy // TODO: This will only work when api.entity is "entity_tag". What about others? if ($context == 'search' || $context == 'create') { $dummyArray = []; - return CRM_Core_BAO_Tag::getTags(CRM_Utils_Array::value('entity_table', $props, 'civicrm_contact'), $dummyArray, CRM_Utils_Array::value('parent_id', $params), '- '); + return CRM_Core_BAO_Tag::getTags($table, $dummyArray, NULL, '- '); } } diff --git a/CRM/Core/DAO/EntityTag.php b/CRM/Core/DAO/EntityTag.php index 0645ff7879..a7bac1ac8e 100644 --- a/CRM/Core/DAO/EntityTag.php +++ b/CRM/Core/DAO/EntityTag.php @@ -6,7 +6,7 @@ * * Generated from xml/schema/CRM/Core/EntityTag.xml * DO NOT EDIT. Generated by CRM_Core_CodeGen - * (GenCodeChecksum:d799212555ac2c5cc5195fcc2e1e1400) + * (GenCodeChecksum:a73f1ade107530181eeb073fd39fc950) */ /** @@ -168,6 +168,7 @@ class CRM_Core_DAO_EntityTag extends CRM_Core_DAO { 'table' => 'civicrm_tag', 'keyColumn' => 'id', 'labelColumn' => 'name', + 'condition' => 'is_tagset != 1', ], 'add' => '1.1', ], diff --git a/CRM/Core/PseudoConstant.php b/CRM/Core/PseudoConstant.php index 5948445f38..9e84516231 100644 --- a/CRM/Core/PseudoConstant.php +++ b/CRM/Core/PseudoConstant.php @@ -193,6 +193,7 @@ class CRM_Core_PseudoConstant { 'onlyActive' => !($context == 'validate' || $context == 'get'), 'fresh' => FALSE, 'context' => $context, + 'condition' => [], ]; $entity = CRM_Core_DAO_AllCoreTables::getBriefName($daoName); @@ -232,10 +233,12 @@ class CRM_Core_PseudoConstant { // Merge params with schema defaults $params += [ - 'condition' => $pseudoconstant['condition'] ?? [], 'keyColumn' => $pseudoconstant['keyColumn'] ?? NULL, 'labelColumn' => $pseudoconstant['labelColumn'] ?? NULL, ]; + if (!empty($pseudoconstant['condition'])) { + $params['condition'] = array_merge((array) $pseudoconstant['condition'], (array) $params['condition']); + } // Fetch option group from option_value table if (!empty($pseudoconstant['optionGroupName'])) { diff --git a/CRM/Upgrade/Incremental/sql/5.40.alpha1.mysql.tpl b/CRM/Upgrade/Incremental/sql/5.40.alpha1.mysql.tpl index a1092d251c..6a2df9741d 100644 --- a/CRM/Upgrade/Incremental/sql/5.40.alpha1.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/5.40.alpha1.mysql.tpl @@ -1 +1,12 @@ {* file to handle db changes in 5.40.alpha1 during upgrade *} + +SELECT @option_group_id_tuf := max(id) from civicrm_option_group where name = 'tag_used_for'; + +UPDATE civicrm_option_value SET name = 'Contact' + WHERE value = 'civicrm_contact' AND option_group_id = @option_group_id_tuf; +UPDATE civicrm_option_value SET name = 'Activity' + WHERE value = 'civicrm_activity' AND option_group_id = @option_group_id_tuf; +UPDATE civicrm_option_value SET name = 'Case' + WHERE value = 'civicrm_case' AND option_group_id = @option_group_id_tuf; +UPDATE civicrm_option_value SET name = 'File' + WHERE value = 'civicrm_file' AND option_group_id = @option_group_id_tuf; diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php index 27b232d8c4..317b6d4348 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -502,7 +502,7 @@ class Api4SelectQuery { if (!empty($field['sql_filters'])) { $sql = []; foreach ($field['sql_filters'] as $filter) { - $clause = is_callable($filter) ? $filter($fieldAlias, $operator, $value, $this, $depth) : NULL; + $clause = is_callable($filter) ? $filter($field, $fieldAlias, $operator, $value, $this, $depth) : NULL; if ($clause) { $sql[] = $clause; } diff --git a/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php b/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php index 8f3683a15f..d9b7155fef 100644 --- a/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php +++ b/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php @@ -52,6 +52,7 @@ class ContactGetSpecProvider implements Generic\SpecProviderInterface { } /** + * @param array $field * @param string $fieldAlias * @param string $operator * @param mixed $value @@ -59,7 +60,7 @@ class ContactGetSpecProvider implements Generic\SpecProviderInterface { * @param int $depth * return string */ - public static function getContactGroupSql(string $fieldAlias, string $operator, $value, Api4SelectQuery $query, int $depth): string { + public static function getContactGroupSql(array $field, string $fieldAlias, string $operator, $value, Api4SelectQuery $query, int $depth): string { $tempTable = \CRM_Utils_SQL_TempTable::build(); $tempTable->createWithColumns('contact_id INT'); $tableName = $tempTable->getName(); diff --git a/Civi/Api4/Service/Spec/Provider/EntityTagFilterSpecProvider.php b/Civi/Api4/Service/Spec/Provider/EntityTagFilterSpecProvider.php new file mode 100644 index 0000000000..7f72d11a3a --- /dev/null +++ b/Civi/Api4/Service/Spec/Provider/EntityTagFilterSpecProvider.php @@ -0,0 +1,105 @@ +getEntity(), 'Array'); + $field->setLabel(ts('With Tags')) + ->setTitle(ts('Tags')) + ->setColumnName('id') + ->setDescription(ts('Filter by tags (including child tags)')) + ->setType('Filter') + ->setOperators(['IN', 'NOT IN']) + ->addSqlFilter([__CLASS__, 'getTagFilterSql']) + ->setOptionsCallback([__CLASS__, 'getTagList']); + $spec->addFieldSpec($field); + } + + /** + * @param string $entity + * @param string $action + * + * @return bool + */ + public function applies($entity, $action) { + if ($action !== 'get') { + return FALSE; + } + $usedFor = \CRM_Core_OptionGroup::values('tag_used_for', FALSE, FALSE, FALSE, NULL, 'name'); + return in_array($entity, $usedFor, TRUE); + } + + /** + * @param array $field + * @param string $fieldAlias + * @param string $operator + * @param mixed $value + * @param \Civi\Api4\Query\Api4SelectQuery $query + * @param int $depth + * return string + */ + public static function getTagFilterSql(array $field, string $fieldAlias, string $operator, $value, Api4SelectQuery $query, int $depth): string { + $tableName = CoreUtil::getTableName($field['entity']); + $tagTree = \CRM_Core_BAO_Tag::getChildTags(); + foreach ($value as $tagID) { + if (!empty($tagTree[$tagID])) { + $value = array_unique(array_merge($value, $tagTree[$tagID])); + } + } + $tags = \CRM_Utils_Type::validate(implode(',', $value), 'CommaSeparatedIntegers'); + return "$fieldAlias $operator (SELECT entity_id FROM `civicrm_entity_tag` WHERE entity_table = '$tableName' AND tag_id IN ($tags))"; + } + + /** + * Callback function to build option list for tags filters. + * + * @param \Civi\Api4\Service\Spec\FieldSpec $spec + * @param array $values + * @param bool|array $returnFormat + * @param bool $checkPermissions + * @return array + */ + public static function getTagList($spec, $values, $returnFormat, $checkPermissions) { + $table = CoreUtil::getTableName($spec->getEntity()); + $result = new Result(); + Request::create('EntityTag', 'getFields', [ + 'version' => 4, + 'loadOptions' => $returnFormat, + 'values' => ['entity_table' => $table], + 'select' => ['options'], + 'where' => [['name', '=', 'tag_id']], + 'checkPermissions' => $checkPermissions, + ])->_run($result); + return $result->first()['options']; + } + +} diff --git a/api/v3/utils.php b/api/v3/utils.php index c9a7f03fef..0c8f319020 100644 --- a/api/v3/utils.php +++ b/api/v3/utils.php @@ -2344,6 +2344,18 @@ function _civicrm_api3_api_match_pseudoconstant_value(&$value, $options, $fieldN return; } + // Legacy handling of tag used_for values, see https://github.com/civicrm/civicrm-core/pull/20573 + if ($fieldName === 'used_for') { + $legacyTagUsedFor = [ + 'Activities' => 'Activity', + 'Contacts' => 'Contact', + 'Cases' => 'Case', + // Attachements [sic] was the original spelling + 'Attachements' => 'File', + ]; + $value = $legacyTagUsedFor[$value] ?? $value; + } + // Translate value into key // Cast $value to string to avoid a bug in array_search $newValue = array_search((string) $value, $options); diff --git a/tests/phpunit/api/v4/Action/FkJoinTest.php b/tests/phpunit/api/v4/Action/FkJoinTest.php index ed140f8738..dcacfd0efa 100644 --- a/tests/phpunit/api/v4/Action/FkJoinTest.php +++ b/tests/phpunit/api/v4/Action/FkJoinTest.php @@ -207,13 +207,13 @@ class FkJoinTest extends UnitTestCase { $grouped = Contact::get()->setCheckPermissions(FALSE) ->addJoin('Tag', FALSE, 'EntityTag', ['tag.name', 'IN', [$tag1, $tag3]]) - ->addSelect('first_name', 'COUNT(tag.name) AS tags') + ->addSelect('first_name', 'COUNT(tag.name) AS tag_count') ->addWhere('id', 'IN', [$cid1, $cid2, $cid3]) ->addGroupBy('id') ->execute()->indexBy('id'); - $this->assertEquals(1, (int) $grouped[$cid1]['tags']); - $this->assertEquals(2, (int) $grouped[$cid2]['tags']); - $this->assertEquals(0, (int) $grouped[$cid3]['tags']); + $this->assertEquals(1, (int) $grouped[$cid1]['tag_count']); + $this->assertEquals(2, (int) $grouped[$cid2]['tag_count']); + $this->assertEquals(0, (int) $grouped[$cid3]['tag_count']); $reverse = Tag::get()->setCheckPermissions(FALSE) ->addJoin('Contact', FALSE, 'EntityTag', ['contact.id', 'IN', [$cid1, $cid2, $cid3]]) diff --git a/tests/phpunit/api/v4/Action/GetExtraFieldsTest.php b/tests/phpunit/api/v4/Action/GetExtraFieldsTest.php index 50707591d4..2c49db46b9 100644 --- a/tests/phpunit/api/v4/Action/GetExtraFieldsTest.php +++ b/tests/phpunit/api/v4/Action/GetExtraFieldsTest.php @@ -20,8 +20,10 @@ namespace api\v4\Action; use api\v4\UnitTestCase; +use Civi\Api4\Activity; use Civi\Api4\Address; use Civi\Api4\Contact; +use Civi\Api4\Tag; /** * @group headless @@ -78,4 +80,46 @@ class GetExtraFieldsTest extends UnitTestCase { $this->assertGreaterThan(1, count($fields['contact_id.gender_id']['options'])); } + public function testGetTagsFromFilterField() { + $actTag = Tag::create(FALSE) + ->addValue('name', uniqid('act')) + ->addValue('used_for', 'civicrm_activity') + ->addValue('color', '#aaaaaa') + ->execute()->first(); + $conTag = Tag::create(FALSE) + ->addValue('name', uniqid('con')) + ->addValue('used_for', 'civicrm_contact') + ->addValue('color', '#cccccc') + ->execute()->first(); + $tagSet = Tag::create(FALSE) + ->addValue('name', uniqid('set')) + ->addValue('used_for', 'civicrm_contact') + ->addValue('is_tagset', TRUE) + ->execute()->first(); + $setChild = Tag::create(FALSE) + ->addValue('name', uniqid('child')) + ->addValue('parent_id', $tagSet['id']) + ->execute()->first(); + + $actField = Activity::getFields(FALSE) + ->addWhere('name', '=', 'tags') + ->setLoadOptions(['name', 'color']) + ->execute()->first(); + $actTags = array_column($actField['options'], 'color', 'name'); + $this->assertEquals('#aaaaaa', $actTags[$actTag['name']]); + $this->assertArrayNotHasKey($conTag['name'], $actTags); + $this->assertArrayNotHasKey($tagSet['name'], $actTags); + $this->assertArrayNotHasKey($setChild['name'], $actTags); + + $conField = Contact::getFields(FALSE) + ->addWhere('name', '=', 'tags') + ->setLoadOptions(['name', 'color']) + ->execute()->first(); + $conTags = array_column($conField['options'], 'color', 'name'); + $this->assertEquals('#cccccc', $conTags[$conTag['name']]); + $this->assertArrayNotHasKey($actTag['name'], $conTags); + $this->assertArrayNotHasKey($tagSet['name'], $conTags); + $this->assertArrayHasKey($setChild['name'], $conTags); + } + } diff --git a/tests/phpunit/api/v4/Entity/TagTest.php b/tests/phpunit/api/v4/Entity/TagTest.php new file mode 100644 index 0000000000..ac2f7bce2e --- /dev/null +++ b/tests/phpunit/api/v4/Entity/TagTest.php @@ -0,0 +1,86 @@ +addValue('name', uniqid('con')) + ->addValue('used_for', 'civicrm_contact') + ->addValue('color', '#cccccc') + ->execute()->first(); + $tagChild = Tag::create(FALSE) + ->addValue('name', uniqid('child')) + ->addValue('parent_id', $conTag['id']) + ->execute()->first(); + $tagSubChild = Tag::create(FALSE) + ->addValue('name', uniqid('child')) + ->addValue('parent_id', $tagChild['id']) + ->execute()->first(); + $tagSet = Tag::create(FALSE) + ->addValue('name', uniqid('set')) + ->addValue('used_for', 'civicrm_contact') + ->addValue('is_tagset', TRUE) + ->execute()->first(); + $setChild = Tag::create(FALSE) + ->addValue('name', uniqid('child')) + ->addValue('parent_id', $tagSet['id']) + ->execute()->first(); + + $contact1 = Contact::create(FALSE) + ->execute()->first(); + $contact2 = Contact::create(FALSE) + ->execute()->first(); + EntityTag::create(FALSE) + ->addValue('entity_id', $contact1['id']) + ->addValue('entity_table', 'civicrm_contact') + ->addValue('tag_id', $tagSubChild['id']) + ->execute(); + EntityTag::create(FALSE) + ->addValue('entity_id', $contact2['id']) + ->addValue('entity_table', 'civicrm_contact') + ->addValue('tag_id', $setChild['id']) + ->execute(); + + $shouldReturnContact1 = Contact::get(FALSE) + ->addSelect('id') + ->addWhere('tags:name', 'IN', [$conTag['name']]) + ->execute(); + $this->assertCount(1, $shouldReturnContact1); + $this->assertEquals($contact1['id'], $shouldReturnContact1->first()['id']); + + $shouldReturnContact2 = Contact::get(FALSE) + ->addSelect('id') + ->addWhere('tags', 'IN', [$setChild['id']]) + ->execute(); + $this->assertCount(1, $shouldReturnContact2); + $this->assertEquals($contact2['id'], $shouldReturnContact2->first()['id']); + } + +} diff --git a/xml/schema/Core/EntityTag.xml b/xml/schema/Core/EntityTag.xml index e7aeb504de..cccfd8314d 100644 --- a/xml/schema/Core/EntityTag.xml +++ b/xml/schema/Core/EntityTag.xml @@ -57,6 +57,7 @@ civicrm_tag
id name + is_tagset != 1 Select diff --git a/xml/templates/civicrm_data.tpl b/xml/templates/civicrm_data.tpl index ad9ebafe45..7898670885 100644 --- a/xml/templates/civicrm_data.tpl +++ b/xml/templates/civicrm_data.tpl @@ -750,10 +750,10 @@ VALUES (@option_group_id_website, 'Vine', 12, 'Vine ', NULL, 0, NULL, 12, NULL, 0, 0, 1, NULL, NULL, NULL), -- Tag used for - (@option_group_id_tuf, 'Contacts', 'civicrm_contact', 'Contacts', NULL, 0, NULL, 1, NULL, 0, 0, 1, NULL, NULL, NULL), - (@option_group_id_tuf, 'Activities', 'civicrm_activity', 'Activities', NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL, NULL, NULL), - (@option_group_id_tuf, 'Cases', 'civicrm_case', 'Cases', NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL, NULL, NULL), - (@option_group_id_tuf, 'Attachments','civicrm_file', 'Attachements', NULL, 0, NULL, 4, NULL, 0, 0, 1, NULL, NULL, NULL), + (@option_group_id_tuf, '{ts escape="sql"}Contacts{/ts}', 'civicrm_contact', 'Contact', NULL, 0, NULL, 1, NULL, 0, 0, 1, NULL, NULL, NULL), + (@option_group_id_tuf, '{ts escape="sql"}Activities{/ts}', 'civicrm_activity', 'Activity', NULL, 0, NULL, 2, NULL, 0, 0, 1, NULL, NULL, NULL), + (@option_group_id_tuf, '{ts escape="sql"}Cases{/ts}', 'civicrm_case', 'Case', NULL, 0, NULL, 3, NULL, 0, 0, 1, NULL, NULL, NULL), + (@option_group_id_tuf, '{ts escape="sql"}Attachments{/ts}', 'civicrm_file', 'File', NULL, 0, NULL, 4, NULL, 0, 0, 1, NULL, NULL, NULL), (@option_group_id_currency, 'USD ($)', 'USD', 'USD', NULL, 0, 1, 1, NULL, 0, 0, 1, NULL, NULL, NULL), -- 2.25.1