/** * Add required JOINs to resulting Query Builder. * * @param QueryBuilder $qb * @param array $parts * @return bool */ protected function addJoins(QueryBuilder $qb, array $parts) { // Collect list of tables which should be added to new query $requiredToJoin = $this->qbTools->getUsedTableAliases($parts['where']); $requiredToJoin = array_merge($requiredToJoin, $this->qbTools->getUsedTableAliases($parts['groupBy'])); $requiredToJoin = array_merge($requiredToJoin, $this->qbTools->getUsedTableAliases($parts['having'])); $requiredToJoin = array_merge($requiredToJoin, $this->qbTools->getUsedJoinAliases($parts['join'], $requiredToJoin, $this->rootAlias)); $requiredToJoin = array_diff(array_unique($requiredToJoin), array($this->rootAlias)); /** @var Expr\Join $join */ $hasJoins = false; foreach ($parts['join'][$this->rootAlias] as $join) { $alias = $join->getAlias(); // To count results number join all tables with inner join and required to tables if ($join->getJoinType() == Expr\Join::INNER_JOIN || in_array($alias, $requiredToJoin)) { $hasJoins = true; $condition = $this->qbTools->replaceAliasesWithFields($join->getCondition()); $condition = $this->qbTools->replaceAliasesWithJoinPaths($condition); if ($join->getJoinType() == Expr\Join::INNER_JOIN) { $qb->innerJoin($join->getJoin(), $alias, $join->getConditionType(), $condition, $join->getIndexBy()); } else { $qb->leftJoin($join->getJoin(), $alias, $join->getConditionType(), $condition, $join->getIndexBy()); } } } return $hasJoins; }
/** * Add required JOINs to resulting Query Builder. * * @param QueryBuilder $optimizedQueryBuilder * @param array $originalQueryParts */ protected function addJoins(QueryBuilder $optimizedQueryBuilder, array $originalQueryParts) { // Collect list of tables which should be added to new query $whereAliases = $this->qbTools->getUsedTableAliases($originalQueryParts['where']); $groupByAliases = $this->qbTools->getUsedTableAliases($originalQueryParts['groupBy']); $havingAliases = $this->qbTools->getUsedTableAliases($originalQueryParts['having']); $joinAliases = array_merge($whereAliases, $groupByAliases, $havingAliases); $joinAliases = array_unique($joinAliases); // this joins cannot be removed outside of this class $requiredJoinAliases = $joinAliases; $joinAliases = array_merge($joinAliases, $this->getNonSymmetricJoinAliases($originalQueryParts['from'], $originalQueryParts['join'], $groupByAliases)); $rootAliases = []; /** @var Expr\From $from */ foreach ($originalQueryParts['from'] as $from) { $rootAliases[] = $from->getAlias(); $joinAliases = array_merge($joinAliases, $this->qbTools->getUsedJoinAliases($originalQueryParts['join'], $joinAliases, $from->getAlias())); } $allAliases = $this->context->getAliases(); $joinAliases = array_intersect(array_diff(array_unique($joinAliases), $rootAliases), $allAliases); $requiredJoinAliases = array_intersect(array_diff($requiredJoinAliases, $rootAliases), $allAliases); $joinAliases = $this->dispatchQueryOptimizationEvent($joinAliases, $requiredJoinAliases); foreach ($rootAliases as $rootAlias) { if (!isset($originalQueryParts['join'][$rootAlias])) { continue; } /** @var Expr\Join $join */ foreach ($originalQueryParts['join'][$rootAlias] as $join) { $alias = $join->getAlias(); // To count results number join all tables with inner join and required to tables if ($join->getJoinType() === Expr\Join::INNER_JOIN || in_array($alias, $joinAliases, true)) { $condition = $this->qbTools->replaceAliasesWithFields($join->getCondition()); $condition = $this->qbTools->replaceAliasesWithJoinPaths($condition); if ($join->getJoinType() === Expr\Join::INNER_JOIN) { $optimizedQueryBuilder->innerJoin($join->getJoin(), $alias, $join->getConditionType(), $condition, $join->getIndexBy()); } else { $optimizedQueryBuilder->leftJoin($join->getJoin(), $alias, $join->getConditionType(), $condition, $join->getIndexBy()); } } } } }
/** * @param QueryBuilder $qb * @param QueryBuilderTools $qbTools * @param array $value * @param string $joinType * @param array $usedAliases * @param array $knownAliases */ protected function addJoinByDefinition(QueryBuilder $qb, QueryBuilderTools $qbTools, array $value, $joinType, array &$usedAliases, array $knownAliases) { $joinType = strtolower($joinType); if (!isset($value['join'][$joinType])) { return; } $defaultValues = ['conditionType' => null, 'condition' => null]; foreach ((array) $value['join'][$joinType] as $join) { if (in_array($join['alias'], $usedAliases)) { continue; } $join = array_merge($defaultValues, $join); $joinUsedAliases = array_merge($qbTools->getUsedTableAliases($join['join']), $qbTools->getTablesUsedInJoinCondition($join['condition'], $knownAliases)); // Intersect with known aliases to prevent counting aliases from subselects $joinUsedAliases = array_intersect($joinUsedAliases, $knownAliases); $unknownAliases = array_diff($joinUsedAliases, array_merge($usedAliases, [$join['alias']])); if (!empty($unknownAliases)) { continue; } $joinMethod = $joinType . 'Join'; $qb->{$joinMethod}($join['join'], $join['alias'], $join['conditionType'], $join['condition']); $usedAliases[] = $join['alias']; } }
/** * @dataProvider usedTableAliasesDataProvider * @param string|array $condition * @param array $expected */ public function testGetUsedTableAliases($condition, $expected) { $selects = array($this->getSelectMock(array('e.data as eData')), $this->getSelectMock(array('someTable.field aS alias1')), $this->getSelectMock(array('someTable.field2 AS alias2'))); $tools = new QueryBuilderTools($selects); $this->assertEquals($expected, $tools->getUsedTableAliases($condition)); }