QueryBuilder::join() - Code Metrics - Inspection of "Merge pull request #26 from tflori/fix-whereEmpty" - tflori/orm - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 85cf16...ce3632 )

by Thomas

created 2017-02-10 07:05 UTC

QueryBuilder::join() A

↳ Parent: QueryBuilder

Complexity

Conditions	3
Paths	1

Size

Total Lines	11
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	5
CRAP Score	3

Importance

Changes

Metric	Value
c	0
b	0
f	0
dl	0
loc	11
ccs	5
cts	5
cp	1
rs	9.4285
cc	3
eloc	8
nc	1
nop	4
crap	3

<?php

namespace ORM\QueryBuilder;

use ORM\EntityManager;
use ORM\Exception;
use ORM\Exceptions\NoOperator;

/**
 * Build a ansi sql query / select statement
 *
 * If you need more specific queries you write them yourself. If you need just more specific where clause you can pass
 * them to the *where() methods.
 *
 * Supported:
 *  - joins with on clause (and alias)
 *  - joins with using (and alias)
 *  - where conditions
 *  - parenthesis
 *  - order by one or more columns / expressions
 *  - group by one or more columns / expressions
 *  - limit and offset
 *
 * @package ORM
 * @author Thomas Flori <[email protected]>
 */
class QueryBuilder extends Parenthesis implements QueryBuilderInterface
{
    /** The table to query
     * @var string */
    protected $tableName = '';

    /** The alias of the main table
     * @var string */
    protected $alias = '';

    /** Columns to fetch (null is equal to ['*'])
     * @var array|null */
    protected $columns = null;

    /** Joins get concatenated with space
     * @var string[] */
    protected $joins = [];

    /** Limit amount of rows
     * @var int */
    protected $limit;

    /** Offset to start from
     * @var int */
    protected $offset;

    /** Group by conditions get concatenated with comma
     * @var string[] */
    protected $groupBy = [];

    /** Order by conditions get concatenated with comma
     * @var string[] */
    protected $orderBy = [];

    /** Modifiers get concatenated with space
     * @var string[] */
    protected $modifier = [];

    /** EntityManager to use for quoting
     * @var EntityManager */
    protected $entityManager;

    /** The default EntityManager to use to for quoting
     * @var EntityManager */
    public static $defaultEntityManager;

    /** @noinspection PhpMissingParentConstructorInspection */
    /**
     * Constructor
     *
     * Create a select statement for $tableName with an object oriented interface.
     *
     * It uses static::$defaultEntityManager if $entityManager is not given.
     *
     * @param string        $tableName     The main table to use in FROM clause
     * @param string        $alias         An alias for the table
     * @param EntityManager $entityManager EntityManager for quoting
     */
    public function __construct($tableName, $alias = '', EntityManager $entityManager = null)
    {
        $this->tableName = $tableName;
        $this->alias = $alias;
        $this->entityManager = $entityManager;
    }

    /**
     * Replaces question marks in $expression with $args
     *
     * @param string      $expression Expression with placeholders
     * @param array|mixed $args       Arguments for placeholders
     * @return string
     * @throws \ORM\Exceptions\NoConnection
     * @throws \ORM\Exceptions\NotScalar
     */
    protected function convertPlaceholders(
        $expression,
        $args
    ) {
        if (strpos($expression, '?') === false) {
            return $expression;
        }

        if (!is_array($args)) {
            $args = [$args];
        }

        $parts = explode('?', $expression);
        $expression = '';
        while ($part = array_shift($parts)) {
            $expression .= $part;
            if (count($args)) {
                $expression .= $this->getEntityManager()->escapeValue(array_shift($args));
            } elseif (count($parts)) {
                $expression .= '?';
            }
        }

        return $expression;
    }

    /**
     * @return EntityManager
     */
    public function getEntityManager()
    {
        return $this->entityManager ?: static::$defaultEntityManager;
    }

    /**
     * Common implementation for creating a where condition
     *
     * @param string $column   Column or expression with placeholders
     * @param string $operator Operator or value if operator is omited
     * @param string $value    Value or array of values
     * @return string
     * @throws NoOperator
     * @internal
     */
    public function createWhereCondition($column, $operator = null, $value = null)
    {
        if (strpos($column, '?') !== false) {
            $expression = $column;
            $value      = $operator;
        } elseif ($operator === null && $value === null) {
            $expression = $column;
        } else {
            if ($value === null) {
                $value = $operator;
                $operator = null;
            }

            $expression = $this->buildExpression($column, $value, $operator);
        }

        $whereCondition = $this->convertPlaceholders($expression, $value);

        return $whereCondition;
    }

