QueryBuilder.php

<?php

declare(strict_types=1);

namespace Doctrine\DBAL\Query;

use Doctrine\DBAL\ArrayParameterType;
use Doctrine\DBAL\Cache\QueryCacheProfile;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Exception;
use Doctrine\DBAL\ParameterType;
use Doctrine\DBAL\Query\Exception\NonUniqueAlias;
use Doctrine\DBAL\Query\Exception\UnknownAlias;
use Doctrine\DBAL\Query\Expression\CompositeExpression;
use Doctrine\DBAL\Query\Expression\ExpressionBuilder;
use Doctrine\DBAL\Query\ForUpdate\ConflictResolutionMode;
use Doctrine\DBAL\Result;
use Doctrine\DBAL\Statement;
use Doctrine\DBAL\Types\Type;

use function array_filter;
use function array_intersect;
use function array_key_exists;
use function array_keys;
use function array_merge;
use function array_unshift;
use function count;
use function implode;
use function is_object;
use function sprintf;
use function substr;

/**
 * QueryBuilder class is responsible to dynamically create SQL queries.
 *
 * Important: Verify that every feature you use will work with your database vendor.
 * SQL Query Builder does not attempt to validate the generated SQL at all.
 *
 * The query builder does no validation whatsoever if certain features even work with the
 * underlying database vendor. Limit queries and joins are NOT applied to UPDATE and DELETE statements
 * even if some vendors such as MySQL support it.
 *
 * @phpstan-import-type WrapperParameterTypeArray from Connection
 */
class QueryBuilder
{
    /**
     * The complete SQL string for this query.
     */
    private ?string $sql = null;

    /**
     * The query parameters.
     *
     * @var list<mixed>|array<string, mixed>
     */
    private array $params = [];

    /**
     * The parameter type map of this query.
     *
     * @phpstan-var WrapperParameterTypeArray
     */
    private array $types = [];

    /**
     * The type of query this is. Can be select, update or delete.
     */
    private QueryType $type = QueryType::SELECT;

    /**
     * The index of the first result to retrieve.
     */
    private int $firstResult = 0;

    /**
     * The maximum number of results to retrieve or NULL to retrieve all results.
     */
    private ?int $maxResults = null;

    /**
     * The counter of bound parameters used with {@see bindValue).
     *
     * @var int<0, max>
     */
    private int $boundCounter = 0;

    /**
     * The SELECT parts of the query.
     *
     * @var string[]
     */
    private array $select = [];

    /**
     * Whether this is a SELECT DISTINCT query.
     */
    private bool $distinct = false;

    /**
     * The FROM parts of a SELECT query.
     *
     * @var From[]
     */
    private array $from = [];

    /**
     * The table name for an INSERT, UPDATE or DELETE query.
     */
    private ?string $table = null;

    /**
     * The list of joins, indexed by from alias.
     *
     * @var array<string, Join[]>
     */
    private array $join = [];

    /**
     * The SET parts of an UPDATE query.
     *
     * @var string[]
     */
    private array $set = [];

    /**
     * The WHERE part of a SELECT, UPDATE or DELETE query.
     */
    private string|CompositeExpression|null $where = null;

    /**
     * The GROUP BY part of a SELECT query.
     *
     * @var string[]
     */
    private array $groupBy = [];

    /**
     * The HAVING part of a SELECT query.
     */
    private string|CompositeExpression|null $having = null;

    /**
     * The ORDER BY parts of a SELECT query.
     *
     * @var string[]
     */
    private array $orderBy = [];

    private ?ForUpdate $forUpdate = null;

    /**
     * The values of an INSERT query.
     *
     * @var array<string, mixed>
     */
    private array $values = [];

    /**
     * The QueryBuilder for the union parts.
     *
     * @var Union[]
     */
    private array $unionParts = [];

    /**
     * The common table expression parts.
     *
     * @var CommonTableExpression[]
     */
    private array $commonTableExpressions = [];

    /**
     * The query cache profile used for caching results.
     */
    private ?QueryCacheProfile $resultCacheProfile = null;

    /**
     * Initializes a new <tt>QueryBuilder</tt>.
     *
     * @param Connection $connection The DBAL Connection.
     */
    public function __construct(private readonly Connection $connection)
    {
    }

    /**
     * Gets an ExpressionBuilder used for object-oriented construction of query expressions.
     * This producer method is intended for convenient inline usage. Example:
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u')
     *         ->from('users', 'u')
     *         ->where($qb->expr()->eq('u.id', 1));
     * </code>
     *
     * For more complex expression construction, consider storing the expression
     * builder object in a local variable.
     */
    public function expr(): ExpressionBuilder
    {
        return $this->connection->createExpressionBuilder();
    }

    /**
     * Returns a fresh query builder instance that can be used to build a subquery.
     */
    public function sub(): self
    {
        return $this->connection->createQueryBuilder();
    }

