Yiisoft\Db\Query\Query

Complexity

Total Complexity

161

Size/Duplication

Total Lines	858
Duplicated Lines	0 %

Test Coverage

Coverage

95.79%

Importance

Changes	1
Bugs	0	Features	0

75 Methods

Rating	Name	Duplication	Size	Complexity
A	getUnion()	0	3	1
A	getLimit()	0	3	1
A	emulateExecution()	0	5	1
A	getWhere()	0	3	1
A	getOffset()	0	3	1
A	orderBy()	0	5	1
A	exists()	0	12	2
A	from()	0	13	3
A	getJoin()	0	3	1
A	getGroupBy()	0	3	1
A	distinct()	0	5	1
A	getWithQueries()	0	3	1
A	batch()	0	3	1
A	orFilterHaving()	0	9	2
A	getSelectOption()	0	3	1
A	filterWhere()	0	9	2
A	each()	0	3	1
A	getIndexBy()	0	3	1
A	getParams()	0	3	1
A	limit()	0	5	1
A	params()	0	5	1
A	having()	0	6	1
A	average()	0	5	2
A	getDistinct()	0	3	1
A	getSelect()	0	3	1
B	column()	0	41	11
A	orHaving()	0	11	2
A	indexBy()	0	5	1
A	groupBy()	0	10	3
A	getHaving()	0	3	1
A	offset()	0	5	1
A	one()	0	5	1
A	max()	0	5	2
A	leftJoin()	0	5	1
A	count()	0	5	2
A	innerJoin()	0	5	1
A	getFrom()	0	3	1
A	getTablesUsedInFrom()	0	3	1
A	orFilterWhere()	0	9	2
A	min()	0	5	2
A	filterHaving()	0	9	2
A	orWhere()	0	11	2
A	createCommand()	0	4	1
A	join()	0	5	1
A	getOrderBy()	0	3	1
A	addOrderBy()	0	11	2
A	addGroupBy()	0	15	4
A	__toString()	0	3	1
A	__construct()	0	2	1
A	andFilterHaving()	0	9	2
A	andFilterCompare()	0	10	2
A	andHaving()	0	11	2
A	addSelect()	0	9	2
A	andWhere()	0	13	5
A	addParams()	0	21	5
A	andFilterWhere()	0	9	2
A	isEmpty()	0	3	5
A	where()	0	6	1
D	filterCondition()	0	61	19
A	select()	0	6	1
A	shouldEmulateExecution()	0	3	1
A	setJoin()	0	5	1
B	normalizeSelect()	0	41	10
B	queryScalar()	0	38	7
A	normalizeOrderBy()	0	22	6
A	setUnion()	0	5	1
A	all()	0	7	2
A	prepare()	0	3	1
A	rightJoin()	0	5	1
A	sum()	0	5	2
A	scalar()	0	5	1
A	withQuery()	0	5	1
A	union()	0	5	1
A	withQueries()	0	5	1
A	selectOption()	0	5	1

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Query;

use Closure;
use Throwable;
use Yiisoft\Db\Command\CommandInterface;
use Yiisoft\Db\Connection\ConnectionInterface;
use Yiisoft\Db\Exception\Exception;
use Yiisoft\Db\Exception\InvalidArgumentException;
use Yiisoft\Db\Exception\InvalidConfigException;
use Yiisoft\Db\Exception\NotSupportedException;
use Yiisoft\Db\Expression\ExpressionInterface;
use Yiisoft\Db\Helper\ArrayHelper;
use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;

use function array_merge;
use function count;
use function is_array;
use function is_int;
use function is_string;
use function key;
use function preg_match;
use function preg_split;
use function reset;
use function str_contains;
use function strcasecmp;
use function strlen;
use function substr;
use function trim;

/**
 * Query represents a SELECT SQL statement in a way that is independent of DBMS.
 *
 * Query provides a set of methods to facilitate the specification of different clauses in a SELECT statement. These
 * methods can be chained together.
 *
 * By calling {@see createCommand()}, we can get a {@see Command} instance which can be further used to perform/execute
 * the DB query against a database.
 *
 * For example,
 *
 * ```php
 * $query = new Query;
 * // compose the query
 * $query->select('id, name')
 *     ->from('user')
 *     ->limit(10);
 * // build and execute the query
 * $rows = $query->all();
 * // alternatively, you can create DB command and execute it
 * $command = $query->createCommand();
 * // $command->sql returns the actual SQL
 * $rows = $command->queryAll();
 * ```
 *
 * Query internally uses the {@see QueryBuilder} class to generate the SQL statement.
 */
