From 07e7a46bc490f22a7caaaa3a555776b2cffe8314 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Thu, 9 Sep 2021 17:50:57 -0400 Subject: [PATCH] SearchKit - Support custom fields in bridge join entities This fixes bridge joins in APIv4 to allow selecting custom fields that belong to the bridge entity as if they were part of the joined entity. This was already working for core fields. --- Civi/Api4/Query/Api4SelectQuery.php | 39 ++++++++++++++++--- .../crmSearchAdmin.component.js | 2 +- .../api/v4/Action/BasicCustomFieldTest.php | 29 +++++++++++++- 3 files changed, 63 insertions(+), 7 deletions(-) diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php index 929b48d2eb..191ef0f46b 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -733,7 +733,6 @@ class Api4SelectQuery { ]; // If the first condition is a string, it's the name of a bridge entity if (!empty($join[0]) && is_string($join[0]) && \CRM_Utils_Rule::alphanumeric($join[0])) { - $this->explicitJoins[$alias]['bridge'] = $join[0]; $this->addBridgeJoin($join, $entity, $alias, $side); } else { @@ -811,6 +810,7 @@ class Api4SelectQuery { */ protected function addBridgeJoin($joinTree, $joinEntity, $alias, $side) { $bridgeEntity = array_shift($joinTree); + $this->explicitJoins[$alias]['bridge'] = $bridgeEntity; // INNER joins require unique aliases, whereas left joins will be inside a subquery and short aliases are more readable $bridgeAlias = $side === 'INNER' ? $alias . '_via_' . strtolower($bridgeEntity) : 'b'; @@ -834,6 +834,9 @@ class Api4SelectQuery { // INNER joins are done with 2 joins if ($side === 'INNER') { + // Info needed for joining custom fields extending the bridge entity + $this->explicitJoins[$alias]['bridge_table_alias'] = $bridgeAlias; + $this->explicitJoins[$alias]['bridge_id_alias'] = 'id'; $this->join('INNER', $bridgeTable, $bridgeAlias, $bridgeConditions); $this->join('INNER', $joinTable, $alias, array_merge($linkConditions, $acls, $joinConditions)); } @@ -845,6 +848,10 @@ class Api4SelectQuery { $bridgeFields[$field['column_name']] = '`' . $joinAlias . '`.`' . $field['column_name'] . '`'; } } + // Info needed for joining custom fields extending the bridge entity + $this->explicitJoins[$alias]['bridge_table_alias'] = $alias; + $this->explicitJoins[$alias]['bridge_id_alias'] = 'bridge_entity_id_key'; + $bridgeFields[] = "`$bridgeAlias`.`id` AS `bridge_entity_id_key`"; $select = implode(',', $bridgeFields); $joinConditions = array_merge($joinConditions, $bridgeConditions); $innerConditions = array_merge($linkConditions, $acls); @@ -1018,12 +1025,25 @@ class Api4SelectQuery { // During iteration this variable will refer to the current position in the tree $joinTreeNode =& $this->joinTree[$baseTableAlias]; + $useBridgeTable = FALSE; try { $joinPath = $joiner->getPath($explicitJoin['table'] ?? $this->getFrom(), $pathArray); } catch (\API_Exception $e) { - // Because the select clause silently ignores unknown fields, this function shouldn't throw exceptions - return; + if ($explicitJoin['bridge']) { + // Try looking up custom field in bridge entity instead + try { + $useBridgeTable = TRUE; + $joinPath = $joiner->getPath(CoreUtil::getTableName($explicitJoin['bridge']), $pathArray); + } + catch (\API_Exception $e) { + return; + } + } + else { + // Because the select clause silently ignores unknown fields, this function shouldn't throw exceptions + return; + } } foreach ($joinPath as $joinName => $link) { @@ -1053,6 +1073,14 @@ class Api4SelectQuery { \CRM_Core_Error::deprecatedWarning("Deprecated join alias '$deprecatedAlias' used in APIv4 get. Should be changed to '{$deprecatedAlias}_id'"); } $virtualField = $link->getSerialize(); + $baseTableAlias = $joinTreeNode['#table_alias']; + if ($useBridgeTable) { + // When joining custom fields that directly extend the bridge entity + $baseTableAlias = $explicitJoin['bridge_table_alias']; + if ($link->getBaseColumn() === 'id') { + $link->setBaseColumn($explicitJoin['bridge_id_alias']); + } + } // Cache field info for retrieval by $this->getField() foreach ($link->getEntityFields() as $fieldObject) { @@ -1064,7 +1092,7 @@ class Api4SelectQuery { // For virtual joins on serialized fields, the callback function will need the sql name of the serialized field // @see self::renderSerializedJoin() else { - $fieldArray['sql_name'] = '`' . $joinTreeNode['#table_alias'] . '`.`' . $link->getBaseColumn() . '`'; + $fieldArray['sql_name'] = '`' . $baseTableAlias . '`.`' . $link->getBaseColumn() . '`'; } // Custom fields will already have the group name prefixed $fieldName = $isCustom ? explode('.', $fieldArray['name'])[1] : $fieldArray['name']; @@ -1074,7 +1102,7 @@ class Api4SelectQuery { // Serialized joins are rendered by this::renderSerializedJoin. Don't add their tables. if (!$virtualField) { $bao = $joinEntity ? CoreUtil::getBAOFromApiName($joinEntity) : NULL; - $conditions = $link->getConditionsForJoin($joinTreeNode['#table_alias'], $tableAlias); + $conditions = $link->getConditionsForJoin($baseTableAlias, $tableAlias); if ($bao) { $conditions = array_merge($conditions, $this->getAclClause($tableAlias, $bao, $joinPath)); } @@ -1083,6 +1111,7 @@ class Api4SelectQuery { } $joinTreeNode =& $joinTreeNode[$joinName]; + $useBridgeTable = FALSE; } } diff --git a/ext/search_kit/ang/crmSearchAdmin/crmSearchAdmin.component.js b/ext/search_kit/ang/crmSearchAdmin/crmSearchAdmin.component.js index b09294fb4a..9145885821 100644 --- a/ext/search_kit/ang/crmSearchAdmin/crmSearchAdmin.component.js +++ b/ext/search_kit/ang/crmSearchAdmin/crmSearchAdmin.component.js @@ -475,7 +475,7 @@ // Add extra searchable fields from bridge entity if (join && join.bridge) { addFields(_.filter(searchMeta.getEntity(join.bridge).fields, function(field) { - return (field.name !== 'id' && field.name !== 'entity_id' && field.name !== 'entity_table' && !field.fk_entity && !_.includes(field.name, '.')); + return (field.name !== 'id' && field.name !== 'entity_id' && field.name !== 'entity_table' && !field.fk_entity); })); } diff --git a/tests/phpunit/api/v4/Action/BasicCustomFieldTest.php b/tests/phpunit/api/v4/Action/BasicCustomFieldTest.php index ef5d38eaa4..89846c1ee2 100644 --- a/tests/phpunit/api/v4/Action/BasicCustomFieldTest.php +++ b/tests/phpunit/api/v4/Action/BasicCustomFieldTest.php @@ -247,6 +247,12 @@ class BasicCustomFieldTest extends BaseCustomValueTest { ->addValue('data_type', 'String') ->execute(); + // Adding custom field to Relationship entity also adds it to RelationshipCache entity + $this->assertCount(1, RelationshipCache::getFields(FALSE) + ->addWhere('name', '=', "$cgName.PetName") + ->execute() + ); + $parent = Contact::create(FALSE) ->addValue('first_name', 'Parent') ->addValue('last_name', 'Tester') @@ -261,13 +267,14 @@ class BasicCustomFieldTest extends BaseCustomValueTest { ->execute() ->first()['id']; - $relationship = Relationship::create(FALSE) + Relationship::create(FALSE) ->addValue('contact_id_a', $parent) ->addValue('contact_id_b', $child) ->addValue('relationship_type_id', 1) ->addValue("$cgName.PetName", 'Buddy') ->execute(); + // Test get directly from relationshipCache entity $results = RelationshipCache::get(FALSE) ->addSelect("$cgName.PetName") ->addWhere("$cgName.PetName", '=', 'Buddy') @@ -275,6 +282,26 @@ class BasicCustomFieldTest extends BaseCustomValueTest { $this->assertCount(2, $results); $this->assertEquals('Buddy', $results[0]["$cgName.PetName"]); + + // Test get via bridge INNER join + $result = Contact::get(FALSE) + ->addSelect('relative.display_name', "relative.$cgName.PetName") + ->addJoin('Contact AS relative', 'INNER', 'RelationshipCache') + ->addWhere('id', '=', $parent) + ->addWhere('relative.relationship_type_id', '=', 1) + ->execute()->single(); + $this->assertEquals('Child Tester', $result['relative.display_name']); + $this->assertEquals('Buddy', $result["relative.$cgName.PetName"]); + + // Test get via bridge LEFT join + $result = Contact::get(FALSE) + ->addSelect('relative.display_name', "relative.$cgName.PetName") + ->addJoin('Contact AS relative', 'LEFT', 'RelationshipCache') + ->addWhere('id', '=', $parent) + ->addWhere('relative.relationship_type_id', '=', 1) + ->execute()->single(); + $this->assertEquals('Child Tester', $result['relative.display_name']); + $this->assertEquals('Buddy', $result["relative.$cgName.PetName"]); } public function testMultipleJoinsToCustomTable() { -- 2.25.1