    /**
     * Prepares and executes an SQL query and returns the first row of the result
     * as an associative array.
     *
     * @return array<string, mixed>|false False is returned if no rows are found.
     *
     * @throws Exception
     */
    public function fetchAssociative(): array|false
    {
        return $this->executeQuery()->fetchAssociative();
    }

    /**
     * Prepares and executes an SQL query and returns the first row of the result
     * as a numerically indexed array.
     *
     * @return array<int, mixed>|false False is returned if no rows are found.
     *
     * @throws Exception
     */
    public function fetchNumeric(): array|false
    {
        return $this->executeQuery()->fetchNumeric();
    }

    /**
     * Prepares and executes an SQL query and returns the value of a single column
     * of the first row of the result.
     *
     * @return mixed|false False is returned if no rows are found.
     *
     * @throws Exception
     */
    public function fetchOne(): mixed
    {
        return $this->executeQuery()->fetchOne();
    }

    /**
     * Prepares and executes an SQL query and returns the result as an array of numeric arrays.
     *
     * @return array<int,array<int,mixed>>
     *
     * @throws Exception
     */
    public function fetchAllNumeric(): array
    {
        return $this->executeQuery()->fetchAllNumeric();
    }

    /**
     * Prepares and executes an SQL query and returns the result as an array of associative arrays.
     *
     * @return array<int,array<string,mixed>>
     *
     * @throws Exception
     */
    public function fetchAllAssociative(): array
    {
        return $this->executeQuery()->fetchAllAssociative();
    }

    /**
     * Prepares and executes an SQL query and returns the result as an associative array with the keys
     * mapped to the first column and the values mapped to the second column.
     *
     * @return array<mixed,mixed>
     *
     * @throws Exception
     */
    public function fetchAllKeyValue(): array
    {
        return $this->executeQuery()->fetchAllKeyValue();
    }

    /**
     * Prepares and executes an SQL query and returns the result as an associative array with the keys mapped
     * to the first column and the values being an associative array representing the rest of the columns
     * and their values.
     *
     * @return array<mixed,array<string,mixed>>
     *
     * @throws Exception
     */
    public function fetchAllAssociativeIndexed(): array
    {
        return $this->executeQuery()->fetchAllAssociativeIndexed();
    }

    /**
     * Prepares and executes an SQL query and returns the result as an array of the first column values.
     *
     * @return array<int,mixed>
     *
     * @throws Exception
     */
    public function fetchFirstColumn(): array
    {
        return $this->executeQuery()->fetchFirstColumn();
    }

    /**
     * Executes an SQL query (SELECT) and returns a Result.
     *
     * @throws Exception
     */
    public function executeQuery(): Result
    {
        return $this->connection->executeQuery(
            $this->getSQL(),
            $this->params,
            $this->types,
            $this->resultCacheProfile,
        );
    }

    /**
     * Executes an SQL statement and returns the number of affected rows.
     *
     * Should be used for INSERT, UPDATE and DELETE
     *
     * @return int|numeric-string The number of affected rows.
     *
     * @throws Exception
     */
    public function executeStatement(): int|string
    {
        return $this->connection->executeStatement($this->getSQL(), $this->params, $this->types);
    }

    /**
     * Gets the complete SQL string formed by the current specifications of this QueryBuilder.
     *
     * <code>
     *     $qb = $em->createQueryBuilder()
     *         ->select('u')
     *         ->from('User', 'u')
     *     echo $qb->getSQL(); // SELECT u FROM User u
     * </code>
     *
     * @return string The SQL query string.
     *
     * @throws Exception
     */
    public function getSQL(): string
    {
        return $this->sql ??= match ($this->type) {
            QueryType::INSERT => $this->getSQLForInsert(),
            QueryType::DELETE => $this->getSQLForDelete(),
            QueryType::UPDATE => $this->getSQLForUpdate(),
            QueryType::SELECT => $this->getSQLForSelect(),
            QueryType::UNION  => $this->getSQLForUnion(),
        };
    }

    /**
     * Sets a query parameter for the query being constructed.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u')
     *         ->from('users', 'u')
     *         ->where('u.id = :user_id')
     *         ->setParameter('user_id', 1);
     * </code>
     *
     * @param int<0, max>|string $key Parameter position or name
     *
     * @return $this This QueryBuilder instance.
     */
    public function setParameter(
        int|string $key,
        mixed $value,
        string|ParameterType|Type|ArrayParameterType $type = ParameterType::STRING,
    ): self {
        $this->params[$key] = $value;
        $this->types[$key]  = $type;

        return $this;
    }

    /**
     * Sets a collection of query parameters for the query being constructed.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u')
     *         ->from('users', 'u')
     *         ->where('u.id = :user_id1 OR u.id = :user_id2')
     *         ->setParameters(array(
     *             'user_id1' => 1,
     *             'user_id2' => 2
     *         ));
     * </code>
     *
     * @param list<mixed>|array<string, mixed> $params
     * @phpstan-param WrapperParameterTypeArray $types
     *
     * @return $this This QueryBuilder instance.
     */
    public function setParameters(array $params, array $types = []): self
    {
        $this->params = $params;
        $this->types  = $types;

        return $this;
    }