class Query implements QueryInterface
{
    protected array $select = [];
    protected string|null $selectOption = null;
    protected bool|null $distinct = null;
    protected array $from = [];
    protected array $groupBy = [];
    protected array|ExpressionInterface|string|null $having = null;
    protected array $join = [];
    protected array $orderBy = [];
    protected array $params = [];
    protected array $union = [];
    protected array $withQueries = [];
    protected Closure|string|null $indexBy = null;
    protected ExpressionInterface|int|null $limit = null;
    protected ExpressionInterface|int|null $offset = null;
    protected array|string|ExpressionInterface|null $where = null;

    private bool $emulateExecution = false;

    public function __construct(protected ConnectionInterface $db)
    {
    }

    /**
     * Returns the SQL representation of Query.
     *
     * @return string
     */
    public function __toString(): string
    {
        return serialize($this);
    }

    public function addGroupBy(array|string|ExpressionInterface $columns): static
    {
        if ($columns instanceof ExpressionInterface) {

$columns is never a sub-type of Yiisoft\Db\Expression\ExpressionInterface.

            $columns = [$columns];
        } elseif (!is_array($columns)) {

The condition is_array($columns) is always true.

            $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY);
        }

        if ($this->groupBy === []) {
            $this->groupBy = $columns;
        } else {
            $this->groupBy = array_merge($this->groupBy, $columns);
        }

