QueryBuilder.php

Go to the documentation of this file.

<?php
 
declare(strict_types=1);
 
/*
 * This file is part of the TYPO3 CMS project.
 *
 * It is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License, either version 2
 * of the License, or any later version.
 *
 * For the full copyright and license information, please read the
 * LICENSE.txt file that was distributed with this source code.
 *
 * The TYPO3 project - inspiring people to share!
 */
 
namespace ‪TYPO3\CMS\Core\Database\Query;
 
use Doctrine\DBAL\Driver\Statement;
use Doctrine\DBAL\Platforms\MySqlPlatform;
use Doctrine\DBAL\Platforms\OraclePlatform;
use Doctrine\DBAL\Platforms\PostgreSqlPlatform;
use Doctrine\DBAL\Platforms\SqlitePlatform;
use Doctrine\DBAL\Platforms\SQLServerPlatform;
use Doctrine\DBAL\Query\Expression\CompositeExpression;
use ‪TYPO3\CMS\Core\Database\Connection;
use ‪TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
use ‪TYPO3\CMS\Core\Database\Query\Restriction\DefaultRestrictionContainer;
use ‪TYPO3\CMS\Core\Database\Query\Restriction\LimitToTablesRestrictionContainer;
use ‪TYPO3\CMS\Core\Database\Query\Restriction\QueryRestrictionContainerInterface;
use ‪TYPO3\CMS\Core\Database\Query\Restriction\QueryRestrictionInterface;
use ‪TYPO3\CMS\Core\Utility\GeneralUtility;
 
class ‪QueryBuilder
{
    protected ‪$connection;
 
    protected ‪$concreteQueryBuilder;
 
    protected ‪$restrictionContainer;
 
    protected ‪$additionalRestrictions;
 
    private ‪$restrictionsAppliedInJoinCondition = [];
 
    public function ‪__construct(
        ‪Connection ‪$connection,
        ‪QueryRestrictionContainerInterface ‪$restrictionContainer = null,
        \Doctrine\DBAL\Query\‪QueryBuilder ‪$concreteQueryBuilder = null,
        array ‪$additionalRestrictions = null
    ) {
        $this->connection = ‪$connection;
        $this->additionalRestrictions = ‪$additionalRestrictions ?: ‪$GLOBALS['TYPO3_CONF_VARS']['DB']['additionalQueryRestrictions'] ?? [];
        $this->‪setRestrictions(‪$restrictionContainer ?: GeneralUtility::makeInstance(DefaultRestrictionContainer::class));
        $this->concreteQueryBuilder = ‪$concreteQueryBuilder ?: GeneralUtility::makeInstance(\Doctrine\DBAL\Query\QueryBuilder::class, ‪$connection);
    }
 
    public function ‪getRestrictions()
    {
        return ‪$this->restrictionContainer;
    }
 
    public function ‪setRestrictions(‪QueryRestrictionContainerInterface ‪$restrictionContainer)
    {
        foreach ($this->additionalRestrictions as $restrictionClass => $options) {
            if (empty($options['disabled'])) {
                $restriction = GeneralUtility::makeInstance($restrictionClass);
                ‪$restrictionContainer->‪add($restriction);
            }
        }
        $this->restrictionContainer = ‪$restrictionContainer;
    }
 
    public function ‪limitRestrictionsToTables(array $tableAliases): void
    {
        $this->restrictionContainer = GeneralUtility::makeInstance(LimitToTablesRestrictionContainer::class)->addForTables($this->restrictionContainer, $tableAliases);
    }
 
    public function ‪resetRestrictions()
    {
        $this->‪setRestrictions(GeneralUtility::makeInstance(DefaultRestrictionContainer::class));
    }
 
    public function ‪expr(): ‪ExpressionBuilder
    {
        return $this->connection->getExpressionBuilder();
    }
 
    public function ‪getType(): int
    {
        return $this->concreteQueryBuilder->getType();
    }
 
    public function ‪getConnection(): Connection
    {
        return ‪$this->connection;
    }
 
    public function ‪getState(): int
    {
        return $this->concreteQueryBuilder->getState();
    }
 
    public function ‪getConcreteQueryBuilder(): \Doctrine\DBAL\Query\QueryBuilder
    {
        return ‪$this->concreteQueryBuilder;
    }
 