    /**
     * Gets all defined query parameters for the query being constructed indexed by parameter index or name.
     *
     * @return list<mixed>|array<string, mixed> The currently defined query parameters
     */
    public function getParameters(): array
    {
        return $this->params;
    }

    /**
     * Gets a (previously set) query parameter of the query being constructed.
     *
     * @param string|int $key The key (index or name) of the bound parameter.
     *
     * @return mixed The value of the bound parameter.
     */
    public function getParameter(string|int $key): mixed
    {
        return $this->params[$key] ?? null;
    }

    /**
     * Gets all defined query parameter types for the query being constructed indexed by parameter index or name.
     *
     * @phpstan-return WrapperParameterTypeArray
     */
    public function getParameterTypes(): array
    {
        return $this->types;
    }

    /**
     * Gets a (previously set) query parameter type of the query being constructed.
     *
     * @param int|string $key The key of the bound parameter type
     */
    public function getParameterType(int|string $key): string|ParameterType|Type|ArrayParameterType
    {
        return $this->types[$key] ?? ParameterType::STRING;
    }

    /**
     * Sets the position of the first result to retrieve (the "offset").
     *
     * @param int $firstResult The first result to return.
     *
     * @return $this This QueryBuilder instance.
     */
    public function setFirstResult(int $firstResult): self
    {
        $this->firstResult = $firstResult;

        $this->sql = null;

        return $this;
    }

    /**
     * Gets the position of the first result the query object was set to retrieve (the "offset").
     *
     * @return int The position of the first result.
     */
    public function getFirstResult(): int
    {
        return $this->firstResult;
    }

    /**
     * Sets the maximum number of results to retrieve (the "limit").
     *
     * @param int|null $maxResults The maximum number of results to retrieve or NULL to retrieve all results.
     *
     * @return $this This QueryBuilder instance.
     */
    public function setMaxResults(?int $maxResults): self
    {
        $this->maxResults = $maxResults;

        $this->sql = null;

        return $this;
    }

    /**
     * Gets the maximum number of results the query object was set to retrieve (the "limit").
     * Returns NULL if all results will be returned.
     *
     * @return int|null The maximum number of results.
     */
    public function getMaxResults(): ?int
    {
        return $this->maxResults;
    }

    /**
     * Locks the queried rows for a subsequent update.
     *
     * @return $this
     */
    public function forUpdate(ConflictResolutionMode $conflictResolutionMode = ConflictResolutionMode::ORDINARY): self
    {
        $this->forUpdate = new ForUpdate($conflictResolutionMode);

        $this->sql = null;

        return $this;
    }

    /**
     * Specifies union parts to be used to build a UNION query.
     * Replaces any previously specified parts.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->union('SELECT 1 AS field1')
     *         ->addUnion('SELECT 2 AS field1')
     *         ->addUnion('SELECT 3 AS field1');
     * </code>
     *
     * @return $this
     */
    public function union(string|QueryBuilder $part): self
    {
        $this->type = QueryType::UNION;

        $this->unionParts = [new Union($part)];

        $this->sql = null;

        return $this;
    }

    /**
     * Add parts to be used to build a UNION query.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->union('SELECT 1 AS field1')
     *         ->addUnion('SELECT 2 AS field1')
     *         ->addUnion('SELECT 3 AS field1');
     * </code>
     *
     * @return $this
     *
     * @throws QueryException
     */
    public function addUnion(string|QueryBuilder $part, UnionType $type = UnionType::DISTINCT): self
    {
        $this->type = QueryType::UNION;

        if (count($this->unionParts) === 0) {
            throw new QueryException('No initial UNION part set, use union() to set one first.');
        }

        $this->unionParts[] = new Union($part, $type);

        $this->sql = null;

        return $this;
    }

    /**
     * Add a Common Table Expression to be used for a select query.
     *
     * <code>
     *     // WITH cte_name AS (SELECT id AS column1 FROM table_a)
     *     $qb = $conn->createQueryBuilder()
     *         ->with('cte_name', 'SELECT id AS column1 FROM table_a');
     *
     *     // WITH cte_name(column1) AS (SELECT id AS column1 FROM table_a)
     *     $qb = $conn->createQueryBuilder()
     *         ->with('cte_name', 'SELECT id AS column1 FROM table_a', ['column1']);
     * </code>
     *
     * @param string        $name    The name of the CTE
     * @param string[]|null $columns The optional columns list to select in the CTE.
     *                               If not provided, the columns are inferred from the CTE.
     *
     * @return $this This QueryBuilder instance.
     *
     * @throws QueryException Setting an empty array as columns is not allowed.
     */
    public function with(string $name, string|QueryBuilder $part, ?array $columns = null): self
    {
        $this->commonTableExpressions[] = new CommonTableExpression($name, $part, $columns);

        $this->sql = null;

        return $this;
    }

