DQLQueryBuilder::buildOrderBy() - Code Metrics - Inspection of "Change return type for query one and query column" - yiisoft/db - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#343)

by Sergei

created 2022-07-09 20:00 UTC

DQLQueryBuilder::buildOrderBy() A

↳ Parent: DQLQueryBuilder

Complexity

Conditions	5
Paths	5

Size

Total Lines	19
Code Lines	10

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	5
eloc	10
nc	5
nop	2
dl	0
loc	19
rs	9.6111
c	0
b	0
f	0

<?php

declare(strict_types=1);

namespace Yiisoft\Db\QueryBuilder;

use Yiisoft\Db\Command\Param;
use Yiisoft\Db\Command\ParamBuilder;
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\ExpressionBuilder;
use Yiisoft\Db\Expression\ExpressionBuilderInterface;
use Yiisoft\Db\Expression\ExpressionInterface;
use Yiisoft\Db\QueryBuilder\Conditions\HashCondition;
use Yiisoft\Db\QueryBuilder\Conditions\Interface\ConditionInterface;
use Yiisoft\Db\QueryBuilder\Conditions\SimpleCondition;
use Yiisoft\Db\Query\Query;
use Yiisoft\Db\Query\QueryExpressionBuilder;
use Yiisoft\Db\Query\QueryInterface;
use Yiisoft\Db\Schema\QuoterInterface;
use Yiisoft\Db\Schema\SchemaInterface;

use function array_filter;
use function array_merge;
use function array_shift;
use function ctype_digit;
use function get_class;
use function implode;
use function is_array;
use function is_int;
use function is_string;
use function ltrim;
use function preg_match;
use function preg_split;
use function reset;
use function strtoupper;
use function trim;

abstract class DQLQueryBuilder implements DQLQueryBuilderInterface
{
    protected string $separator = ' ';

    /**
     * @var array map of condition aliases to condition classes. For example:
     *
     * ```php
     * return [
     *     'LIKE' => \Yiisoft\Db\Condition\LikeCondition::class,
     * ];
     * ```
     *
     * This property is used by {@see createConditionFromArray} method.
     * See default condition classes list in {@see defaultConditionClasses()} method.
     *
     * In case you want to add custom conditions support, use the {@see setConditionClasses()} method.
     *
     * @see setConditonClasses()
     * @see defaultConditionClasses()
     */
    protected array $conditionClasses = [];

    /**
     * @psalm-var array<string, class-string<ExpressionBuilderInterface>> maps expression class to expression builder
     * class.
     *
     * For example:
     *
     * ```php
     * [
     *    Expression::class => ExpressionBuilder::class
     * ]
     * ```
     * This property is mainly used by {@see buildExpression()} to build SQL expressions form expression objects.
     * See default values in {@see defaultExpressionBuilders()} method.
     *
     * {@see setExpressionBuilders()}
     * {@see defaultExpressionBuilders()}
     */
    protected array $expressionBuilders = [];

    public function __construct(
        private QueryBuilderInterface $queryBuilder,
        private QuoterInterface $quoter,
        private SchemaInterface $schema
    ) {
        $this->expressionBuilders = $this->defaultExpressionBuilders();
        $this->conditionClasses = $this->defaultConditionClasses();
    }

    public function build(QueryInterface $query, array $params = []): array
    {
        $query = $query->prepare($this->queryBuilder);
        $params = empty($params) ? $query->getParams() : array_merge($params, $query->getParams());
        $clauses = [
            $this->buildSelect($query->getSelect(), $params, $query->getDistinct(), $query->getSelectOption()),
            $this->buildFrom($query->getFrom(), $params),
            $this->buildJoin($query->getJoin(), $params),
            $this->buildWhere($query->getWhere(), $params),
            $this->buildGroupBy($query->getGroupBy(), $params),
            $this->buildHaving($query->getHaving(), $params),
        ];
        $sql = implode($this->separator, array_filter($clauses));
        $sql = $this->buildOrderByAndLimit($sql, $query->getOrderBy(), $query->getLimit(), $query->getOffset());

        if (!empty($query->getOrderBy())) {
            /** @psalm-var array<string, ExpressionInterface|string> */
            foreach ($query->getOrderBy() as $expression) {
                if ($expression instanceof ExpressionInterface) {
                    $this->buildExpression($expression, $params);
                }
            }
        }

        if (!empty($query->getGroupBy())) {
            /** @psalm-var array<string, ExpressionInterface|string> */
            foreach ($query->getGroupBy() as $expression) {
                if ($expression instanceof ExpressionInterface) {
                    $this->buildExpression($expression, $params);
                }
            }
        }

        $union = $this->buildUnion($query->getUnion(), $params);

        if ($union !== '') {
            $sql = "($sql)$this->separator$union";
        }

        $with = $this->buildWithQueries($query->getWithQueries(), $params);

        if ($with !== '') {
            $sql = "$with$this->separator$sql";
        }

        return [$sql, $params];
    }

    public function buildColumns(array|string $columns): string
    {
        if (!is_array($columns)) {

            if (str_contains($columns, '(')) {
                return $columns;
            }

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

            if ($columns === false) {
                throw new InvalidArgumentException("$rawColumns is not valid columns.");
            }
        }

        /** @psalm-var array<array-key, ExpressionInterface|string> $columns */
        foreach ($columns as $i => $column) {
            if ($column instanceof ExpressionInterface) {
                $columns[$i] = $this->buildExpression($column);
            } elseif (!str_contains($column, '(')) {
                $columns[$i] = $this->quoter->quoteColumnName($column);
            }
        }

        /** @psalm-var string[] $columns */
        return implode(', ', $columns);
    }