    public function ‪execute()
    {
        if ($this->‪getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) {
            return $this->concreteQueryBuilder->execute();
        }
 
        // Set additional query restrictions
        $originalWhereConditions = $this->‪addAdditionalWhereConditions();
 
        $result = $this->concreteQueryBuilder->execute();
 
        // Restore the original query conditions in case the user keeps
        // on modifying the state.
        $this->concreteQueryBuilder->add('where', $originalWhereConditions, false);
 
        return $result;
    }
 
    public function ‪getSQL(): string
    {
        if ($this->‪getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) {
            return $this->concreteQueryBuilder->getSQL();
        }
 
        // Set additional query restrictions
        $originalWhereConditions = $this->‪addAdditionalWhereConditions();
 
        $sql = $this->concreteQueryBuilder->getSQL();
 
        // Restore the original query conditions in case the user keeps
        // on modifying the state.
        $this->concreteQueryBuilder->add('where', $originalWhereConditions, false);
 
        return $sql;
    }
 
    public function ‪setParameter($key, $value, int $type = null): QueryBuilder
    {
        $this->concreteQueryBuilder->setParameter($key, $value, $type);
 
        return $this;
    }
 
    public function ‪setParameters(array $params, array $types = []): QueryBuilder
    {
        $this->concreteQueryBuilder->setParameters($params, $types);
 
        return $this;
    }
 
    public function ‪getParameters(): array
    {
        return $this->concreteQueryBuilder->getParameters();
    }
 
    public function ‪getParameter($key)
    {
        return $this->concreteQueryBuilder->getParameter($key);
    }
 
    public function ‪getParameterTypes(): array
    {
        return $this->concreteQueryBuilder->getParameterTypes();
    }
 
    public function ‪getParameterType($key)
    {
        return $this->concreteQueryBuilder->getParameterType($key);
    }
 
    public function ‪setFirstResult(int $firstResult): QueryBuilder
    {
        $this->concreteQueryBuilder->setFirstResult($firstResult);
 
        return $this;
    }
 
    public function ‪getFirstResult(): int
    {
        return (int)$this->concreteQueryBuilder->getFirstResult();
    }
 
    public function ‪setMaxResults(int $maxResults): QueryBuilder
    {
        $this->concreteQueryBuilder->setMaxResults($maxResults);
 
        return $this;
    }
 
    public function ‪getMaxResults(): int
    {
        return (int)$this->concreteQueryBuilder->getMaxResults();
    }
 
    public function ‪add(string $sqlPartName, $sqlPart, bool $append = false): QueryBuilder
    {
        $this->concreteQueryBuilder->add($sqlPartName, $sqlPart, $append);
 
        return $this;
    }
 
    public function ‪count(string $item): QueryBuilder
    {
        $countExpr = $this->‪getConnection()->getDatabasePlatform()->getCountExpression(
            $item === '*' ? $item : $this->‪quoteIdentifier($item)
        );
        $this->concreteQueryBuilder->‪select($countExpr);
 
        return $this;
    }
 
    public function ‪select(string ...$selects): QueryBuilder
    {
        $this->concreteQueryBuilder->select(...$this->‪quoteIdentifiersForSelect($selects));
 
        return $this;
    }
 
    public function ‪addSelect(string ...$selects): QueryBuilder
    {
        $this->concreteQueryBuilder->addSelect(...$this->‪quoteIdentifiersForSelect($selects));
 
        return $this;
    }
 
    public function ‪selectLiteral(string ...$selects): QueryBuilder
    {
        $this->concreteQueryBuilder->select(...$selects);
 
        return $this;
    }
 
    public function ‪addSelectLiteral(string ...$selects): QueryBuilder
    {
        $this->concreteQueryBuilder->addSelect(...$selects);
 
        return $this;
    }
 
    public function delete(string $delete, string $alias = null): QueryBuilder
    {
        $this->concreteQueryBuilder->delete(
            $this->‪quoteIdentifier($delete),
            empty($alias) ? $alias : $this->‪quoteIdentifier($alias)
        );
 
        return $this;
    }
 
    public function ‪update(string $update, string $alias = null): QueryBuilder
    {
        $this->concreteQueryBuilder->update(
            $this->‪quoteIdentifier($update),
            empty($alias) ? $alias : $this->‪quoteIdentifier($alias)
        );
 
        return $this;
    }
 