    /**
     * Specifies an item that is to be returned in the query result.
     * Replaces any previously specified selections, if any.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u.id', 'p.id')
     *         ->from('users', 'u')
     *         ->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id');
     * </code>
     *
     * @param string ...$expressions The selection expressions.
     *
     * @return $this This QueryBuilder instance.
     */
    public function select(string ...$expressions): self
    {
        $this->type = QueryType::SELECT;

        $this->select = $expressions;

        $this->sql = null;

        return $this;
    }

    /**
     * Adds or removes DISTINCT to/from the query.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u.id')
     *         ->distinct()
     *         ->from('users', 'u')
     * </code>
     *
     * @return $this This QueryBuilder instance.
     */
    public function distinct(bool $distinct = true): self
    {
        $this->distinct = $distinct;
        $this->sql      = null;

        return $this;
    }

    /**
     * Adds an item that is to be returned in the query result.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u.id')
     *         ->addSelect('p.id')
     *         ->from('users', 'u')
     *         ->leftJoin('u', 'phonenumbers', 'u.id = p.user_id');
     * </code>
     *
     * @param string $expression     The selection expression.
     * @param string ...$expressions Additional selection expressions.
     *
     * @return $this This QueryBuilder instance.
     */
    public function addSelect(string $expression, string ...$expressions): self
    {
        $this->type = QueryType::SELECT;

        $this->select = array_merge($this->select, [$expression], $expressions);

        $this->sql = null;

        return $this;
    }

    /**
     * Turns the query being built into a bulk delete query that ranges over
     * a certain table.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->delete('users u')
     *         ->where('u.id = :user_id')
     *         ->setParameter(':user_id', 1);
     * </code>
     *
     * @param string $table The table whose rows are subject to the deletion.
     *
     * @return $this This QueryBuilder instance.
     */
    public function delete(string $table): self
    {
        $this->type = QueryType::DELETE;

        $this->table = $table;

        $this->sql = null;

        return $this;
    }

    /**
     * Turns the query being built into a bulk update query that ranges over
     * a certain table
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->update('counters c')
     *         ->set('c.value', 'c.value + 1')
     *         ->where('c.id = ?');
     * </code>
     *
     * @param string $table The table whose rows are subject to the update.
     *
     * @return $this This QueryBuilder instance.
     */
    public function update(string $table): self
    {
        $this->type = QueryType::UPDATE;

        $this->table = $table;

        $this->sql = null;

        return $this;
    }

    /**
     * Turns the query being built into an insert query that inserts into
     * a certain table
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->insert('users')
     *         ->values(
     *             array(
     *                 'name' => '?',
     *                 'password' => '?'
     *             )
     *         );
     * </code>
     *
     * @param string $table The table into which the rows should be inserted.
     *
     * @return $this This QueryBuilder instance.
     */
    public function insert(string $table): self
    {
        $this->type = QueryType::INSERT;

        $this->table = $table;

        $this->sql = null;

        return $this;
    }

    /**
     * Creates and adds a query root corresponding to the table identified by the
     * given alias, forming a cartesian product with any existing query roots.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u.id')
     *         ->from('users', 'u')
     * </code>
     *
     * @param string      $table The table.
     * @param string|null $alias The alias of the table.
     *
     * @return $this This QueryBuilder instance.
     */
    public function from(string $table, ?string $alias = null): self
    {
        $this->from[] = new From($table, $alias);

        $this->sql = null;

        return $this;
    }

    /**
     * Creates and adds a join to the query.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u.name')
     *         ->from('users', 'u')
     *         ->join('u', 'phonenumbers', 'p', 'p.is_primary = 1');
     * </code>
     *
     * @param string $fromAlias The alias that points to a from clause.
     * @param string $join      The table name to join.
     * @param string $alias     The alias of the join table.
     * @param string $condition The condition for the join.
     *
     * @return $this This QueryBuilder instance.
     */
    public function join(string $fromAlias, string $join, string $alias, ?string $condition = null): self
    {
        return $this->innerJoin($fromAlias, $join, $alias, $condition);
    }

    /**
     * Creates and adds a join to the query.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u.name')
     *         ->from('users', 'u')
     *         ->innerJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');
     * </code>
     *
     * @param string $fromAlias The alias that points to a from clause.
     * @param string $join      The table name to join.
     * @param string $alias     The alias of the join table.
     * @param string $condition The condition for the join.
     *
     * @return $this This QueryBuilder instance.
     */
    public function innerJoin(string $fromAlias, string $join, string $alias, ?string $condition = null): self
    {
        $this->join[$fromAlias][] = Join::inner($join, $alias, $condition);

        $this->sql = null;

        return $this;
    }