        return $this;
    }

    public function addOrderBy(array|string|ExpressionInterface $columns): static
    {
        $columns = $this->normalizeOrderBy($columns);

        if ($this->orderBy === []) {
            $this->orderBy = $columns;
        } else {
            $this->orderBy = array_merge($this->orderBy, $columns);
        }

        return $this;
    }

    public function addParams(array $params): static
    {
        if (!empty($params)) {
            if (empty($this->params)) {
                $this->params = $params;
            } else {
                /**
                 * @psalm-var array $params
                 * @psalm-var mixed $value
                 */
                foreach ($params as $name => $value) {
                    if (is_int($name)) {
                        $this->params[] = $value;
                    } else {
                        $this->params[$name] = $value;
                    }
                }
            }
        }

        return $this;
    }

    public function andFilterHaving(array $condition): static
    {
        $condition = $this->filterCondition($condition);

        if ($condition !== []) {
            $this->andHaving($condition);
        }

        return $this;
    }

    public function andFilterWhere(array $condition): static
    {
        $condition = $this->filterCondition($condition);

        if ($condition !== []) {
            $this->andWhere($condition);
        }

        return $this;
    }

    public function andHaving(array|string|ExpressionInterface $condition, array $params = []): static
    {
        if ($this->having === null) {
            $this->having = $condition;
        } else {
            $this->having = ['and', $this->having, $condition];
        }

        $this->addParams($params);

        return $this;
    }

    public function addSelect(array|string|ExpressionInterface $columns): static
    {
        if ($this->select === []) {
            return $this->select($columns);
        }

        $this->select = array_merge($this->select, $this->normalizeSelect($columns));

        return $this;
    }

    public function andFilterCompare(string $name, string|null $value, string $defaultOperator = '='): static
    {
        $operator = $defaultOperator;

        if (preg_match('/^(<>|>=|>|<=|<|=)/', (string) $value, $matches)) {
            $operator = $matches[1];
            $value = substr((string) $value, strlen($operator));
        }

        return $this->andFilterWhere([$operator, $name, $value]);
    }

    public function andWhere($condition, array $params = []): static
    {
        if ($this->where === null) {
            $this->where = $condition;
        } elseif (is_array($this->where) && isset($this->where[0]) && strcasecmp((string) $this->where[0], 'and') === 0) {
            $this->where[] = $condition;
        } else {
            $this->where = ['and', $this->where, $condition];
        }

        $this->addParams($params);

        return $this;
    }

    public function all(): array
    {
        if ($this->emulateExecution === true) {
            return [];
        }

        return ArrayHelper::populate($this->createCommand()->queryAll(), $this->indexBy);
    }

    public function average(string $q): int|float|null|string
    {
        return match ($this->emulateExecution) {
            true => null,
            false => is_numeric($avg = $this->queryScalar("AVG($q)")) ? $avg : null,
        };
    }

    public function batch(int $batchSize = 100): BatchQueryResultInterface
    {
        return $this->db->createBatchQueryResult($this)->batchSize($batchSize);
    }

    /**
     * @psalm-suppress MixedArrayOffset
     *
     * @throws Exception
     * @throws InvalidConfigException
     * @throws NotSupportedException
     * @throws Throwable
     */
    public function column(): array
    {
        if ($this->emulateExecution) {
            return [];
        }

        if ($this->indexBy === null) {
            return $this->createCommand()->queryColumn();
        }

        if (is_string($this->indexBy) && count($this->select) === 1) {
            if (!str_contains($this->indexBy, '.') && count($tables = $this->getTablesUsedInFrom()) > 0) {
                $this->select[] = key($tables) . '.' . $this->indexBy;
            } else {
                $this->select[] = $this->indexBy;
            }
        }

        $rows = $this->createCommand()->queryAll();
        $results = [];
        $column = null;
        if (is_string($this->indexBy)) {
            if (($dotPos = strpos($this->indexBy, '.')) === false) {
                $column = $this->indexBy;
            } else {
                $column = substr($this->indexBy, $dotPos + 1);
            }
        }

        /** @psalm-var array<array-key, array<string, string>> $rows */
        foreach ($rows as $row) {
            $value = reset($row);

            if ($this->indexBy instanceof Closure) {
                $results[($this->indexBy)($row)] = $value;
            } else {
                $results[$row[$column] ?? $row[$this->indexBy]] = $value;
            }
        }

        return $results;
    }

    public function count(string $q = '*'): int|string
    {
        return match ($this->emulateExecution) {
            true => 0,
            false => is_numeric($count = $this->queryScalar("COUNT($q)")) ? (int) $count : 0,
        };
    }

    public function createCommand(): CommandInterface
    {
        [$sql, $params] = $this->db->getQueryBuilder()->build($this);
        return $this->db->createCommand($sql, $params);
    }

    public function distinct(bool|null $value = true): static
    {
        $this->distinct = $value;

        return $this;
    }

    public function each(int $batchSize = 100): BatchQueryResultInterface
    {
        return $this->db->createBatchQueryResult($this, true)->batchSize($batchSize);
    }

    /**
     * @throws Exception|InvalidConfigException|Throwable
     */
    public function exists(): bool
    {
        if ($this->emulateExecution) {
            return false;
        }

        $command = $this->createCommand();
        $params = $command->getParams();
        $command->setSql($this->db->getQueryBuilder()->selectExists($command->getSql()));
        $command->bindValues($params);

        return (bool) $command->queryScalar();
    }

    public function emulateExecution(bool $value = true): static
    {
        $this->emulateExecution = $value;

        return $this;
    }

    public function filterHaving(array $condition): static
    {
        $condition = $this->filterCondition($condition);

        if ($condition !== []) {
            $this->having($condition);
        }

        return $this;
    }

    public function filterWhere(array $condition): static
    {
        $condition = $this->filterCondition($condition);

        if ($condition !== []) {
            $this->where($condition);
        }

        return $this;
    }

    public function from(array|ExpressionInterface|string $tables): static
    {
        if ($tables instanceof ExpressionInterface) {

$tables is never a sub-type of Yiisoft\Db\Expression\ExpressionInterface.

            $tables = [$tables];
        }

        if (is_string($tables)) {

The condition is_string($tables) is always false.

            $tables = preg_split('/\s*,\s*/', trim($tables), -1, PREG_SPLIT_NO_EMPTY);
        }

        $this->from = $tables;

        return $this;
    }

    public function getDistinct(): bool|null
    {
        return $this->distinct;
    }

    public function getFrom(): array
    {
        return $this->from;
    }

    public function getGroupBy(): array
    {
        return $this->groupBy;
    }

    public function getHaving(): string|array|ExpressionInterface|null
    {
        return $this->having;
    }

    public function getIndexBy(): Closure|string|null
    {
        return $this->indexBy;
    }

    public function getJoin(): array
    {
        return $this->join;
    }

    public function getLimit(): ExpressionInterface|int|null
    {
        return $this->limit;
    }

    public function getOffset(): ExpressionInterface|int|null
    {
        return $this->offset;
    }

    public function getOrderBy(): array
    {
        return $this->orderBy;
    }

    public function getParams(): array
    {
        return $this->params;
    }

    public function getSelect(): array
    {
        return $this->select;
    }

    public function getSelectOption(): string|null
    {
        return $this->selectOption;
    }

    public function getTablesUsedInFrom(): array
    {
        return $this->db->getQuoter()->cleanUpTableNames($this->from);
    }

    public function getUnion(): array
    {
        return $this->union;
    }

    public function getWhere(): array|string|ExpressionInterface|null
    {
        return $this->where;
    }

    public function getWithQueries(): array
    {
        return $this->withQueries;
    }

    public function groupBy(array|string|ExpressionInterface $columns): static
    {
        if ($columns instanceof ExpressionInterface) {

$columns is never a sub-type of Yiisoft\Db\Expression\ExpressionInterface.

            $columns = [$columns];
        } elseif (!is_array($columns)) {

The condition is_array($columns) is always true.

            $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY);
        }
        $this->groupBy = $columns;

        return $this;
    }

    public function having(array|ExpressionInterface|string|null $condition, array $params = []): static
    {
        $this->having = $condition;
        $this->addParams($params);

        return $this;
    }

    public function indexBy(Closure|string|null $column): static
    {
        $this->indexBy = $column;

        return $this;
    }

    public function innerJoin(array|string $table, array|string $on = '', array $params = []): static
    {
        $this->join[] = ['INNER JOIN', $table, $on];

        return $this->addParams($params);
    }

    public function join(string $type, array|string $table, array|string $on = '', array $params = []): static
    {
        $this->join[] = [$type, $table, $on];

        return $this->addParams($params);
    }

    public function leftJoin(array|string $table, array|string $on = '', array $params = []): static
    {
        $this->join[] = ['LEFT JOIN', $table, $on];

        return $this->addParams($params);
    }

    public function limit(ExpressionInterface|int|null $limit): static
    {
        $this->limit = $limit;

        return $this;
    }

    public function max(string $q): int|float|null|string
    {
        $max = $this->queryScalar("MAX($q)");

        return is_numeric($max) ? $max : null;
    }

    public function min(string $q): int|float|null|string
    {
        $min = $this->queryScalar("MIN($q)");

        return is_numeric($min) ? $min : null;
    }

    public function offset(ExpressionInterface|int|null $offset): static
    {
        $this->offset = $offset;

        return $this;
    }

    public function one(): array|object|null
    {
        return match ($this->emulateExecution) {
            true => null,
            false => $this->createCommand()->queryOne(),
        };
    }

    public function orderBy(array|string|ExpressionInterface $columns): static
    {
        $this->orderBy = $this->normalizeOrderBy($columns);

        return $this;
    }

    public function orFilterHaving(array $condition): static
    {
        $condition = $this->filterCondition($condition);

        if ($condition !== []) {
            $this->orHaving($condition);
        }

        return $this;
    }

    public function orFilterWhere(array $condition): static
    {
        $condition = $this->filterCondition($condition);

        if ($condition !== []) {
            $this->orWhere($condition);
        }

        return $this;
    }

    public function orHaving(array|string|ExpressionInterface $condition, array $params = []): static
    {
        if ($this->having === null) {
            $this->having = $condition;
        } else {
            $this->having = ['or', $this->having, $condition];
        }

        $this->addParams($params);

        return $this;
    }

    public function orWhere(array|string|ExpressionInterface $condition, array $params = []): static
    {
        if ($this->where === null) {
            $this->where = $condition;
        } else {
            $this->where = ['or', $this->where, $condition];
        }

        $this->addParams($params);

        return $this;
    }

    public function params(array $params): static
    {
        $this->params = $params;

        return $this;
    }

    public function prepare(QueryBuilderInterface $builder): QueryInterface
    {
        return $this;
    }

    public function rightJoin(array|string $table, array|string $on = '', array $params = []): static
    {
        $this->join[] = ['RIGHT JOIN', $table, $on];

        return $this->addParams($params);
    }

    public function scalar(): bool|int|null|string|float
    {
        return match ($this->emulateExecution) {
            true => null,
            false => $this->createCommand()->queryScalar(),
        };
    }

    public function select(array|string|ExpressionInterface $columns, string $option = null): static
    {
        $this->select = $this->normalizeSelect($columns);
        $this->selectOption = $option;

        return $this;
    }

    public function selectOption(string|null $value): static
    {
        $this->selectOption = $value;

        return $this;
    }

    public function setJoin(array $value): static
    {
        $this->join = $value;

        return $this;
    }

    public function setUnion(array $value): static
    {
        $this->union = $value;

        return $this;
    }

    public function shouldEmulateExecution(): bool
    {
        return $this->emulateExecution;
    }

    public function sum(string $q): int|float|null|string
    {
        return match ($this->emulateExecution) {
            true => null,
            false => is_numeric($sum = $this->queryScalar("SUM($q)")) ? $sum : null,
        };
    }

    public function union(QueryInterface|string $sql, bool $all = false): static
    {
        $this->union[] = ['query' => $sql, 'all' => $all];

        return $this;
    }

    public function where(array|string|ExpressionInterface|null $condition, array $params = []): static
    {
        $this->where = $condition;
        $this->addParams($params);

        return $this;
    }

    public function withQuery(QueryInterface|string $query, string $alias, bool $recursive = false): static
    {
        $this->withQueries[] = ['query' => $query, 'alias' => $alias, 'recursive' => $recursive];

        return $this;
    }

    public function withQueries(array $withQueries): static
    {
        $this->withQueries = $withQueries;

        return $this;
    }

    /**
     * Queries a scalar value by setting {@see select} first.
     *
     * Restores the value of select to make this query reusable.
     *
     * @param ExpressionInterface|string $selectExpression
     *
     * @throws Exception
     * @throws InvalidArgumentException
     * @throws InvalidConfigException
     * @throws NotSupportedException
     * @throws Throwable
     *
     * @return bool|float|int|string|null
     *
     * @psalm-suppress PossiblyUndefinedVariable
     */
    protected function queryScalar(string|ExpressionInterface $selectExpression): bool|int|null|string|float
    {
        if ($this->emulateExecution) {
            return null;
        }

        if (
            !$this->distinct

The expression $this->distinct of type boolean|null is loosely compared to false; this is ambiguous if the boolean can be false. You might want to explicitly use !== null instead.

            && empty($this->groupBy)
            && empty($this->having)
            && empty($this->union)
            && empty($this->with)
        ) {
            $select = $this->select;
            $order = $this->orderBy;
            $limit = $this->limit;
            $offset = $this->offset;

            $this->select = [$selectExpression];
            $this->orderBy = [];
            $this->limit = null;
            $this->offset = null;

            $command = $this->createCommand();

            $this->select = $select;
            $this->orderBy = $order;
            $this->limit = $limit;
            $this->offset = $offset;

            return $command->queryScalar();
        }

        $query = (new self($this->db))->select($selectExpression)->from(['c' => $this]);
        [$sql, $params] = $this->db->getQueryBuilder()->build($query);
        $command = $this->db->createCommand($sql, $params);

        return $command->queryScalar();
    }

    /**
     * Removes {@see isEmpty()|empty operands} from the given query condition.
     *
     * @param array|string $condition the original condition
     *
     * @return array|string the condition with {@see isEmpty()|empty operands} removed.
     */
    private function filterCondition(array|string $condition): array|string
    {
        if (!is_array($condition)) {

The condition is_array($condition) is always true.

            return $condition;
        }

        if (!isset($condition[0])) {
            /** hash format: 'column1' => 'value1', 'column2' => 'value2', ... */
            /** @var mixed $value */
            foreach ($condition as $name => $value) {
                if ($this->isEmpty($value)) {
                    unset($condition[$name]);
                }
            }

            return $condition;
        }

        /** operator format: operator, operand 1, operand 2, ... */
        /** @var string */
        $operator = array_shift($condition);

        switch (strtoupper($operator)) {
            case 'NOT':
            case 'AND':
            case 'OR':
                /** @psalm-var array<array-key, array|string> $condition */
                foreach ($condition as $i => $operand) {
                    $subCondition = $this->filterCondition($operand);
                    if ($this->isEmpty($subCondition)) {
                        unset($condition[$i]);
                    } else {
                        $condition[$i] = $subCondition;
                    }
                }

                if (empty($condition)) {
                    return [];
                }

                break;
            case 'BETWEEN':
            case 'NOT BETWEEN':
                if (array_key_exists(1, $condition) && array_key_exists(2, $condition)) {
                    if ($this->isEmpty($condition[1]) || $this->isEmpty($condition[2])) {
                        return [];
                    }
                } else {
                    return [];
                }

                break;
            default:
                if (array_key_exists(1, $condition) && $this->isEmpty($condition[1])) {
                    return [];
                }
        }

        array_unshift($condition, $operator);

        return $condition;
    }

    /**
     * Returns a value indicating whether the give value is "empty".
     *
     * The value is considered "empty", if one of the following conditions is satisfied:
     *
     * - it is `null`,
     * - an empty string (`''`),
     * - a string containing only whitespace characters,
     * - or an empty array.
     *
     * @param mixed $value
     *
     * @return bool if the value is empty
     */
    private function isEmpty(mixed $value): bool
    {
        return $value === '' || $value === [] || $value === null || (is_string($value) && trim($value) === '');
    }

    /**
     * Normalizes format of ORDER BY data.
     *
     * @param array|ExpressionInterface|string $columns the columns value to normalize.
     *
     * See {@see orderBy} and {@see addOrderBy}.
     */
    private function normalizeOrderBy(array|string|ExpressionInterface $columns): array
    {
        if ($columns instanceof ExpressionInterface) {

$columns is never a sub-type of Yiisoft\Db\Expression\ExpressionInterface.

            return [$columns];
        }

        if (is_array($columns)) {

The condition is_array($columns) is always true.

            return $columns;
        }

        $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY);
        $result = [];

        foreach ($columns as $column) {
            if (preg_match('/^(.*?)\s+(asc|desc)$/i', $column, $matches)) {
                $result[$matches[1]] = strcasecmp($matches[2], 'desc') ? SORT_ASC : SORT_DESC;
            } else {
                $result[$column] = SORT_ASC;
            }
        }

        return $result;
    }

    /**
     * Normalizes the SELECT columns passed to {@see select()} or {@see addSelect()}.
     */
    private function normalizeSelect(array|ExpressionInterface|string $columns): array
    {
        if ($columns instanceof ExpressionInterface) {

$columns is never a sub-type of Yiisoft\Db\Expression\ExpressionInterface.

            $columns = [$columns];
        } elseif (!is_array($columns)) {

The condition is_array($columns) is always true.

            $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY);
        }

        $select = [];

        /** @psalm-var array<array-key, ExpressionInterface|string> $columns */
        foreach ($columns as $columnAlias => $columnDefinition) {
            if (is_string($columnAlias)) {
                // Already in the normalized format, good for them.
                $select[$columnAlias] = $columnDefinition;
                continue;
            }

            if (is_string($columnDefinition)) {
                if (
                    preg_match('/^(.*?)(?i:\s+as\s+|\s+)([\w\-_.]+)$/', $columnDefinition, $matches) &&
                    !preg_match('/^\d+$/', $matches[2]) &&
                    !str_contains($matches[2], '.')
                ) {
                    /** Using "columnName as alias" or "columnName alias" syntax */
                    $select[$matches[2]] = $matches[1];
                    continue;
                }
                if (!str_contains($columnDefinition, '(')) {
                    /** Normal column name, just alias it to itself to ensure it's not selected twice */
                    $select[$columnDefinition] = $columnDefinition;
                    continue;
                }
            }

            // Either a string calling a function, DB expression, or sub-query
            /** @var string */
            $select[] = $columnDefinition;
        }

        return $select;
    }
}