    public function ‪insert(string $insert): QueryBuilder
    {
        $this->concreteQueryBuilder->insert($this->‪quoteIdentifier($insert));
 
        return $this;
    }
 
    public function ‪from(string $from, string $alias = null): QueryBuilder
    {
        $this->concreteQueryBuilder->from(
            $this->‪quoteIdentifier($from),
            empty($alias) ? $alias : $this->‪quoteIdentifier($alias)
        );
 
        return $this;
    }
 
    public function ‪join(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
    {
        $this->concreteQueryBuilder->innerJoin(
            $this->‪quoteIdentifier($fromAlias),
            $this->‪quoteIdentifier($join),
            $this->‪quoteIdentifier($alias),
            $condition
        );
 
        return $this;
    }
 
    public function ‪innerJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
    {
        $this->concreteQueryBuilder->innerJoin(
            $this->‪quoteIdentifier($fromAlias),
            $this->‪quoteIdentifier($join),
            $this->‪quoteIdentifier($alias),
            $condition
        );
 
        return $this;
    }
 
    public function ‪leftJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
    {
        $condition = $this->‪expr()->‪andX(
            $condition,
            $this->restrictionContainer->buildExpression([$alias ?? $join => $join], $this->expr())
        );
        $this->restrictionsAppliedInJoinCondition[] = $alias ?? $join;
 
        $this->concreteQueryBuilder->leftJoin(
            $this->‪quoteIdentifier($fromAlias),
            $this->‪quoteIdentifier($join),
            $this->‪quoteIdentifier($alias),
            $condition
        );
 
        return $this;
    }
 
    public function ‪rightJoin(string $fromAlias, string $join, string $alias, string $condition = null): QueryBuilder
    {
        $fromTable = $fromAlias;
        // find the table belonging to the $fromAlias, if it's an alias at all
        foreach ($this->‪getQueryPart('from') ?: [] as $from) {
            if (isset($from['alias']) && $this->‪unquoteSingleIdentifier($from['alias']) === $fromAlias) {
                $fromTable = $this->‪unquoteSingleIdentifier($from['table']);
                break;
            }
        }
 
        $condition = $this->‪expr()->‪andX(
            $condition,
            $this->restrictionContainer->buildExpression([$fromAlias => $fromTable], $this->expr())
        );
        $this->restrictionsAppliedInJoinCondition[] = $fromAlias;
 
        $this->concreteQueryBuilder->rightJoin(
            $this->‪quoteIdentifier($fromAlias),
            $this->‪quoteIdentifier($join),
            $this->‪quoteIdentifier($alias),
            $condition
        );
 
        return $this;
    }
 
    public function set(string $key, $value, bool $createNamedParameter = true, int $type = \PDO::PARAM_STR): QueryBuilder
    {
        $this->concreteQueryBuilder->set(
            $this->‪quoteIdentifier($key),
            $createNamedParameter ? $this->‪createNamedParameter($value, $type) : $value
        );
 
        return $this;
    }
 
    public function ‪where(...$predicates): QueryBuilder
    {
        $this->concreteQueryBuilder->where(...$predicates);
 
        return $this;
    }
 
    public function ‪andWhere(...$where): QueryBuilder
    {
        $this->concreteQueryBuilder->andWhere(...$where);
 
        return $this;
    }
 
    public function ‪orWhere(...$where): QueryBuilder
    {
        $this->concreteQueryBuilder->orWhere(...$where);
 
        return $this;
    }
 
    public function ‪groupBy(...$groupBy): QueryBuilder
    {
        $this->concreteQueryBuilder->groupBy(...$this->‪quoteIdentifiers($groupBy));
 
        return $this;
    }
 
    public function ‪addGroupBy(...$groupBy): QueryBuilder
    {
        $this->concreteQueryBuilder->addGroupBy(...$this->‪quoteIdentifiers($groupBy));
 
        return $this;
    }
 
    public function ‪setValue(string $column, $value, bool $createNamedParameter = true): QueryBuilder
    {
        $this->concreteQueryBuilder->setValue(
            $this->‪quoteIdentifier($column),
            $createNamedParameter ? $this->‪createNamedParameter($value) : $value
        );
 
        return $this;
    }
 
    public function ‪values(array $values, bool $createNamedParameters = true): QueryBuilder
    {
        if ($createNamedParameters === true) {
            foreach ($values as &$value) {
                $value = $this->‪createNamedParameter($value);
            }
        }
 
        $this->concreteQueryBuilder->values($this->‪quoteColumnValuePairs($values));
 
        return $this;
    }
 
    public function ‪having(...$having): QueryBuilder
    {
        $this->concreteQueryBuilder->having(...$having);
        return $this;
    }
 
    public function ‪andHaving(...$having): QueryBuilder
    {
        $this->concreteQueryBuilder->andHaving(...$having);
 
        return $this;
    }
 
    public function ‪orHaving(...$having): QueryBuilder
    {
        $this->concreteQueryBuilder->orHaving(...$having);
 
        return $this;
    }
 
    public function ‪orderBy(string $fieldName, string $order = null): QueryBuilder
    {
        $this->concreteQueryBuilder->orderBy($this->connection->quoteIdentifier($fieldName), $order);
 
        return $this;
    }
 
    public function ‪addOrderBy(string $fieldName, string $order = null): QueryBuilder
    {
        $this->concreteQueryBuilder->addOrderBy($this->connection->quoteIdentifier($fieldName), $order);
 
        return $this;
    }
 
    public function ‪getQueryPart(string $queryPartName)
    {
        return $this->concreteQueryBuilder->getQueryPart($queryPartName);
    }
 
    public function ‪getQueryParts(): array
    {
        return $this->concreteQueryBuilder->getQueryParts();
    }
 
    public function ‪resetQueryParts(array $queryPartNames = null): QueryBuilder
    {
        $this->concreteQueryBuilder->resetQueryParts($queryPartNames);
 
        return $this;
    }
 
    public function ‪resetQueryPart($queryPartName): QueryBuilder
    {
        $this->concreteQueryBuilder->resetQueryPart($queryPartName);
 
        return $this;
    }
 
    public function ‪__toString(): string
    {
        return $this->‪getSQL();
    }
 
    public function ‪createNamedParameter($value, int $type = \PDO::PARAM_STR, string $placeHolder = null): string
    {
        return $this->concreteQueryBuilder->createNamedParameter($value, $type, $placeHolder);
    }
 
    public function ‪createPositionalParameter($value, int $type = \PDO::PARAM_STR): string
    {
        return $this->concreteQueryBuilder->createPositionalParameter($value, $type);
    }
 
    public function ‪escapeLikeWildcards(string $value): string
    {
        return $this->connection->escapeLikeWildcards($value);
    }
 
    public function ‪quote($input, int $type = null)
    {
        return $this->‪getConnection()->quote($input, $type);
    }
 
    public function ‪quoteIdentifier(string $identifier): string
    {
        return $this->‪getConnection()->‪quoteIdentifier($identifier);
    }
 
    public function ‪quoteIdentifiers(array $input): array
    {
        return $this->‪getConnection()->‪quoteIdentifiers($input);
    }
 
    public function ‪quoteIdentifiersForSelect(array $input): array
    {
        foreach ($input as &$select) {
            [$fieldName, $alias, $suffix] = array_pad(
                ‪GeneralUtility::trimExplode(
                    ' AS ',
                    str_ireplace(' as ', ' AS ', $select),
                    true,
                    3
                ),
                3,
                null
            );
            if (!empty($suffix)) {
                throw new \InvalidArgumentException(
                    'QueryBuilder::quoteIdentifiersForSelect() could not parse the select ' . $select . '.',
                    1461170686
                );
            }
 
            // The SQL * operator must not be quoted. As it can only occur either by itself
            // or preceded by a tablename (tablename.*) check if the last character of a select
            // expression is the * and quote only prepended table name. In all other cases the
            // full expression is being quoted.
            if (substr($fieldName, -2) === '.*') {
                $select = $this->‪quoteIdentifier(substr($fieldName, 0, -2)) . '.*';
            } elseif ($fieldName !== '*') {
                $select = $this->‪quoteIdentifier($fieldName);
            }
 
            // Quote the alias for the current fieldName, if given
            if (!empty($alias)) {
                $select .= ' AS ' . $this->‪quoteIdentifier($alias);
            }
        }
        return $input;
    }
 
    public function ‪quoteColumnValuePairs(array $input): array
    {
        return $this->‪getConnection()->‪quoteColumnValuePairs($input);
    }
 
    public function ‪castFieldToTextType(string $fieldName): string
    {
        $databasePlatform = $this->connection->getDatabasePlatform();
        // https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#function_convert
        if ($databasePlatform instanceof MySqlPlatform) {
            return sprintf('CONVERT(%s, CHAR)', $this->connection->quoteIdentifier($fieldName));
        }
        // https://www.postgresql.org/docs/current/sql-createcast.html
        if ($databasePlatform instanceof PostgreSqlPlatform) {
            return sprintf('%s::text', $this->connection->quoteIdentifier($fieldName));
        }
        // https://www.sqlite.org/lang_expr.html#castexpr
        if ($databasePlatform instanceof SqlitePlatform) {
            return sprintf('CAST(%s as TEXT)', $this->connection->quoteIdentifier($fieldName));
        }
        // https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#implicit-conversions
        if ($databasePlatform instanceof SQLServerPlatform) {
            return sprintf('CAST(%s as VARCHAR)', $this->connection->quoteIdentifier($fieldName));
        }
        // https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj33562.html
        if ($databasePlatform instanceof OraclePlatform) {
            return sprintf('CAST(%s as VARCHAR)', $this->connection->quoteIdentifier($fieldName));
        }
 
        throw new \RuntimeException(
            sprintf(
                '%s is not implemented for the used database platform "%s", yet!',
                __METHOD__,
                get_class($this->connection->getDatabasePlatform())
            ),
            1584637096
        );
    }
 
    protected function ‪unquoteSingleIdentifier(string $identifier): string
    {
        $identifier = trim($identifier);
        $platform = $this->‪getConnection()->getDatabasePlatform();
        if ($platform instanceof SQLServerPlatform) {
            // mssql quotes identifiers with [ and ], not a single character
            $identifier = ltrim($identifier, '[');
            $identifier = rtrim($identifier, ']');
        } else {
            $quoteChar = $platform->getIdentifierQuoteCharacter();
            $identifier = trim($identifier, $quoteChar);
            $identifier = str_replace($quoteChar . $quoteChar, $quoteChar, $identifier);
        }
        return $identifier;
    }
 
    protected function ‪getQueriedTables(): array
    {
        $queriedTables = [];
 
        // Loop through all FROM tables
        foreach ($this->‪getQueryPart('from') as $from) {
            $tableName = $this->‪unquoteSingleIdentifier($from['table']);
            $tableAlias = isset($from['alias']) ? $this->‪unquoteSingleIdentifier($from['alias']) : $tableName;
            if (!in_array($tableAlias, $this->restrictionsAppliedInJoinCondition, true)) {
                $queriedTables[$tableAlias] = $tableName;
            }
        }
 
        // Loop through all JOIN tables
        foreach ($this->‪getQueryPart('join') as $fromTable => $joins) {
            foreach ($joins as $join) {
                $tableName = $this->‪unquoteSingleIdentifier($join['joinTable']);
                $tableAlias = isset($join['joinAlias']) ? $this->‪unquoteSingleIdentifier($join['joinAlias']) : $tableName;
                if (!in_array($tableAlias, $this->restrictionsAppliedInJoinCondition, true)) {
                    $queriedTables[$tableAlias] = $tableName;
                }
            }
        }
 
        return $queriedTables;
    }
 
    protected function ‪addAdditionalWhereConditions()
    {
        $originalWhereConditions = $this->concreteQueryBuilder->getQueryPart('where');
        $expression = $this->restrictionContainer->buildExpression($this->‪getQueriedTables(), $this->‪expr());
        // This check would be obsolete, as the composite expression would not add empty expressions anyway
        // But we keep it here to only clone the previous state, in case we really will change it.
        // Once we remove this state preserving functionality, we can remove the count check here
        // and just add the expression to the query builder.
        if ($expression->count() > 0) {
            if ($originalWhereConditions instanceof CompositeExpression) {
                // Save the original query conditions so we can restore
                // them after the query has been built.
                $originalWhereConditions = clone $originalWhereConditions;
            }
            $this->concreteQueryBuilder->andWhere($expression);
        }
 
        return $originalWhereConditions;
    }
 
    public function ‪__clone()
    {
        $this->concreteQueryBuilder = clone ‪$this->concreteQueryBuilder;
        $this->restrictionContainer = clone ‪$this->restrictionContainer;
    }
}