    /**
     * Creates and adds a left join to the query.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u.name')
     *         ->from('users', 'u')
     *         ->leftJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');
     * </code>
     *
     * @param string $fromAlias The alias that points to a from clause.
     * @param string $join      The table name to join.
     * @param string $alias     The alias of the join table.
     * @param string $condition The condition for the join.
     *
     * @return $this This QueryBuilder instance.
     */
    public function leftJoin(string $fromAlias, string $join, string $alias, ?string $condition = null): self
    {
        $this->join[$fromAlias][] = Join::left($join, $alias, $condition);

        $this->sql = null;

        return $this;
    }

    /**
     * Creates and adds a right join to the query.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u.name')
     *         ->from('users', 'u')
     *         ->rightJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');
     * </code>
     *
     * @param string $fromAlias The alias that points to a from clause.
     * @param string $join      The table name to join.
     * @param string $alias     The alias of the join table.
     * @param string $condition The condition for the join.
     *
     * @return $this This QueryBuilder instance.
     */
    public function rightJoin(string $fromAlias, string $join, string $alias, ?string $condition = null): self
    {
        $this->join[$fromAlias][] = Join::right($join, $alias, $condition);

        $this->sql = null;

        return $this;
    }

    /**
     * Sets a new value for a column in a bulk update query.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->update('counters c')
     *         ->set('c.value', 'c.value + 1')
     *         ->where('c.id = ?');
     * </code>
     *
     * @param string $key   The column to set.
     * @param string $value The value, expression, placeholder, etc.
     *
     * @return $this This QueryBuilder instance.
     */
    public function set(string $key, string $value): self
    {
        $this->set[] = $key . ' = ' . $value;

        $this->sql = null;

        return $this;
    }

    /**
     * Specifies one or more restrictions to the query result.
     * Replaces any previously specified restrictions, if any.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('c.value')
     *         ->from('counters', 'c')
     *         ->where('c.id = ?');
     *
     *     // You can optionally programmatically build and/or expressions
     *     $qb = $conn->createQueryBuilder();
     *
     *     $or = $qb->expr()->orx();
     *     $or->add($qb->expr()->eq('c.id', 1));
     *     $or->add($qb->expr()->eq('c.id', 2));
     *
     *     $qb->update('counters c')
     *         ->set('c.value', 'c.value + 1')
     *         ->where($or);
     * </code>
     *
     * @param string|CompositeExpression $predicate     The WHERE clause predicate.
     * @param string|CompositeExpression ...$predicates Additional WHERE clause predicates.
     *
     * @return $this This QueryBuilder instance.
     */
    public function where(string|CompositeExpression $predicate, string|CompositeExpression ...$predicates): self
    {
        $this->where = $this->createPredicate($predicate, ...$predicates);

        $this->sql = null;

        return $this;
    }

    /**
     * Adds one or more restrictions to the query results, forming a logical
     * conjunction with any previously specified restrictions.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u')
     *         ->from('users', 'u')
     *         ->where('u.username LIKE ?')
     *         ->andWhere('u.is_active = 1');
     * </code>
     *
     * @see where()
     *
     * @param string|CompositeExpression $predicate     The predicate to append.
     * @param string|CompositeExpression ...$predicates Additional predicates to append.
     *
     * @return $this This QueryBuilder instance.
     */
    public function andWhere(string|CompositeExpression $predicate, string|CompositeExpression ...$predicates): self
    {
        $this->where = $this->appendToPredicate(
            $this->where,
            CompositeExpression::TYPE_AND,
            $predicate,
            ...$predicates,
        );

        $this->sql = null;

        return $this;
    }

    /**
     * Adds one or more restrictions to the query results, forming a logical
     * disjunction with any previously specified restrictions.
     *
     * <code>
     *     $qb = $em->createQueryBuilder()
     *         ->select('u.name')
     *         ->from('users', 'u')
     *         ->where('u.id = 1')
     *         ->orWhere('u.id = 2');
     * </code>
     *
     * @see where()
     *
     * @param string|CompositeExpression $predicate     The predicate to append.
     * @param string|CompositeExpression ...$predicates Additional predicates to append.
     *
     * @return $this This QueryBuilder instance.
     */
    public function orWhere(string|CompositeExpression $predicate, string|CompositeExpression ...$predicates): self
    {
        $this->where = $this->appendToPredicate($this->where, CompositeExpression::TYPE_OR, $predicate, ...$predicates);

        $this->sql = null;

        return $this;
    }

    /**
     * Specifies one or more grouping expressions over the results of the query.
     * Replaces any previously specified groupings, if any.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u.name')
     *         ->from('users', 'u')
     *         ->groupBy('u.id');
     * </code>
     *
     * @param string $expression     The grouping expression
     * @param string ...$expressions Additional grouping expressions
     *
     * @return $this This QueryBuilder instance.
     */
    public function groupBy(string $expression, string ...$expressions): self
    {
        $this->groupBy = array_merge([$expression], $expressions);

        $this->sql = null;

        return $this;
    }