    public function buildCondition(array|string|ExpressionInterface|null $condition, array &$params = []): string
    {
        if (is_array($condition)) {

            if (empty($condition)) {
                return '';
            }

            $condition = $this->createConditionFromArray($condition);
        }

        if ($condition instanceof ExpressionInterface) {

            return $this->buildExpression($condition, $params);
        }

        return $condition ?? '';
    }

    /**
     * @throws InvalidArgumentException
     *
     * @psalm-suppress UndefinedInterfaceMethod
     * @psalm-suppress MixedMethodCall
     */
    public function buildExpression(ExpressionInterface $expression, array &$params = []): string
    {
        $builder = $this->queryBuilder->getExpressionBuilder($expression);
        return (string) $builder->build($expression, $params);
    }

    public function buildFrom(?array $tables, array &$params): string
    {
        if (empty($tables)) {
            return '';
        }

        /** @psalm-var string[] */
        $tables = $this->quoteTableNames($tables, $params);

        return 'FROM ' . implode(', ', $tables);
    }

    public function buildGroupBy(array $columns, array &$params = []): string
    {
        if (empty($columns)) {
            return '';
        }

        /** @psalm-var array<string, Expression|string> $columns */
        foreach ($columns as $i => $column) {
            if ($column instanceof Expression) {
                $columns[$i] = $this->buildExpression($column);
                $params = array_merge($params, $column->getParams());
            } elseif (!str_contains($column, '(')) {
                $columns[$i] = $this->quoter->quoteColumnName($column);
            }
        }

        return 'GROUP BY ' . implode(', ', $columns);
    }

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