    private function buildExpression($column, $value, $operator = null)
    {
        $operator = $operator ?: $this->getDefaultOperator($value);
        $expression = $column . ' ' . $operator;

        if (in_array(strtoupper($operator), ['IN', 'NOT IN']) && is_array($value)) {
            $expression .= ' (?' . str_repeat(',?', count($value) - 1) . ')';
        } else {
            $expression .= ' ?';
        }

        return $expression;
    }

    private function getDefaultOperator($value)
    {
        if (is_array($value)) {
            return 'IN';
        } else {
            return '=';
        }
    }

    /** {@inheritdoc} */
    public function columns(array $columns = null)
    {
        $this->columns = $columns;

        return $this;
    }

    /** {@inheritdoc} */
    public function column($column, $args = [], $alias = '')
    {
        if ($this->columns === null) {
            $this->columns = [];
        }

        $expression = $this->convertPlaceholders($column, $args);

        $this->columns[] = $expression . ($alias ? ' AS ' . $alias : '');

        return $this;
    }

    /** @internal
     * @return self */
    public function close()
    {
        return $this;
    }

    /**
     * Common implementation for *Join methods
     *
     * @param string      $join       The join type (e. g. `LEFT JOIN`)
     * @param string      $tableName  Table name to join
     * @param string      $expression Expression to use in on clause or single column for USING
     * @param string      $alias      Alias for the table
     * @param array|mixed $args       Arguments to use in $expression
     * @param bool        $empty      Create an empty join (without USING and ON)
     * @return ParenthesisInterface|QueryBuilder
     * @throws \ORM\Exceptions\NoConnection
     * @throws \ORM\Exceptions\NotScalar
     * @internal
     */
    protected function createJoin($join, $tableName, $expression, $alias, $args, $empty)
    {
        $join = $join . ' ' . $tableName
                . ($alias ? ' AS ' . $alias : '');

        if (preg_match('/^[A-Za-z_]+$/', $expression)) {
            $join .= ' USING (' . $expression . ')';
            $this->joins[] = $join;
        } elseif ($expression) {
            $expression = $this->convertPlaceholders($expression, $args);

            $join .= ' ON ' . $expression;
            $this->joins[] = $join;
        } elseif ($empty) {
            $this->joins[] = $join;
        } else {
            return new Parenthesis(function (ParenthesisInterface $parenthesis) use ($join) {
                $join .= ' ON ' . $parenthesis->getExpression();
                $this->joins[] = $join;
                return $this;
            }, $this);
        }

        return $this;
    }

    /** {@inheritdoc} */
    public function join($tableName, $expression = '', $alias = '', $args = [])
    {
        return $this->createJoin(
            'JOIN',
            $tableName,
            is_string($expression) ? $expression : '',
            $alias,
            $args,
            is_bool($expression) ? $expression : false
        );
    }

    /** {@inheritdoc} */
    public function leftJoin($tableName, $expression = '', $alias = '', $args = [])
    {
        return $this->createJoin(
            'LEFT JOIN',
            $tableName,
            is_string($expression) ? $expression : '',
            $alias,
            $args,
            is_bool($expression) ? $expression : false
        );
    }

    /** {@inheritdoc} */
    public function rightJoin($tableName, $expression = '', $alias = '', $args = [])
    {
        return $this->createJoin(
            'RIGHT JOIN',
            $tableName,
            is_string($expression) ? $expression : '',
            $alias,
            $args,
            is_bool($expression) ? $expression : false
        );
    }

    /** {@inheritdoc} */
    public function fullJoin($tableName, $expression = '', $alias = '', $args = [])
    {
        return $this->createJoin(
            'FULL JOIN',
            $tableName,
            is_string($expression) ? $expression : '',
            $alias,
            $args,
            is_bool($expression) ? $expression : true
        );
    }

    /** {@inheritdoc} */
    public function groupBy($column, $args = [])
    {
        $this->groupBy[] = $this->convertPlaceholders($column, $args);

        return $this;
    }