    /**
     * Adds one or more grouping expressions to the query.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->select('u.name')
     *         ->from('users', 'u')
     *         ->groupBy('u.lastLogin')
     *         ->addGroupBy('u.createdAt');
     * </code>
     *
     * @param string $expression     The grouping expression
     * @param string ...$expressions Additional grouping expressions
     *
     * @return $this This QueryBuilder instance.
     */
    public function addGroupBy(string $expression, string ...$expressions): self
    {
        $this->groupBy = array_merge($this->groupBy, [$expression], $expressions);

        $this->sql = null;

        return $this;
    }

    /**
     * Sets a value for a column in an insert query.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->insert('users')
     *         ->values(
     *             array(
     *                 'name' => '?'
     *             )
     *         )
     *         ->setValue('password', '?');
     * </code>
     *
     * @param string $column The column into which the value should be inserted.
     * @param string $value  The value that should be inserted into the column.
     *
     * @return $this This QueryBuilder instance.
     */
    public function setValue(string $column, string $value): self
    {
        $this->values[$column] = $value;

        return $this;
    }

    /**
     * Specifies values for an insert query indexed by column names.
     * Replaces any previous values, if any.
     *
     * <code>
     *     $qb = $conn->createQueryBuilder()
     *         ->insert('users')
     *         ->values(
     *             array(
     *                 'name' => '?',
     *                 'password' => '?'
     *             )
     *         );
     * </code>
     *
     * @param array<string, mixed> $values The values to specify for the insert query indexed by column names.
     *
     * @return $this This QueryBuilder instance.
     */
    public function values(array $values): self
    {
        $this->values = $values;

        $this->sql = null;

        return $this;
    }

    /**
     * Specifies a restriction over the groups of the query.
     * Replaces any previous having restrictions, if any.
     *
     * @param string|CompositeExpression $predicate     The HAVING clause predicate.
     * @param string|CompositeExpression ...$predicates Additional HAVING clause predicates.
     *
     * @return $this This QueryBuilder instance.
     */
    public function having(string|CompositeExpression $predicate, string|CompositeExpression ...$predicates): self
    {
        $this->having = $this->createPredicate($predicate, ...$predicates);

        $this->sql = null;

        return $this;
    }

    /**
     * Adds a restriction over the groups of the query, forming a logical
     * conjunction with any existing having restrictions.
     *
     * @param string|CompositeExpression $predicate     The predicate to append.
     * @param string|CompositeExpression ...$predicates Additional predicates to append.
     *
     * @return $this This QueryBuilder instance.
     */
    public function andHaving(string|CompositeExpression $predicate, string|CompositeExpression ...$predicates): self
    {
        $this->having = $this->appendToPredicate(
            $this->having,
            CompositeExpression::TYPE_AND,
            $predicate,
            ...$predicates,
        );

        $this->sql = null;

        return $this;
    }

    /**
     * Adds a restriction over the groups of the query, forming a logical
     * disjunction with any existing having restrictions.
     *
     * @param string|CompositeExpression $predicate     The predicate to append.
     * @param string|CompositeExpression ...$predicates Additional predicates to append.
     *
     * @return $this This QueryBuilder instance.
     */
    public function orHaving(string|CompositeExpression $predicate, string|CompositeExpression ...$predicates): self
    {
        $this->having = $this->appendToPredicate(
            $this->having,
            CompositeExpression::TYPE_OR,
            $predicate,
            ...$predicates,
        );

        $this->sql = null;

        return $this;
    }

    /**
     * Creates a CompositeExpression from one or more predicates combined by the AND logic.
     */
    private function createPredicate(
        string|CompositeExpression $predicate,
        string|CompositeExpression ...$predicates,
    ): string|CompositeExpression {
        if (count($predicates) === 0) {
            return $predicate;
        }

        return new CompositeExpression(CompositeExpression::TYPE_AND, $predicate, ...$predicates);
    }

    /**
     * Appends the given predicates combined by the given type of logic to the current predicate.
     */
    private function appendToPredicate(
        string|CompositeExpression|null $currentPredicate,
        string $type,
        string|CompositeExpression ...$predicates,
    ): string|CompositeExpression {
        if ($currentPredicate instanceof CompositeExpression && $currentPredicate->getType() === $type) {
            return $currentPredicate->with(...$predicates);
        }

        if ($currentPredicate !== null) {
            array_unshift($predicates, $currentPredicate);
        } elseif (count($predicates) === 1) {
            return $predicates[0];
        }

        return new CompositeExpression($type, ...$predicates);
    }

    /**
     * Specifies an ordering for the query results.
     * Replaces any previously specified orderings, if any.
     *
     * @param string $sort  The ordering expression.
     * @param string $order The ordering direction.
     *
     * @return $this This QueryBuilder instance.
     */
    public function orderBy(string $sort, ?string $order = null): self
    {
        $orderBy = $sort;

        if ($order !== null) {
            $orderBy .= ' ' . $order;
        }

        $this->orderBy = [$orderBy];

        $this->sql = null;

        return $this;
    }

