Yiisoft\Db\Query\Query

Complexity

Total Complexity

126

Size/Duplication

Total Lines	724
Duplicated Lines	0 %

Test Coverage

Coverage

95.79%

Importance

Changes	1
Bugs	0	Features	0

74 Methods

Rating	Name	Duplication	Size	Complexity
A	addOrderBy()	0	11	2
A	addGroupBy()	0	15	4
A	batch()	0	3	1
A	__toString()	0	3	1
A	__construct()	0	2	1
A	andFilterHaving()	0	9	2
A	average()	0	5	2
A	andFilterCompare()	0	10	2
B	column()	0	41	11
A	andHaving()	0	11	2
A	addSelect()	0	9	2
A	andWhere()	0	13	5
A	all()	0	5	1
A	addParams()	0	21	5
A	andFilterWhere()	0	9	2
A	count()	0	5	2
A	getUnion()	0	3	1
A	getLimit()	0	3	1
A	where()	0	6	1
A	emulateExecution()	0	5	1
A	filterCondition()	0	3	1
A	createQueryHelper()	0	7	2
A	select()	0	6	1
A	getWhere()	0	3	1
A	getOffset()	0	3	1
A	orderBy()	0	5	1
A	shouldEmulateExecution()	0	3	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	populate()	0	14	3
A	getWithQueries()	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	setJoin()	0	5	1
A	limit()	0	5	1
A	params()	0	5	1
A	having()	0	6	1
A	getDistinct()	0	3	1
B	queryScalar()	0	38	7
A	getSelect()	0	3	1
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	setUnion()	0	5	1
A	leftJoin()	0	5	1
A	prepare()	0	3	1
A	rightJoin()	0	5	1
A	sum()	0	5	2
A	scalar()	0	5	1
A	innerJoin()	0	5	1
A	getFrom()	0	3	1
A	withQuery()	0	5	1
A	union()	0	5	1
A	getTablesUsedInFrom()	0	3	1
A	withQueries()	0	5	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	selectOption()	0	5	1

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Query;

use Closure;
use Throwable;
use Yiisoft\Arrays\ArrayHelper;
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\Expression;
use Yiisoft\Db\Expression\ExpressionInterface;
use Yiisoft\Db\Query\Helper\QueryHelper;
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 = [];
    private array $orderBy = [];
    protected array $params = [];
    protected array $union = [];
    protected array $withQueries = [];
    private bool $emulateExecution = false;
    private Closure|string|null $indexBy = null;
    private Expression|int|null $limit = null;
    private Expression|int|null $offset = null;
    private QueryHelper|null $queryHelper = null;
    private array|string|ExpressionInterface|null $where = null;

    public function __construct(private 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->createQueryHelper()->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->createQueryHelper()->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
    {
        return match ($this->emulateExecution) {
            true => [],
            false => $this->populate($this->createCommand()->queryAll()),
        };
    }

    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(): Expression|int|null
    {
        return $this->limit;
    }

    public function getOffset(): Expression|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->createQueryHelper()->cleanUpTableNames($this->from, $this->db->getQuoter());
    }

    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(Expression|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(Expression|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->createQueryHelper()->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;
    }

    /**
     * @psalm-suppress MixedArrayOffset
     */
    public function populate(array $rows): array
    {
        if ($this->indexBy === null) {
            return $rows;
        }

        $result = [];

        /** @psalm-var array[][] $row */
        foreach ($rows as $row) {
            $result[ArrayHelper::getValueByPath($row, $this->indexBy)] = $row;
        }

        return $result;
    }

    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->createQueryHelper()->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();
    }

    private function createQueryHelper(): QueryHelper
    {
        if ($this->queryHelper === null) {
            $this->queryHelper = new QueryHelper();
        }

        return $this->queryHelper;

The expression return $this->queryHelper could return the type null which is incompatible with the type-hinted return Yiisoft\Db\Query\Helper\QueryHelper. Consider adding an additional type-check to rule them out.

    }

    private function filterCondition(array $condition): array
    {
        return (array) $this->createQueryHelper()->filterCondition($condition);
    }
}