    /** {@inheritdoc} */
    public function orderBy($column, $direction = self::DIRECTION_ASCENDING, $args = [])
    {
        $expression = $this->convertPlaceholders($column, $args);

        $this->orderBy[] = $expression . ' ' . $direction;

        return $this;
    }

    /** {@inheritdoc} */
    public function limit($limit)
    {
        $this->limit = (int) $limit;

        return $this;
    }

    /** {@inheritdoc} */
    public function offset($offset)
    {
        $this->offset = (int) $offset;

        return $this;
    }

    /** {@inheritdoc} */
    public function getQuery()
    {
        return 'SELECT '
               . (!empty($this->modifier) ? implode(' ', $this->modifier) . ' ' : '')
               . ($this->columns ? implode(',', $this->columns) : '*')
               . ' FROM ' . $this->tableName . ($this->alias ? ' AS ' . $this->alias : '')
               . (!empty($this->joins) ? ' ' . implode(' ', $this->joins) : '')
               . (!empty($this->where) ? ' WHERE ' . implode(' ', $this->where) : '')
               . (!empty($this->groupBy) ? ' GROUP BY ' . implode(',', $this->groupBy) : '')
               . (!empty($this->orderBy) ? ' ORDER BY ' . implode(',', $this->orderBy) : '')
               . ($this->limit ? ' LIMIT ' . $this->limit . ($this->offset ? ' OFFSET ' . $this->offset : '') : '');
    }

    /** {@inheritdoc} */
    public function modifier($modifier)
    {
        $this->modifier[] = $modifier;

        return $this;
    }
}