        return ($having === '') ? '' : ('HAVING ' . $having);
    }

    public function buildJoin(array $joins, array &$params): string
    {
        if (empty($joins)) {
            return '';
        }

        /**
         * @psalm-var array<
         *   array-key,
         *   array{
         *     0?:string,
         *     1?:array<array-key, Query|string>|string,
         *     2?:array|ExpressionInterface|string|null
         *   }|null
         * > $joins
         */
        foreach ($joins as $i => $join) {
            if (!is_array($join) || !isset($join[0], $join[1])) {
                throw new Exception(
                    'A join clause must be specified as an array of join type, join table, and optionally join '
                    . 'condition.'
                );
            }

            /* 0:join type, 1:join table, 2:on-condition (optional) */
            [$joinType, $table] = $join;

            $tables = $this->quoteTableNames((array) $table, $params);

            /** @var string $table */
            $table = reset($tables);
            $joins[$i] = "$joinType $table";

            if (isset($join[2])) {
                $condition = $this->buildCondition($join[2], $params);
                if ($condition !== '') {
                    $joins[$i] .= ' ON ' . $condition;
                }
            }
        }

        /** @psalm-var array<string> $joins */
        return implode($this->separator, $joins);
    }

    public function buildLimit(Expression|int|null $limit, Expression|int|null $offset): string
    {
        $sql = '';

        if ($this->hasLimit($limit)) {
            $sql = 'LIMIT ' . (string) $limit;
        }

        if ($this->hasOffset($offset)) {
            $sql .= ' OFFSET ' . (string) $offset;
        }

        return ltrim($sql);
    }

    public function buildOrderBy(array $columns, array &$params = []): string
    {
        if (empty($columns)) {
            return '';
        }

        $orders = [];

        /** @psalm-var array<string, Expression|int|string> $columns */
        foreach ($columns as $name => $direction) {
            if ($direction instanceof Expression) {
                $orders[] = $this->buildExpression($direction);
                $params = array_merge($params, $direction->getParams());
            } else {
                $orders[] = $this->quoter->quoteColumnName($name) . ($direction === SORT_DESC ? ' DESC' : '');
            }
        }

        return 'ORDER BY ' . implode(', ', $orders);
    }

    public function buildOrderByAndLimit(
        string $sql,
        array $orderBy,
        Expression|int|null $limit,
        Expression|int|null $offset,
        array &$params = []
    ): string {
        $orderBy = $this->buildOrderBy($orderBy, $params);
        if ($orderBy !== '') {
            $sql .= $this->separator . $orderBy;
        }
        $limit = $this->buildLimit($limit, $offset);
        if ($limit !== '') {
            $sql .= $this->separator . $limit;
        }

        return $sql;
    }

    public function buildSelect(
        array $columns,
        array &$params,
        ?bool $distinct = false,
        ?string $selectOption = null
    ): string {
        $select = $distinct ? 'SELECT DISTINCT' : 'SELECT';

        if ($selectOption !== null) {
            $select .= ' ' . $selectOption;
        }

        if (empty($columns)) {
            return $select . ' *';
        }

        /** @psalm-var array<array-key, ExpressionInterface|Query|string> $columns */
        foreach ($columns as $i => $column) {
            if ($column instanceof ExpressionInterface) {
                if (is_int($i)) {
                    $columns[$i] = $this->buildExpression($column, $params);
                } else {
                    $columns[$i] = $this->buildExpression($column, $params) . ' AS '
                        . $this->quoter->quoteColumnName($i);
                }
            } elseif ($column instanceof QueryInterface) {
                [$sql, $params] = $this->build($column, $params);
                $columns[$i] = "($sql) AS " . $this->quoter->quoteColumnName((string) $i);
            } elseif (is_string($i) && $i !== $column) {
                if (!str_contains($column, '(')) {
                    $column = $this->quoter->quoteColumnName($column);
                }
                $columns[$i] = "$column AS " . $this->quoter->quoteColumnName($i);
            } elseif (!str_contains($column, '(')) {
                if (preg_match('/^(.*?)(?i:\s+as\s+|\s+)([\w\-_.]+)$/', $column, $matches)) {
                    $columns[$i] = $this->quoter->quoteColumnName($matches[1])
                        . ' AS ' . $this->quoter->quoteColumnName($matches[2]);
                } else {
                    $columns[$i] = $this->quoter->quoteColumnName($column);
                }
            }
        }

        return $select . ' ' . implode(', ', $columns);
    }

    public function buildUnion(array $unions, array &$params): string
    {
        if (empty($unions)) {
            return '';
        }

        $result = '';

        /** @psalm-var array<array{query:Query|string, all:bool}> $unions */
        foreach ($unions as $i => $union) {
            $query = $union['query'];
            if ($query instanceof QueryInterface) {
                [$unions[$i]['query'], $params] = $this->build($query, $params);
            }

            $result .= 'UNION ' . ($union['all'] ? 'ALL ' : '') . '( ' . $unions[$i]['query'] . ' ) ';
        }

        return trim($result);
    }

    public function buildWhere(
        array|string|ConditionInterface|ExpressionInterface|null $condition,
        array &$params = []
    ): string {
        $where = $this->buildCondition($condition, $params);
        return ($where === '') ? '' : ('WHERE ' . $where);
    }

    public function buildWithQueries(array $withs, array &$params): string
    {
        if (empty($withs)) {
            return '';
        }

        $recursive = false;
        $result = [];

        /** @psalm-var array<array-key, array{query:string|Query, alias:string, recursive:bool}> $withs */
        foreach ($withs as $with) {
            if ($with['recursive']) {
                $recursive = true;
            }

            $query = $with['query'];
            if ($query instanceof QueryInterface) {
                [$with['query'], $params] = $this->build($query, $params);
            }

            $result[] = $with['alias'] . ' AS (' . $with['query'] . ')';
        }

        return 'WITH ' . ($recursive ? 'RECURSIVE ' : '') . implode(', ', $result);
    }

    public function createConditionFromArray(array $condition): ConditionInterface
    {
        /** operator format: operator, operand 1, operand 2, ... */
        if (isset($condition[0])) {
            $operator = strtoupper((string) array_shift($condition));

            /** @var string $className */
            $className = $this->conditionClasses[$operator] ?? SimpleCondition::class;

            /** @var ConditionInterface $className */
            return $className::fromArrayDefinition($operator, $condition);
        }

        /** hash format: 'column1' => 'value1', 'column2' => 'value2', ... */
        return new HashCondition($condition);
    }

    public function getExpressionBuilder(ExpressionInterface $expression): object
    {
        $className = get_class($expression);

        if (!isset($this->expressionBuilders[$className])) {
            throw new InvalidArgumentException(
                'Expression of class ' . $className . ' can not be built in ' . static::class
            );
        }

        return new $this->expressionBuilders[$className]($this->queryBuilder);
    }

    public function selectExists(string $rawSql): string
    {
        return 'SELECT EXISTS(' . $rawSql . ')';
    }

    public function setConditionClasses(array $classes): void
    {
        $this->conditionClasses = array_merge($this->conditionClasses, $classes);
    }

    public function setExpressionBuilders(array $builders): void
    {
        $this->expressionBuilders = array_merge($this->expressionBuilders, $builders);
    }

    /**
     * @param string the separator between different fragments of a SQL statement.
filter:
    dependency_paths: ["lib/*"]
     *
     * Defaults to an empty space. This is mainly used by {@see build()} when generating a SQL statement.
     */
    public function setSeparator(string $separator): void
    {
        $this->separator = $separator;
    }

    /**
     * Contains array of default condition classes. Extend this method, if you want to change default condition classes
     * for the query builder.
     *
     * @return array
     *
     * See {@see conditionClasses} docs for details.
     */
    protected function defaultConditionClasses(): array
    {
        return [
            'NOT' => Conditions\NotCondition::class,
            'AND' => Conditions\AndCondition::class,
            'OR' => Conditions\OrCondition::class,
            'BETWEEN' => Conditions\BetweenCondition::class,
            'NOT BETWEEN' => Conditions\BetweenCondition::class,
            'IN' => Conditions\InCondition::class,
            'NOT IN' => Conditions\InCondition::class,
            'LIKE' => Conditions\LikeCondition::class,
            'NOT LIKE' => Conditions\LikeCondition::class,
            'OR LIKE' => Conditions\LikeCondition::class,
            'OR NOT LIKE' => Conditions\LikeCondition::class,
            'EXISTS' => Conditions\ExistsCondition::class,
            'NOT EXISTS' => Conditions\ExistsCondition::class,
        ];
    }

    /**
     * Contains array of default expression builders. Extend this method and override it, if you want to change default
     * expression builders for this query builder.
     *
     * @return array
     *
     * See {@see expressionBuilders} docs for details.
     *
     * @psalm-return array<string, class-string<ExpressionBuilderInterface>>
     */
    protected function defaultExpressionBuilders(): array
    {
        return [
            Query::class => QueryExpressionBuilder::class,
            Param::class => ParamBuilder::class,
            Expression::class => ExpressionBuilder::class,
            Conditions\ConjunctionCondition::class => Conditions\Builder\ConjunctionConditionBuilder::class,
            Conditions\NotCondition::class => Conditions\Builder\NotConditionBuilder::class,
            Conditions\AndCondition::class => Conditions\Builder\ConjunctionConditionBuilder::class,
            Conditions\OrCondition::class => Conditions\Builder\ConjunctionConditionBuilder::class,
            Conditions\BetweenCondition::class => Conditions\Builder\BetweenConditionBuilder::class,
            Conditions\InCondition::class => Conditions\Builder\InConditionBuilder::class,
            Conditions\LikeCondition::class => Conditions\Builder\LikeConditionBuilder::class,
            Conditions\ExistsCondition::class => Conditions\Builder\ExistsConditionBuilder::class,
            Conditions\SimpleCondition::class => Conditions\Builder\SimpleConditionBuilder::class,
            Conditions\HashCondition::class => Conditions\Builder\HashConditionBuilder::class,
            Conditions\BetweenColumnsCondition::class => Conditions\Builder\BetweenColumnsConditionBuilder::class,
        ];
    }

    /**
     * Extracts table alias if there is one or returns false.
     *
     * @param string $table
     *
     * @return array|bool
     *
     * @psalm-return string[]|bool
     */
    protected function extractAlias(string $table): array|bool
    {
        if (preg_match('/^(.*?)(?i:\s+as|)\s+([^ ]+)$/', $table, $matches)) {
            return $matches;
        }

        return false;
    }

    /**
     * Checks to see if the given limit is effective.
     *
     * @param mixed $limit the given limit.
     *
     * @return bool whether the limit is effective.
     */
    protected function hasLimit(mixed $limit): bool
    {
        return ($limit instanceof ExpressionInterface) || ctype_digit((string) $limit);
    }

    /**
     * Checks to see if the given offset is effective.
     *
     * @param mixed $offset the given offset.
     *
     * @return bool whether the offset is effective.
     */
    protected function hasOffset(mixed $offset): bool
    {
        return ($offset instanceof ExpressionInterface) || (ctype_digit((string)$offset) && (string)$offset !== '0');
    }

    /**
     * Quotes table names passed.
     *
     * @param array $tables
     * @param array $params
     *
     * @throws Exception|InvalidConfigException|NotSupportedException
     *
     * @return array
     */
    private function quoteTableNames(array $tables, array &$params): array
    {
        /** @psalm-var array<array-key, array|QueryInterface|string> $tables */
        foreach ($tables as $i => $table) {
            if ($table instanceof QueryInterface) {
                [$sql, $params] = $this->build($table, $params);
                $tables[$i] = "($sql) " . $this->quoter->quoteTableName((string) $i);
            } elseif (is_string($table) && is_string($i)) {
                if (!str_contains($table, '(')) {
                    $table = $this->quoter->quoteTableName($table);
                }
                $tables[$i] = "$table " . $this->quoter->quoteTableName($i);
            } elseif (is_string($table) && !str_contains($table, '(')) {
                $tableWithAlias = $this->extractAlias($table);
                if (is_array($tableWithAlias)) { // with alias
                    $tables[$i] = $this->quoter->quoteTableName($tableWithAlias[1]) . ' '
                        . $this->quoter->quoteTableName($tableWithAlias[2]);
                } else {
                    $tables[$i] = $this->quoter->quoteTableName($table);
                }
            }
        }

        return $tables;
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace Yiisoft\Db\QueryBuilder;
6
7			use Yiisoft\Db\Command\Param;
8			use Yiisoft\Db\Command\ParamBuilder;
9			use Yiisoft\Db\Exception\Exception;
10			use Yiisoft\Db\Exception\InvalidArgumentException;
11			use Yiisoft\Db\Exception\InvalidConfigException;
12			use Yiisoft\Db\Exception\NotSupportedException;
13			use Yiisoft\Db\Expression\Expression;
14			use Yiisoft\Db\Expression\ExpressionBuilder;
15			use Yiisoft\Db\Expression\ExpressionBuilderInterface;
16			use Yiisoft\Db\Expression\ExpressionInterface;
17			use Yiisoft\Db\QueryBuilder\Conditions\HashCondition;
18			use Yiisoft\Db\QueryBuilder\Conditions\Interface\ConditionInterface;
19			use Yiisoft\Db\QueryBuilder\Conditions\SimpleCondition;
20			use Yiisoft\Db\Query\Query;
21			use Yiisoft\Db\Query\QueryExpressionBuilder;
22			use Yiisoft\Db\Query\QueryInterface;
23			use Yiisoft\Db\Schema\QuoterInterface;
24			use Yiisoft\Db\Schema\SchemaInterface;
25
26			use function array_filter;
27			use function array_merge;
28			use function array_shift;
29			use function ctype_digit;
30			use function get_class;
31			use function implode;
32			use function is_array;
33			use function is_int;
34			use function is_string;
35			use function ltrim;
36			use function preg_match;
37			use function preg_split;
38			use function reset;
39			use function strtoupper;
40			use function trim;
41
42			abstract class DQLQueryBuilder implements DQLQueryBuilderInterface
43			{
44			protected string $separator = ' ';
45
46			/**
47			* @var array map of condition aliases to condition classes. For example:
48			*
49			* ```php
50			* return [
51			* 'LIKE' => \Yiisoft\Db\Condition\LikeCondition::class,
52			* ];
53			* ```
54			*
55			* This property is used by {@see createConditionFromArray} method.
56			* See default condition classes list in {@see defaultConditionClasses()} method.
57			*
58			* In case you want to add custom conditions support, use the {@see setConditionClasses()} method.
59			*
60			* @see setConditonClasses()
61			* @see defaultConditionClasses()
62			*/
63			protected array $conditionClasses = [];
64
65			/**
66			* @psalm-var array<string, class-string<ExpressionBuilderInterface>> maps expression class to expression builder
67			* class.
68			*
69			* For example:
70			*
71			* ```php
72			* [
73			* Expression::class => ExpressionBuilder::class
74			* ]
75			* ```
76			* This property is mainly used by {@see buildExpression()} to build SQL expressions form expression objects.
77			* See default values in {@see defaultExpressionBuilders()} method.
78			*
79			* {@see setExpressionBuilders()}
80			* {@see defaultExpressionBuilders()}
81			*/
82			protected array $expressionBuilders = [];
83
84			public function __construct(
85			private QueryBuilderInterface $queryBuilder,
86			private QuoterInterface $quoter,
87			private SchemaInterface $schema
88			) {
89			$this->expressionBuilders = $this->defaultExpressionBuilders();
90			$this->conditionClasses = $this->defaultConditionClasses();
91			}
92
93			public function build(QueryInterface $query, array $params = []): array
94			{
95			$query = $query->prepare($this->queryBuilder);
96			$params = empty($params) ? $query->getParams() : array_merge($params, $query->getParams());
97			$clauses = [
98			$this->buildSelect($query->getSelect(), $params, $query->getDistinct(), $query->getSelectOption()),
99			$this->buildFrom($query->getFrom(), $params),
100			$this->buildJoin($query->getJoin(), $params),
101			$this->buildWhere($query->getWhere(), $params),
102			$this->buildGroupBy($query->getGroupBy(), $params),
103			$this->buildHaving($query->getHaving(), $params),
104			];
105			$sql = implode($this->separator, array_filter($clauses));
106			$sql = $this->buildOrderByAndLimit($sql, $query->getOrderBy(), $query->getLimit(), $query->getOffset());
107
108			if (!empty($query->getOrderBy())) {
109			/** @psalm-var array<string, ExpressionInterface\|string> */
110			foreach ($query->getOrderBy() as $expression) {
111			if ($expression instanceof ExpressionInterface) {
112			$this->buildExpression($expression, $params);
113			}
114			}
115			}
116
117			if (!empty($query->getGroupBy())) {
118			/** @psalm-var array<string, ExpressionInterface\|string> */
119			foreach ($query->getGroupBy() as $expression) {
120			if ($expression instanceof ExpressionInterface) {
121			$this->buildExpression($expression, $params);
122			}
123			}
124			}
125
126			$union = $this->buildUnion($query->getUnion(), $params);
127
128			if ($union !== '') {
129			$sql = "($sql)$this->separator$union";
130			}
131
132			$with = $this->buildWithQueries($query->getWithQueries(), $params);
133
134			if ($with !== '') {
135			$sql = "$with$this->separator$sql";
136			}
137
138			return [$sql, $params];
139			}
140
141			public function buildColumns(array\|string $columns): string
142			{
143			if (!is_array($columns)) {
			0 ignored issues – show introduced 2022-07-07 10:17 UTC by Report Bug Copy Issue Report The condition `is_array($columns)` is always `true`. Loading history...
144			if (str_contains($columns, '(')) {
145			return $columns;
146			}
147
148			$rawColumns = $columns;
149			$columns = preg_split('/\s,\s/', $columns, -1, PREG_SPLIT_NO_EMPTY);
150
151			if ($columns === false) {
152			throw new InvalidArgumentException("$rawColumns is not valid columns.");
153			}
154			}
155
156			/** @psalm-var array<array-key, ExpressionInterface\|string> $columns */
157			foreach ($columns as $i => $column) {
158			if ($column instanceof ExpressionInterface) {
159			$columns[$i] = $this->buildExpression($column);
160			} elseif (!str_contains($column, '(')) {
161			$columns[$i] = $this->quoter->quoteColumnName($column);
162			}
163			}
164
165			/** @psalm-var string[] $columns */
166			return implode(', ', $columns);
167			}
168
169			public function buildCondition(array\|string\|ExpressionInterface\|null $condition, array &$params = []): string
170			{
171			if (is_array($condition)) {
			0 ignored issues – show introduced 2022-07-07 10:17 UTC by Report Bug Copy Issue Report The condition `is_array($condition)` is always `true`. Loading history...
172			if (empty($condition)) {
173			return '';
174			}
175
176			$condition = $this->createConditionFromArray($condition);
177			}
178
179			if ($condition instanceof ExpressionInterface) {
			0 ignored issues – show introduced 2022-07-07 10:17 UTC by Report Bug Copy Issue Report `$condition` is always a sub-type of `Yiisoft\Db\Expression\ExpressionInterface`. Loading history...
180			return $this->buildExpression($condition, $params);
181			}
182
183			return $condition ?? '';
184			}
185
186			/**
187			* @throws InvalidArgumentException
188			*
189			* @psalm-suppress UndefinedInterfaceMethod
190			* @psalm-suppress MixedMethodCall
191			*/
192			public function buildExpression(ExpressionInterface $expression, array &$params = []): string
193			{
194			$builder = $this->queryBuilder->getExpressionBuilder($expression);
195			return (string) $builder->build($expression, $params);
196			}
197
198			public function buildFrom(?array $tables, array &$params): string
199			{
200			if (empty($tables)) {
201			return '';
202			}
203
204			/** @psalm-var string[] */
205			$tables = $this->quoteTableNames($tables, $params);
206
207			return 'FROM ' . implode(', ', $tables);
208			}
209
210			public function buildGroupBy(array $columns, array &$params = []): string
211			{
212			if (empty($columns)) {
213			return '';
214			}
215
216			/** @psalm-var array<string, Expression\|string> $columns */
217			foreach ($columns as $i => $column) {
218			if ($column instanceof Expression) {
219			$columns[$i] = $this->buildExpression($column);
220			$params = array_merge($params, $column->getParams());
221			} elseif (!str_contains($column, '(')) {
222			$columns[$i] = $this->quoter->quoteColumnName($column);
223			}
224			}
225
226			return 'GROUP BY ' . implode(', ', $columns);
227			}
228
229			public function buildHaving(array\|ExpressionInterface\|string\|null $condition, array &$params = []): string
230			{
231			$having = $this->buildCondition($condition, $params);
232
233			return ($having === '') ? '' : ('HAVING ' . $having);
234			}
235
236			public function buildJoin(array $joins, array &$params): string
237			{
238			if (empty($joins)) {
239			return '';
240			}
241
242			/**
243			* @psalm-var array<
244			* array-key,
245			* array{
246			* 0?:string,
247			* 1?:array<array-key, Query\|string>\|string,
248			* 2?:array\|ExpressionInterface\|string\|null
249			* }\|null
250			* > $joins
251			*/
252			foreach ($joins as $i => $join) {
253			if (!is_array($join) \|\| !isset($join[0], $join[1])) {
254			throw new Exception(
255			'A join clause must be specified as an array of join type, join table, and optionally join '
256			. 'condition.'
257			);
258			}
259
260			/* 0:join type, 1:join table, 2:on-condition (optional) */
261			[$joinType, $table] = $join;
262
263			$tables = $this->quoteTableNames((array) $table, $params);
264
265			/** @var string $table */
266			$table = reset($tables);
267			$joins[$i] = "$joinType $table";
268
269			if (isset($join[2])) {
270			$condition = $this->buildCondition($join[2], $params);
271			if ($condition !== '') {
272			$joins[$i] .= ' ON ' . $condition;
273			}
274			}
275			}
276
277			/** @psalm-var array<string> $joins */
278			return implode($this->separator, $joins);
279			}
280
281			public function buildLimit(Expression\|int\|null $limit, Expression\|int\|null $offset): string
282			{
283			$sql = '';
284
285			if ($this->hasLimit($limit)) {
286			$sql = 'LIMIT ' . (string) $limit;
287			}
288
289			if ($this->hasOffset($offset)) {
290			$sql .= ' OFFSET ' . (string) $offset;
291			}
292
293			return ltrim($sql);
294			}
295
296			public function buildOrderBy(array $columns, array &$params = []): string
297			{
298			if (empty($columns)) {
299			return '';
300			}
301
302			$orders = [];
303
304			/** @psalm-var array<string, Expression\|int\|string> $columns */
305			foreach ($columns as $name => $direction) {
306			if ($direction instanceof Expression) {
307			$orders[] = $this->buildExpression($direction);
308			$params = array_merge($params, $direction->getParams());
309			} else {
310			$orders[] = $this->quoter->quoteColumnName($name) . ($direction === SORT_DESC ? ' DESC' : '');
311			}
312			}
313
314			return 'ORDER BY ' . implode(', ', $orders);
315			}
316
317			public function buildOrderByAndLimit(
318			string $sql,
319			array $orderBy,
320			Expression\|int\|null $limit,
321			Expression\|int\|null $offset,
322			array &$params = []
323			): string {
324			$orderBy = $this->buildOrderBy($orderBy, $params);
325			if ($orderBy !== '') {
326			$sql .= $this->separator . $orderBy;
327			}
328			$limit = $this->buildLimit($limit, $offset);
329			if ($limit !== '') {
330			$sql .= $this->separator . $limit;
331			}
332
333			return $sql;
334			}
335
336			public function buildSelect(
337			array $columns,
338			array &$params,
339			?bool $distinct = false,
340			?string $selectOption = null
341			): string {
342			$select = $distinct ? 'SELECT DISTINCT' : 'SELECT';
343
344			if ($selectOption !== null) {
345			$select .= ' ' . $selectOption;
346			}
347
348			if (empty($columns)) {
349			return $select . ' *';
350			}
351
352			/** @psalm-var array<array-key, ExpressionInterface\|Query\|string> $columns */
353			foreach ($columns as $i => $column) {
354			if ($column instanceof ExpressionInterface) {
355			if (is_int($i)) {
356			$columns[$i] = $this->buildExpression($column, $params);
357			} else {
358			$columns[$i] = $this->buildExpression($column, $params) . ' AS '
359			. $this->quoter->quoteColumnName($i);
360			}
361			} elseif ($column instanceof QueryInterface) {
362			[$sql, $params] = $this->build($column, $params);
363			$columns[$i] = "($sql) AS " . $this->quoter->quoteColumnName((string) $i);
364			} elseif (is_string($i) && $i !== $column) {
365			if (!str_contains($column, '(')) {
366			$column = $this->quoter->quoteColumnName($column);
367			}
368			$columns[$i] = "$column AS " . $this->quoter->quoteColumnName($i);
369			} elseif (!str_contains($column, '(')) {
370			if (preg_match('/^(.*?)(?i:\s+as\s+\|\s+)([\w\-_.]+)$/', $column, $matches)) {
371			$columns[$i] = $this->quoter->quoteColumnName($matches[1])
372			. ' AS ' . $this->quoter->quoteColumnName($matches[2]);
373			} else {
374			$columns[$i] = $this->quoter->quoteColumnName($column);
375			}
376			}
377			}
378
379			return $select . ' ' . implode(', ', $columns);
380			}
381
382			public function buildUnion(array $unions, array &$params): string
383			{
384			if (empty($unions)) {
385			return '';
386			}
387
388			$result = '';
389
390			/** @psalm-var array<array{query:Query\|string, all:bool}> $unions */
391			foreach ($unions as $i => $union) {
392			$query = $union['query'];
393			if ($query instanceof QueryInterface) {
394			[$unions[$i]['query'], $params] = $this->build($query, $params);
395			}
396
397			$result .= 'UNION ' . ($union['all'] ? 'ALL ' : '') . '( ' . $unions[$i]['query'] . ' ) ';
398			}
399
400			return trim($result);
401			}
402
403			public function buildWhere(
404			array\|string\|ConditionInterface\|ExpressionInterface\|null $condition,
405			array &$params = []
406			): string {
407			$where = $this->buildCondition($condition, $params);
408			return ($where === '') ? '' : ('WHERE ' . $where);
409			}
410
411			public function buildWithQueries(array $withs, array &$params): string
412			{
413			if (empty($withs)) {
414			return '';
415			}
416
417			$recursive = false;
418			$result = [];
419
420			/** @psalm-var array<array-key, array{query:string\|Query, alias:string, recursive:bool}> $withs */
421			foreach ($withs as $with) {
422			if ($with['recursive']) {
423			$recursive = true;
424			}
425
426			$query = $with['query'];
427			if ($query instanceof QueryInterface) {
428			[$with['query'], $params] = $this->build($query, $params);
429			}
430
431			$result[] = $with['alias'] . ' AS (' . $with['query'] . ')';
432			}
433
434			return 'WITH ' . ($recursive ? 'RECURSIVE ' : '') . implode(', ', $result);
435			}
436
437			public function createConditionFromArray(array $condition): ConditionInterface
438			{
439			/** operator format: operator, operand 1, operand 2, ... */
440			if (isset($condition[0])) {
441			$operator = strtoupper((string) array_shift($condition));
442
443			/** @var string $className */
444			$className = $this->conditionClasses[$operator] ?? SimpleCondition::class;
445
446			/** @var ConditionInterface $className */
447			return $className::fromArrayDefinition($operator, $condition);
448			}
449
450			/** hash format: 'column1' => 'value1', 'column2' => 'value2', ... */
451			return new HashCondition($condition);
452			}
453
454			public function getExpressionBuilder(ExpressionInterface $expression): object
455			{
456			$className = get_class($expression);
457
458			if (!isset($this->expressionBuilders[$className])) {
459			throw new InvalidArgumentException(
460			'Expression of class ' . $className . ' can not be built in ' . static::class
461			);
462			}
463
464			return new $this->expressionBuilders[$className]($this->queryBuilder);
465			}
466
467			public function selectExists(string $rawSql): string
468			{
469			return 'SELECT EXISTS(' . $rawSql . ')';
470			}
471
472			public function setConditionClasses(array $classes): void
473			{
474			$this->conditionClasses = array_merge($this->conditionClasses, $classes);
475			}
476
477			public function setExpressionBuilders(array $builders): void
478			{
479			$this->expressionBuilders = array_merge($this->expressionBuilders, $builders);
480			}
481
482			/**
483			* @param string the separator between different fragments of a SQL statement.
			0 ignored issues – show Bug introduced 2022-07-07 10:17 UTC by Report Bug Copy Issue Report The type `Yiisoft\Db\QueryBuilder\the` was not found. Maybe you did not declare it correctly or list all dependencies? The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g. `excluded_paths: ["lib/"]`, you can move it to the dependency path list as follows: filter: dependency_paths: ["lib/"] For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths Loading history...
484			*
485			* Defaults to an empty space. This is mainly used by {@see build()} when generating a SQL statement.
486			*/
487			public function setSeparator(string $separator): void
488			{
489			$this->separator = $separator;
490			}
491
492			/**
493			* Contains array of default condition classes. Extend this method, if you want to change default condition classes
494			* for the query builder.
495			*
496			* @return array
497			*
498			* See {@see conditionClasses} docs for details.
499			*/
500			protected function defaultConditionClasses(): array
501			{
502			return [
503			'NOT' => Conditions\NotCondition::class,
504			'AND' => Conditions\AndCondition::class,
505			'OR' => Conditions\OrCondition::class,
506			'BETWEEN' => Conditions\BetweenCondition::class,
507			'NOT BETWEEN' => Conditions\BetweenCondition::class,
508			'IN' => Conditions\InCondition::class,
509			'NOT IN' => Conditions\InCondition::class,
510			'LIKE' => Conditions\LikeCondition::class,
511			'NOT LIKE' => Conditions\LikeCondition::class,
512			'OR LIKE' => Conditions\LikeCondition::class,
513			'OR NOT LIKE' => Conditions\LikeCondition::class,
514			'EXISTS' => Conditions\ExistsCondition::class,
515			'NOT EXISTS' => Conditions\ExistsCondition::class,
516			];
517			}
518
519			/**
520			* Contains array of default expression builders. Extend this method and override it, if you want to change default
521			* expression builders for this query builder.
522			*
523			* @return array
524			*
525			* See {@see expressionBuilders} docs for details.
526			*
527			* @psalm-return array<string, class-string<ExpressionBuilderInterface>>
528			*/
529			protected function defaultExpressionBuilders(): array
530			{
531			return [
532			Query::class => QueryExpressionBuilder::class,
533			Param::class => ParamBuilder::class,
534			Expression::class => ExpressionBuilder::class,
535			Conditions\ConjunctionCondition::class => Conditions\Builder\ConjunctionConditionBuilder::class,
536			Conditions\NotCondition::class => Conditions\Builder\NotConditionBuilder::class,
537			Conditions\AndCondition::class => Conditions\Builder\ConjunctionConditionBuilder::class,
538			Conditions\OrCondition::class => Conditions\Builder\ConjunctionConditionBuilder::class,
539			Conditions\BetweenCondition::class => Conditions\Builder\BetweenConditionBuilder::class,
540			Conditions\InCondition::class => Conditions\Builder\InConditionBuilder::class,
541			Conditions\LikeCondition::class => Conditions\Builder\LikeConditionBuilder::class,
542			Conditions\ExistsCondition::class => Conditions\Builder\ExistsConditionBuilder::class,
543			Conditions\SimpleCondition::class => Conditions\Builder\SimpleConditionBuilder::class,
544			Conditions\HashCondition::class => Conditions\Builder\HashConditionBuilder::class,
545			Conditions\BetweenColumnsCondition::class => Conditions\Builder\BetweenColumnsConditionBuilder::class,
546			];
547			}
548
549			/**
550			* Extracts table alias if there is one or returns false.
551			*
552			* @param string $table
553			*
554			* @return array\|bool
555			*
556			* @psalm-return string[]\|bool
557			*/
558			protected function extractAlias(string $table): array\|bool
559			{
560			if (preg_match('/^(.*?)(?i:\s+as\|)\s+([^ ]+)$/', $table, $matches)) {
561			return $matches;
562			}
563
564			return false;
565			}
566
567			/**
568			* Checks to see if the given limit is effective.
569			*
570			* @param mixed $limit the given limit.
571			*
572			* @return bool whether the limit is effective.
573			*/
574			protected function hasLimit(mixed $limit): bool
575			{
576			return ($limit instanceof ExpressionInterface) \|\| ctype_digit((string) $limit);
577			}
578
579			/**
580			* Checks to see if the given offset is effective.
581			*
582			* @param mixed $offset the given offset.
583			*
584			* @return bool whether the offset is effective.
585			*/
586			protected function hasOffset(mixed $offset): bool
587			{
588			return ($offset instanceof ExpressionInterface) \|\| (ctype_digit((string)$offset) && (string)$offset !== '0');
589			}
590
591			/**
592			* Quotes table names passed.
593			*
594			* @param array $tables
595			* @param array $params
596			*
597			* @throws Exception\|InvalidConfigException\|NotSupportedException
598			*
599			* @return array
600			*/
601			private function quoteTableNames(array $tables, array &$params): array
602			{
603			/** @psalm-var array<array-key, array\|QueryInterface\|string> $tables */
604			foreach ($tables as $i => $table) {
605			if ($table instanceof QueryInterface) {
606			[$sql, $params] = $this->build($table, $params);
607			$tables[$i] = "($sql) " . $this->quoter->quoteTableName((string) $i);
608			} elseif (is_string($table) && is_string($i)) {
609			if (!str_contains($table, '(')) {
610			$table = $this->quoter->quoteTableName($table);
611			}
612			$tables[$i] = "$table " . $this->quoter->quoteTableName($i);
613			} elseif (is_string($table) && !str_contains($table, '(')) {
614			$tableWithAlias = $this->extractAlias($table);
615			if (is_array($tableWithAlias)) { // with alias
616			$tables[$i] = $this->quoter->quoteTableName($tableWithAlias[1]) . ' '
617			. $this->quoter->quoteTableName($tableWithAlias[2]);
618			} else {
619			$tables[$i] = $this->quoter->quoteTableName($table);
620			}
621			}
622			}
623
624			return $tables;
625			}
626			}
627

yiisoft / db

Pull Request — master (#343)

DQLQueryBuilder::buildOrderBy() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like