9.5/_query_builder_8php_source.html

<?php

declare(strict_types = 1);

namespace ‪TYPO3\CMS\Core\Database\Query;


/*

 * 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!

 */


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\QueryRestrictionContainerInterface;

use ‪TYPO3\CMS\Core\Utility\GeneralUtility;


class ‪QueryBuilder

{

    protected ‪$connection;


    protected ‪$concreteQueryBuilder;


    protected ‪$restrictionContainer;


    protected ‪$additionalRestrictions;


    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 ‪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

    {

        $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

    {

        $this->concreteQueryBuilder->rightJoin(

            $this->‪quoteIdentifier($fromAlias),

            $this->‪quoteIdentifier($join),

            $this->‪quoteIdentifier($alias),

            $condition

        );


        return $this;

    }


    public function set(string $key, $value, bool $createNamedParameter = true): QueryBuilder

    {

        $this->concreteQueryBuilder->set(

            $this->‪quoteIdentifier($key),

            $createNamedParameter ? $this->‪createNamedParameter($value) : $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 addcslashes($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) {

            list($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;

            $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;

                $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;

    }

}