From d11f6e17002accd2f34d679b7faed2097060aa18 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Sun, 30 Apr 2023 18:06:32 -0400 Subject: [PATCH] APIv4 - Simplify bridge joins This simplifies the code and improves efficiency by using an inner sub-join instead of a subquery. The latter was trying to fake it, but this is the real thing: joining on two tables as if they were a single table. --- Civi/Api4/Query/Api4SelectQuery.php | 83 +++++++---------------------- 1 file changed, 19 insertions(+), 64 deletions(-) diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php index 8430da8794..0ba463c334 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -43,11 +43,6 @@ class Api4SelectQuery { */ protected $query; - /** - * @var array - */ - protected $joins = []; - /** * Used to keep track of implicit join table aliases * @var array @@ -870,12 +865,10 @@ class Api4SelectQuery { } /** - * Join via a Bridge table + * Join via a Bridge table using a join within a join * * This creates a double-join in sql that appears to the API user like a single join. * - * LEFT joins use a subquery so that the bridge + joined-entity can be treated like a single table. - * * @param array $joinTree * @param string $joinEntity * @param string $alias @@ -886,52 +879,30 @@ class Api4SelectQuery { $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'; - $joinAlias = $side === 'INNER' ? $alias : 'c'; + $bridgeAlias = $alias . '_via_' . strtolower($bridgeEntity); $joinTable = CoreUtil::getTableName($joinEntity); [$bridgeTable, $baseRef, $joinRef] = $this->getBridgeRefs($bridgeEntity, $joinEntity); - $bridgeFields = $this->registerBridgeJoinFields($bridgeEntity, $joinRef, $baseRef, $alias, $bridgeAlias, $side); + $this->registerBridgeJoinFields($bridgeEntity, $joinRef, $baseRef, $alias, $bridgeAlias); - $linkConditions = $this->getBridgeLinkConditions($bridgeAlias, $joinAlias, $joinTable, $joinRef); + $linkConditions = $this->getBridgeLinkConditions($bridgeAlias, $alias, $joinTable, $joinRef); $bridgeConditions = $this->getBridgeJoinConditions($joinTree, $baseRef, $alias, $bridgeAlias, $bridgeEntity, $side); - $acls = array_values($this->getAclClause($joinAlias, CoreUtil::getBAOFromApiName($joinEntity), [NULL, NULL])); + $acls = array_values($this->getAclClause($alias, CoreUtil::getBAOFromApiName($joinEntity), [NULL, NULL])); $joinConditions = []; foreach (array_filter($joinTree) as $clause) { $joinConditions[] = $this->treeWalkClauses($clause, 'ON'); } - // 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)); - } - // For LEFT joins, construct a subquery to link the bridge & join tables as one - else { - $joinEntityClass = CoreUtil::getApiClass($joinEntity); - foreach ($joinEntityClass::get($this->getCheckPermissions())->entityFields() as $name => $field) { - if ($field['type'] === 'Field') { - $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); - $subquery = "SELECT $select FROM `$bridgeTable` `$bridgeAlias`, `$joinTable` `$joinAlias` WHERE " . implode(' AND ', $innerConditions); - $this->query->join($alias, "$side JOIN ($subquery) `$alias` ON " . implode(' AND ', $joinConditions)); - } + // Info needed for joining custom fields extending the bridge entity + $this->explicitJoins[$alias]['bridge_table_alias'] = $bridgeAlias; + + $outerConditions = array_merge($joinConditions, $bridgeConditions); + $innerConditions = array_merge($linkConditions, $acls); + $this->query->join($alias, "$side JOIN (`$bridgeTable` AS `$bridgeAlias` INNER JOIN `$joinTable` AS `$alias` ON (" . implode(' AND ', $innerConditions) . ")) ON " . implode(' AND ', $outerConditions)); } /** @@ -995,27 +966,20 @@ class Api4SelectQuery { * @param $baseRef * @param string $alias * @param string $bridgeAlias - * @param string $side - * @return array */ - private function registerBridgeJoinFields($bridgeEntity, $joinRef, $baseRef, string $alias, string $bridgeAlias, string $side): array { - $fakeFields = []; + private function registerBridgeJoinFields($bridgeEntity, $joinRef, $baseRef, string $alias, string $bridgeAlias): void { $bridgeFkFields = [$joinRef->getReferenceKey(), $joinRef->getTypeColumn(), $baseRef->getReferenceKey(), $baseRef->getTypeColumn()]; $bridgeEntityClass = CoreUtil::getApiClass($bridgeEntity); + $bridgeIdColumn = CoreUtil::getIdFieldName($bridgeEntity); foreach ($bridgeEntityClass::get($this->getCheckPermissions())->entityFields() as $name => $field) { - if ($name === 'id' || ($side === 'INNER' && in_array($name, $bridgeFkFields, TRUE))) { + if ($name === $bridgeIdColumn || in_array($name, $bridgeFkFields, TRUE)) { continue; } - // For INNER joins, these fields get a sql alias pointing to the bridge entity, - // but an api alias pretending they belong to the join entity. - $field['sql_name'] = '`' . ($side === 'LEFT' ? $alias : $bridgeAlias) . '`.`' . $field['column_name'] . '`'; + // Fields get a sql alias pointing to the bridge entity, + $field['sql_name'] = '`' . $bridgeAlias . '`.`' . $field['column_name'] . '`'; $field['explicit_join'] = $alias; $this->addSpecField($alias . '.' . $name, $field); - if ($field['type'] === 'Field') { - $fakeFields[$field['column_name']] = '`' . $bridgeAlias . '`.`' . $field['column_name'] . '`'; - } } - return $fakeFields; } /** @@ -1026,13 +990,11 @@ class Api4SelectQuery { * @param string $alias * @param string $bridgeAlias * @param string $bridgeEntity - * @param string $side * @return string[] * @throws \CRM_Core_Exception */ - private function getBridgeJoinConditions(array &$joinTree, $baseRef, string $alias, string $bridgeAlias, string $bridgeEntity, string $side): array { + private function getBridgeJoinConditions(array &$joinTree, $baseRef, string $alias, string $bridgeAlias, string $bridgeEntity): array { $bridgeConditions = []; - $bridgeAlias = $side === 'INNER' ? $bridgeAlias : $alias; // Find explicit bridge join conditions and move them out of the joinTree $joinTree = array_filter($joinTree, function ($clause) use ($baseRef, $alias, $bridgeAlias, &$bridgeConditions) { [$sideA, $op, $sideB] = array_pad((array) $clause, 3, NULL); @@ -1148,9 +1110,6 @@ class Api4SelectQuery { 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() @@ -1194,12 +1153,8 @@ class Api4SelectQuery { * @param string $tableAlias * @param array $conditions */ - public function join($side, $tableName, $tableAlias, $conditions) { - // INNER JOINs take precedence over LEFT JOINs - if ($side != 'LEFT' || !isset($this->joins[$tableAlias])) { - $this->joins[$tableAlias] = $side; - $this->query->join($tableAlias, "$side JOIN `$tableName` `$tableAlias` ON " . implode(' AND ', $conditions)); - } + private function join(string $side, string $tableName, string $tableAlias, array $conditions): void { + $this->query->join($tableAlias, "$side JOIN `$tableName` `$tableAlias` ON " . implode(' AND ', $conditions)); } /** -- 2.25.1