    /**
     * Adds an ordering to the query results.
     *
     * @param string $sort  The ordering expression.
     * @param string $order The ordering direction.
     *
     * @return $this This QueryBuilder instance.
     */
    public function addOrderBy(string $sort, ?string $order = null): self
    {
        $orderBy = $sort;

        if ($order !== null) {
            $orderBy .= ' ' . $order;
        }

        $this->orderBy[] = $orderBy;

        $this->sql = null;

        return $this;
    }

    /**
     * Resets the WHERE conditions for the query.
     *
     * @return $this This QueryBuilder instance.
     */
    public function resetWhere(): self
    {
        $this->where = null;
        $this->sql   = null;

        return $this;
    }

    /**
     * Resets the grouping for the query.
     *
     * @return $this This QueryBuilder instance.
     */
    public function resetGroupBy(): self
    {
        $this->groupBy = [];
        $this->sql     = null;

        return $this;
    }

    /**
     * Resets the HAVING conditions for the query.
     *
     * @return $this This QueryBuilder instance.
     */
    public function resetHaving(): self
    {
        $this->having = null;
        $this->sql    = null;

        return $this;
    }

    /**
     * Resets the ordering for the query.
     *
     * @return $this This QueryBuilder instance.
     */
    public function resetOrderBy(): self
    {
        $this->orderBy = [];
        $this->sql     = null;

        return $this;
    }

    /** @throws Exception */
    private function getSQLForSelect(): string
    {
        if (count($this->select) === 0) {
            throw new QueryException('No SELECT expressions given. Please use select() or addSelect().');
        }

        $databasePlatform = $this->connection->getDatabasePlatform();
        $selectParts      = [];
        if (count($this->commonTableExpressions) > 0) {
            $selectParts[] = $databasePlatform
                ->createWithSQLBuilder()
                ->buildSQL(...$this->commonTableExpressions);
        }

        $selectParts[] = $databasePlatform
            ->createSelectSQLBuilder()
            ->buildSQL(
                new SelectQuery(
                    $this->distinct,
                    $this->select,
                    $this->getFromClauses(),
                    $this->where !== null ? (string) $this->where : null,
                    $this->groupBy,
                    $this->having !== null ? (string) $this->having : null,
                    $this->orderBy,
                    new Limit($this->maxResults, $this->firstResult),
                    $this->forUpdate,
                ),
            );

        return implode(' ', $selectParts);
    }

    /**
     * @return array<string, string>
     *
     * @throws QueryException
     */
    private function getFromClauses(): array
    {
        $fromClauses  = [];
        $knownAliases = [];

        foreach ($this->from as $from) {
            if ($from->alias === null || $from->alias === $from->table) {
                $tableSql       = $from->table;
                $tableReference = $from->table;
            } else {
                $tableSql       = $from->table . ' ' . $from->alias;
                $tableReference = $from->alias;
            }

            $knownAliases[$tableReference] = true;

            $fromClauses[$tableReference] = $tableSql . $this->getSQLForJoins($tableReference, $knownAliases);
        }

        $this->verifyAllAliasesAreKnown($knownAliases);

        return $fromClauses;
    }

    /**
     * @param array<string, true> $knownAliases
     *
     * @throws QueryException
     */
    private function verifyAllAliasesAreKnown(array $knownAliases): void
    {
        foreach ($this->join as $fromAlias => $joins) {
            if (! isset($knownAliases[$fromAlias])) {
                throw UnknownAlias::new($fromAlias, array_keys($knownAliases));
            }
        }
    }

    /**
     * Converts this instance into an INSERT string in SQL.
     */
    private function getSQLForInsert(): string
    {
        return 'INSERT INTO ' . $this->table .
        ' (' . implode(', ', array_keys($this->values)) . ')' .
        ' VALUES(' . implode(', ', $this->values) . ')';
    }

    /**
     * Converts this instance into an UPDATE string in SQL.
     */
    private function getSQLForUpdate(): string
    {
        $query = 'UPDATE ' . $this->table
            . ' SET ' . implode(', ', $this->set);

        if ($this->where !== null) {
            $query .= ' WHERE ' . $this->where;
        }

        return $query;
    }

    /**
     * Converts this instance into a DELETE string in SQL.
     */
    private function getSQLForDelete(): string
    {
        $query = 'DELETE FROM ' . $this->table;

        if ($this->where !== null) {
            $query .= ' WHERE ' . $this->where;
        }

        return $query;
    }

    /**
     * Converts this instance into a UNION string in SQL.
     *
     * @throws Exception
     */
    private function getSQLForUnion(): string
    {
        $countUnions = count($this->unionParts);
        if ($countUnions < 2) {
            throw new QueryException(
                'Insufficient UNION parts give, need at least 2.'
                . ' Please use union() and addUnion() to set enough UNION parts.',
            );
        }

        $databasePlatform = $this->connection->getDatabasePlatform();
        $unionParts       = [];
        if (count($this->commonTableExpressions) > 0) {
            $unionParts[] = $databasePlatform
                ->createWithSQLBuilder()
                ->buildSQL(...$this->commonTableExpressions);
        }

        $unionParts[] = $databasePlatform
            ->createUnionSQLBuilder()
            ->buildSQL(
                new UnionQuery(
                    $this->unionParts,
                    $this->orderBy,
                    new Limit($this->maxResults, $this->firstResult),
                ),
            );

        return implode(' ', $unionParts);
    }