1		<?php
2
3		namespace ORM\QueryBuilder;
4
5		use ORM\EntityManager;
6		use ORM\Exception;
7		use ORM\Exceptions\NoOperator;
8
9		/**
10		* Build a ansi sql query / select statement
11		*
12		* If you need more specific queries you write them yourself. If you need just more specific where clause you can pass
13		* them to the *where() methods.
14		*
15		* Supported:
16		* - joins with on clause (and alias)
17		* - joins with using (and alias)
18		* - where conditions
19		* - parenthesis
20		* - order by one or more columns / expressions
21		* - group by one or more columns / expressions
22		* - limit and offset
23		*
24		* @package ORM
25		* @author Thomas Flori <[email protected]>
26		*/
27		class QueryBuilder extends Parenthesis implements QueryBuilderInterface
28		{
29		/** The table to query
30		* @var string */
31		protected $tableName = '';
32
33		/** The alias of the main table
34		* @var string */
35		protected $alias = '';
36
37		/** Columns to fetch (null is equal to ['*'])
38		* @var array\|null */
39		protected $columns = null;
40
41		/** Joins get concatenated with space
42		* @var string[] */
43		protected $joins = [];
44
45		/** Limit amount of rows
46		* @var int */
47		protected $limit;
48
49		/** Offset to start from
50		* @var int */
51		protected $offset;
52
53		/** Group by conditions get concatenated with comma
54		* @var string[] */
55		protected $groupBy = [];
56
57		/** Order by conditions get concatenated with comma
58		* @var string[] */
59		protected $orderBy = [];
60
61		/** Modifiers get concatenated with space
62		* @var string[] */
63		protected $modifier = [];
64
65		/** EntityManager to use for quoting
66		* @var EntityManager */
67		protected $entityManager;
68
69		/** The default EntityManager to use to for quoting
70		* @var EntityManager */
71		public static $defaultEntityManager;
72
73		/** @noinspection PhpMissingParentConstructorInspection */
74		/**
75		* Constructor
76		*
77		* Create a select statement for $tableName with an object oriented interface.
78		*
79		* It uses static::$defaultEntityManager if $entityManager is not given.
80		*
81		* @param string $tableName The main table to use in FROM clause
82		* @param string $alias An alias for the table
83		* @param EntityManager $entityManager EntityManager for quoting
84		*/
85	116	public function __construct($tableName, $alias = '', EntityManager $entityManager = null)
86		{
87	116	$this->tableName = $tableName;
88	116	$this->alias = $alias;
89	116	$this->entityManager = $entityManager;
90	116	}
91
92		/**
93		* Replaces question marks in $expression with $args
94		*
95		* @param string $expression Expression with placeholders
96		* @param array\|mixed $args Arguments for placeholders
97		* @return string
98		* @throws \ORM\Exceptions\NoConnection
99		* @throws \ORM\Exceptions\NotScalar
100		*/
101	136	protected function convertPlaceholders(
102		$expression,
103		$args
104		) {
105	136	if (strpos($expression, '?') === false) {
106	86	return $expression;
107		}
108
109	94	if (!is_array($args)) {
110	60	$args = [$args];
111		}
112
113	94	$parts = explode('?', $expression);
114	94	$expression = '';
115	94	while ($part = array_shift($parts)) {
116	94	$expression .= $part;
117	94	if (count($args)) {
118	94	$expression .= $this->getEntityManager()->escapeValue(array_shift($args));
119	40	} elseif (count($parts)) {
120	1	$expression .= '?';
121		}
122		}
123
124	94	return $expression;
125		}
126
127		/**
128		* @return EntityManager
129		*/
130	96	public function getEntityManager()
131		{
132	96	return $this->entityManager ?: static::$defaultEntityManager;
133		}
134
135		/**
136		* Common implementation for creating a where condition
137		*
138		* @param string $column Column or expression with placeholders
139		* @param string $operator Operator or value if operator is omited
140		* @param string $value Value or array of values
141		* @return string
142		* @throws NoOperator
143		* @internal
144		*/
145	94	public function createWhereCondition($column, $operator = null, $value = null)
146		{
147	94	if (strpos($column, '?') !== false) {
148	16	$expression = $column;
149	16	$value = $operator;
150	89	} elseif ($operator === null && $value === null) {
151	52	$expression = $column;
152		} else {
153	67	if ($value === null) {
154	45	$value = $operator;
155	45	$operator = null;
156		}
157
158	67	$expression = $this->buildExpression($column, $value, $operator);
159		}
160
161	94	$whereCondition = $this->convertPlaceholders($expression, $value);
162
163	93	return $whereCondition;
164		}
165
166	67	private function buildExpression($column, $value, $operator = null)
167		{
168	67	$operator = $operator ?: $this->getDefaultOperator($value);
169	67	$expression = $column . ' ' . $operator;
170
171	67	if (in_array(strtoupper($operator), ['IN', 'NOT IN']) && is_array($value)) {
172	22	$expression .= ' (?' . str_repeat(',?', count($value) - 1) . ')';
173		} else {
174	46	$expression .= ' ?';
175		}
176
177	67	return $expression;
178		}
179
180	46	private function getDefaultOperator($value)
181		{
182	46	if (is_array($value)) {
183	14	return 'IN';
184		} else {
185	32	return '=';
186		}
187		}
188
189		/** {@inheritdoc} */
190	2	public function columns(array $columns = null)
191		{
192	2	$this->columns = $columns;
193
194	2	return $this;
195		}
196
197		/** {@inheritdoc} */
198	5	public function column($column, $args = [], $alias = '')
199		{
200	5	if ($this->columns === null) {
201	5	$this->columns = [];
202		}
203
204	5	$expression = $this->convertPlaceholders($column, $args);
205
206	5	$this->columns[] = $expression . ($alias ? ' AS ' . $alias : '');
207
208	5	return $this;
209		}
210
211		/** @internal
212		* @return self */
213	1	public function close()
214		{
215	1	return $this;
216		}
217
218		/**
219		* Common implementation for *Join methods
220		*
221		* @param string $join The join type (e. g. `LEFT JOIN`)
222		* @param string $tableName Table name to join
223		* @param string $expression Expression to use in on clause or single column for USING
224		* @param string $alias Alias for the table
225		* @param array\|mixed $args Arguments to use in $expression
226		* @param bool $empty Create an empty join (without USING and ON)
227		* @return ParenthesisInterface\|QueryBuilder
228		* @throws \ORM\Exceptions\NoConnection
229		* @throws \ORM\Exceptions\NotScalar
230		* @internal
231		*/
232	42	protected function createJoin($join, $tableName, $expression, $alias, $args, $empty)
233		{
234	42	$join = $join . ' ' . $tableName
235	42	. ($alias ? ' AS ' . $alias : '');
236
237	42	if (preg_match('/^[A-Za-z_]+$/', $expression)) {
238	4	$join .= ' USING (' . $expression . ')';
239	4	$this->joins[] = $join;
240	38	} elseif ($expression) {
241	30	$expression = $this->convertPlaceholders($expression, $args);
242
243	30	$join .= ' ON ' . $expression;
244	30	$this->joins[] = $join;
245	8	} elseif ($empty) {
246	4	$this->joins[] = $join;
247		} else {
248	4	return new Parenthesis(function (ParenthesisInterface $parenthesis) use ($join) {
249	4	$join .= ' ON ' . $parenthesis->getExpression();
250	4	$this->joins[] = $join;
251	4	return $this;
252	4	}, $this);
253		}
254
255	38	return $this;
256		}
257
258		/** {@inheritdoc} */
259	17	public function join($tableName, $expression = '', $alias = '', $args = [])
260		{
261	17	return $this->createJoin(
262	17	'JOIN',
263		$tableName,
264	17	is_string($expression) ? $expression : '',
265		$alias,
266		$args,
267	17	is_bool($expression) ? $expression : false
268		);
269		}
270
271		/** {@inheritdoc} */
272	11	public function leftJoin($tableName, $expression = '', $alias = '', $args = [])
273		{
274	11	return $this->createJoin(
275	11	'LEFT JOIN',
276		$tableName,
277	11	is_string($expression) ? $expression : '',
278		$alias,
279		$args,
280	11	is_bool($expression) ? $expression : false
281		);
282		}
283
284		/** {@inheritdoc} */
285	7	public function rightJoin($tableName, $expression = '', $alias = '', $args = [])
286		{
287	7	return $this->createJoin(
288	7	'RIGHT JOIN',
289		$tableName,
290	7	is_string($expression) ? $expression : '',
291		$alias,
292		$args,
293	7	is_bool($expression) ? $expression : false
294		);
295		}
296
297		/** {@inheritdoc} */
298	7	public function fullJoin($tableName, $expression = '', $alias = '', $args = [])
299		{
300	7	return $this->createJoin(
301	7	'FULL JOIN',
302		$tableName,
303	7	is_string($expression) ? $expression : '',
304		$alias,
305		$args,
306	7	is_bool($expression) ? $expression : true
307		);
308		}
309
310		/** {@inheritdoc} */
311	4	public function groupBy($column, $args = [])
312		{
313	4	$this->groupBy[] = $this->convertPlaceholders($column, $args);
314
315	4	return $this;
316		}
317
318		/** {@inheritdoc} */
319	5	public function orderBy($column, $direction = self::DIRECTION_ASCENDING, $args = [])
320		{
321	5	$expression = $this->convertPlaceholders($column, $args);
322
323	5	$this->orderBy[] = $expression . ' ' . $direction;
324
325	5	return $this;
326		}
327
328		/** {@inheritdoc} */
329	2	public function limit($limit)
330		{
331	2	$this->limit = (int) $limit;
332
333	2	return $this;
334		}
335
336		/** {@inheritdoc} */
337	1	public function offset($offset)
338		{
339	1	$this->offset = (int) $offset;
340
341	1	return $this;
342		}
343
344		/** {@inheritdoc} */
345	156	public function getQuery()
346		{
347		return 'SELECT '
348	156	. (!empty($this->modifier) ? implode(' ', $this->modifier) . ' ' : '')
349	156	. ($this->columns ? implode(',', $this->columns) : '*')
350	156	. ' FROM ' . $this->tableName . ($this->alias ? ' AS ' . $this->alias : '')
351	156	. (!empty($this->joins) ? ' ' . implode(' ', $this->joins) : '')
352	156	. (!empty($this->where) ? ' WHERE ' . implode(' ', $this->where) : '')
353	156	. (!empty($this->groupBy) ? ' GROUP BY ' . implode(',', $this->groupBy) : '')
354	156	. (!empty($this->orderBy) ? ' ORDER BY ' . implode(',', $this->orderBy) : '')
355	156	. ($this->limit ? ' LIMIT ' . $this->limit . ($this->offset ? ' OFFSET ' . $this->offset : '') : '');
356		}
357
358		/** {@inheritdoc} */
359	1	public function modifier($modifier)
360		{
361	1	$this->modifier[] = $modifier;
362
363	1	return $this;
364		}
365		}
366

tflori / orm

Push — master ( 85cf16...ce3632 )

QueryBuilder::join() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like