From 7de1316125dd7076730a91783fa3d6d12b065f81 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Mon, 8 Feb 2021 21:55:09 -0500 Subject: [PATCH] APIv4 - Support implicit joins to explicitly joined tables In APIv4 you can imply a join through dot notation e.g. contact.display_name. You can also explicity add a join with the addJoin() method. This allows the two techniques to be combined so that e.g. an explicitly added Address join can implicity select fields joined from Address to Country. --- Civi/Api4/Query/Api4SelectQuery.php | 32 ++++++++-- Civi/Api4/Service/Schema/Joiner.php | 60 ++++++++----------- tests/phpunit/api/v4/Action/FkJoinTest.php | 10 ++++ .../api/v4/DataSets/DefaultDataSet.json | 9 +++ 4 files changed, 69 insertions(+), 42 deletions(-) diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php index ad05e798c7..3122d24582 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -76,6 +76,11 @@ class Api4SelectQuery { */ public $forceSelectId = TRUE; + /** + * @var array + */ + private $explicitJoins = []; + /** * @param \Civi\Api4\Generic\DAOGetAction $apiGet */ @@ -545,7 +550,16 @@ class Api4SelectQuery { $field['sql_name'] = '`' . $alias . '`.`' . $field['column_name'] . '`'; $this->addSpecField($alias . '.' . $field['name'], $field); } + $tableName = CoreUtil::getTableName($entity); + // Save join info to be retrieved by $this->getExplicitJoin() + $this->explicitJoins[$alias] = [ + 'entity' => $entity, + 'table' => $tableName, + 'bridge' => NULL, + ]; + // 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]; $conditions = $this->getBridgeJoin($join, $entity, $alias); } else { @@ -554,7 +568,6 @@ class Api4SelectQuery { foreach (array_filter($join) as $clause) { $conditions[] = $this->treeWalkClauses($clause, 'ON'); } - $tableName = CoreUtil::getTableName($entity); $this->join($side, $tableName, $alias, $conditions); } } @@ -739,14 +752,13 @@ class Api4SelectQuery { $joiner = \Civi::container()->get('joiner'); // The last item in the path is the field name. We don't care about that; we'll add all fields from the joined entity. array_pop($pathArray); - $pathString = implode('.', $pathArray); - if (!$joiner->canAutoJoin($this->getFrom(), $pathString)) { + try { + $joinPath = $joiner->autoJoin($this, $pathArray); + } + catch (\Exception $e) { return; } - - $joinPath = $joiner->join($this, $pathString); - $lastLink = array_pop($joinPath); // Custom field names are already prefixed @@ -861,6 +873,14 @@ class Api4SelectQuery { return $this->api->getCheckPermissions(); } + /** + * @param string $alias + * @return array|NULL + */ + public function getExplicitJoin($alias) { + return $this->explicitJoins[$alias] ?? NULL; + } + /** * @param string $path * @param array $field diff --git a/Civi/Api4/Service/Schema/Joiner.php b/Civi/Api4/Service/Schema/Joiner.php index 8786ec72bb..a412d6571c 100644 --- a/Civi/Api4/Service/Schema/Joiner.php +++ b/Civi/Api4/Service/Schema/Joiner.php @@ -42,8 +42,8 @@ class Joiner { /** * @param \Civi\Api4\Query\Api4SelectQuery $query * The query object to do the joins on - * @param string $joinPath - * A path of aliases in dot notation, e.g. contact.phone + * @param array $joinPath + * A list of aliases, e.g. [contact, phone] * @param string $side * Can be LEFT or INNER * @@ -51,65 +51,53 @@ class Joiner { * @return \Civi\Api4\Service\Schema\Joinable\Joinable[] * The path used to make the join */ - public function join(Api4SelectQuery $query, $joinPath, $side = 'LEFT') { - $fullPath = $this->getPath($query->getFrom(), $joinPath); - $baseTable = $query::MAIN_TABLE_ALIAS; + public function autoJoin(Api4SelectQuery $query, array $joinPath, $side = 'LEFT') { + $explicitJoin = $query->getExplicitJoin($joinPath[0]); + + // If the first item is the name of an explicit join, use it as the base & shift it off the path + if ($explicitJoin) { + $from = $explicitJoin['table']; + $baseTableAlias = array_shift($joinPath); + } + // Otherwise use the api entity as the base + else { + $from = $query->getFrom(); + $baseTableAlias = $query::MAIN_TABLE_ALIAS; + } + + $fullPath = $this->getPath($from, $joinPath); foreach ($fullPath as $link) { $target = $link->getTargetTable(); $alias = $link->getAlias(); $bao = \CRM_Core_DAO_AllCoreTables::getBAOClassName(\CRM_Core_DAO_AllCoreTables::getClassForTable($target)); - $conditions = $link->getConditionsForJoin($baseTable); + $conditions = $link->getConditionsForJoin($baseTableAlias); // Custom fields do not have a bao, and currently do not have field-specific ACLs if ($bao) { - $conditions = array_merge($conditions, $query->getAclClause($alias, $bao, explode('.', $joinPath))); + $conditions = array_merge($conditions, $query->getAclClause($alias, $bao, $joinPath)); } $query->join($side, $target, $alias, $conditions); - $baseTable = $link->getAlias(); + $baseTableAlias = $link->getAlias(); } return $fullPath; } - /** - * Determines if path string points to a simple n-1 join that can be automatically added - * - * @param string $baseTable - * @param $joinPath - * - * @return bool - */ - public function canAutoJoin($baseTable, $joinPath) { - try { - $path = $this->getPath($baseTable, $joinPath); - foreach ($path as $joinable) { - if ($joinable->getJoinType() === $joinable::JOIN_TYPE_ONE_TO_MANY) { - return FALSE; - } - } - return TRUE; - } - catch (\Exception $e) { - return FALSE; - } - } - /** * @param string $baseTable - * @param string $joinPath + * @param array $joinPath * * @return \Civi\Api4\Service\Schema\Joinable\Joinable[] * @throws \Exception */ - protected function getPath($baseTable, $joinPath) { - $cacheKey = sprintf('%s.%s', $baseTable, $joinPath); + protected function getPath(string $baseTable, array $joinPath) { + $cacheKey = sprintf('%s.%s', $baseTable, implode('.', $joinPath)); if (!isset($this->cache[$cacheKey])) { - $stack = explode('.', $joinPath); $fullPath = []; - foreach ($stack as $key => $targetAlias) { + foreach ($joinPath as $key => $targetAlias) { $links = $this->schemaMap->getPath($baseTable, $targetAlias); if (empty($links)) { diff --git a/tests/phpunit/api/v4/Action/FkJoinTest.php b/tests/phpunit/api/v4/Action/FkJoinTest.php index 39db4a7ae6..7c52dfe9c6 100644 --- a/tests/phpunit/api/v4/Action/FkJoinTest.php +++ b/tests/phpunit/api/v4/Action/FkJoinTest.php @@ -99,6 +99,16 @@ class FkJoinTest extends UnitTestCase { $this->assertEquals('1', $contacts[0]['phone.location_type_id']); } + public function testImplicitJoinOnExplicitJoin() { + $contacts = Contact::get(FALSE) + ->addWhere('id', '=', $this->getReference('test_contact_1')['id']) + ->addJoin('Address AS address', TRUE, ['id', '=', 'address.contact_id'], ['address.location_type_id', '=', 1]) + ->addSelect('id', 'address.country.iso_code') + ->execute(); + $this->assertCount(1, $contacts); + $this->assertEquals('US', $contacts[0]['address.country.iso_code']); + } + public function testJoinToTheSameTableTwice() { $cid1 = Contact::create(FALSE) ->addValue('first_name', 'Aaa') diff --git a/tests/phpunit/api/v4/DataSets/DefaultDataSet.json b/tests/phpunit/api/v4/DataSets/DefaultDataSet.json index 834f107460..17a0a297df 100644 --- a/tests/phpunit/api/v4/DataSets/DefaultDataSet.json +++ b/tests/phpunit/api/v4/DataSets/DefaultDataSet.json @@ -41,5 +41,14 @@ "phone": "+3538733439483", "location_type_id": "2" } + ], + "Address": [ + { + "contact_id": "@ref test_contact_1.id", + "street_address": "123 Sesame St.", + "country_id": "1228", + "location_type_id": "1", + "@ref": "test_address_1" + } ] } -- 2.25.1