    /**
     * Gets a string representation of this QueryBuilder which corresponds to
     * the final SQL query being constructed.
     *
     * @return string The string representation of this QueryBuilder.
     *
     * @throws Exception
     */
    public function __toString(): string
    {
        return $this->getSQL();
    }

    /**
     * Creates a new named parameter and bind the value $value to it.
     *
     * This method provides a shortcut for {@see Statement::bindValue()}
     * when using prepared statements.
     *
     * The parameter $value specifies the value that you want to bind. If
     * $placeholder is not provided createNamedParameter() will automatically
     * create a placeholder for you. An automatic placeholder will be of the
     * name ':dcValue1', ':dcValue2' etc.
     *
     * Example:
     * <code>
     * $value = 2;
     * $q->eq( 'id', $q->createNamedParameter( $value ) );
     * $stmt = $q->executeQuery(); // executed with 'id = 2'
     * </code>
     *
     * @link http://www.zetacomponents.org
     *
     * @param string|null $placeHolder The name to bind with. The string must start with a colon ':'.
     *
     * @return string the placeholder name used.
     */
    public function createNamedParameter(
        mixed $value,
        string|ParameterType|Type|ArrayParameterType $type = ParameterType::STRING,
        ?string $placeHolder = null,
    ): string {
        if ($placeHolder === null) {
            $this->boundCounter++;
            $placeHolder = ':dcValue' . $this->boundCounter;
        }

        $this->setParameter(substr($placeHolder, 1), $value, $type);

        return $placeHolder;
    }

    /**
     * Creates a new positional parameter and bind the given value to it.
     *
     * Attention: If you are using positional parameters with the query builder you have
     * to be very careful to bind all parameters in the order they appear in the SQL
     * statement , otherwise they get bound in the wrong order which can lead to serious
     * bugs in your code.
     *
     * Example:
     * <code>
     *  $qb = $conn->createQueryBuilder();
     *  $qb->select('u.*')
     *     ->from('users', 'u')
     *     ->where('u.username = ' . $qb->createPositionalParameter('Foo', ParameterType::STRING))
     *     ->orWhere('u.username = ' . $qb->createPositionalParameter('Bar', ParameterType::STRING))
     * </code>
     */
    public function createPositionalParameter(
        mixed $value,
        string|ParameterType|Type|ArrayParameterType $type = ParameterType::STRING,
    ): string {
        $this->setParameter($this->boundCounter, $value, $type);
        $this->boundCounter++;

        return '?';
    }

    /**
     * @param array<string, true> $knownAliases
     *
     * @throws QueryException
     */
    private function getSQLForJoins(string $fromAlias, array &$knownAliases): string
    {
        $sql = '';

        if (! isset($this->join[$fromAlias])) {
            return $sql;
        }

        foreach ($this->join[$fromAlias] as $join) {
            if (array_key_exists($join->alias, $knownAliases)) {
                throw NonUniqueAlias::new($join->alias, array_keys($knownAliases));
            }

            $sql .= ' ' . $join->type . ' JOIN ' . $join->table . ' ' . $join->alias;

            if ($join->condition !== null) {
                $sql .= ' ON ' . $join->condition;
            }

            $knownAliases[$join->alias] = true;
        }

        foreach ($this->join[$fromAlias] as $join) {
            $sql .= $this->getSQLForJoins($join->alias, $knownAliases);
        }

        return $sql;
    }

    /**
     * Deep clone of all expression objects in the SQL parts.
     */
    public function __clone()
    {
        foreach ($this->from as $key => $from) {
            $this->from[$key] = clone $from;
        }

        foreach ($this->join as $fromAlias => $joins) {
            foreach ($joins as $key => $join) {
                $this->join[$fromAlias][$key] = clone $join;
            }
        }

        if (is_object($this->where)) {
            $this->where = clone $this->where;
        }

        if (is_object($this->having)) {
            $this->having = clone $this->having;
        }

        foreach ($this->params as $name => $param) {
            if (! is_object($param)) {
                continue;
            }

            $this->params[$name] = clone $param;
        }
    }

    /**
     * Enables caching of the results of this query, for given amount of seconds
     * and optionally specified which key to use for the cache entry.
     *
     * @return $this
     */
    public function enableResultCache(QueryCacheProfile $cacheProfile): self
    {
        $this->resultCacheProfile = $cacheProfile;

        return $this;
    }

    /**
     * Disables caching of the results of this query.
     *
     * @return $this
     */
    public function disableResultCache(): self
    {
        $this->resultCacheProfile = null;

        return $this;
    }
}