BaseQuery::compileJoins() - Code Metrics - gamernetwork/yolk-database - Measure and Improve Code Quality continuously with Scrutinizer

BaseQuery::compileJoins() A
last analyzed 2016-05-18 08:44 UTC

↳ Parent: BaseQuery

Complexity

Conditions	3
Paths	3

Size

Total Lines	15
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	1

Metric	Value
c	1
b	0
f	1
dl	0
loc	15
rs	9.4285
cc	3
eloc	8
nc	3
nop	0

<?php
/*
 * This file is part of Yolk - Gamer Network's PHP Framework.
 *
 * Copyright (c) 2015 Gamer Network Ltd.
 *
 * Distributed under the MIT License, a copy of which is available in the
 * LICENSE file that was bundled with this package, or online at:
 * https://github.com/gamernetwork/yolk-database
 */

namespace yolk\database\query;

use yolk\contracts\database\DatabaseConnection;
use yolk\contracts\database\Query;

/**
 * Generic.
 */
abstract class BaseQuery implements Query {

	/**
	 * Database connection the query is associated with.
	 * @var DatabaseConnection
	 */
	protected $db;

	/**
	 * Array of join clauses.
	 * @var array
	 */
	protected $joins;

	/**
	 * Array of where clauses.
	 * @var array
	 */
	protected $where;

	/**
	 * Array of order by clauses.
	 * @var array
	 */
	protected $order;

	/**
	 * Query offset.
	 * @var integer
	 */
	protected $offset;

	/**
	 * Query limit.
	 * @var integer|null
	 */
	protected $limit;

	/**
	 * Array of query parameters
	 * @var array
	 */
	protected $params;

	public function __construct( DatabaseConnection $db ) {
		$this->db       = $db;
		$this->joins    = [];
		$this->where    = [];
		$this->order    = [];
		$this->offset   = 0;
		$this->limit    = null;
		$this->params   = [];
	}

	public function __toString() {
		return implode("\n", $this->compile());
		return d(implode("\n", $this->compile()), $this->params);
function fx() {
    try {
        doSomething();
        return true;
    }
    catch (\Exception $e) {
        return false;
    }

    return false;
}
	}

	public function innerJoin( $table, array $on ) {
		$this->joins[] = ['INNER', $table, $on];
		return $this;
	}

	public function leftJoin( $table, array $on ) {
		$this->joins[] = ['LEFT', $table, $on];
		return $this;
	}

	public function joinRaw( $sql, $parameters = [] ) {
		$this->joins[] = $sql;
		$this->params = array_merge($this->params, $parameters);
		return $this;
	}

	public function where( $column, $operator, $value = null ) {

		// shortcut for equals
		if( func_num_args() == 2 ) {
			$value    = $operator;
			$operator = '=';
		}

		$operator = trim(strtoupper($operator));

		// can't bind IN values as parameters so we escape them and embed them directly
		if( in_array($operator, ['IN', 'NOT IN']) && is_array($value) ) {
			$value = $this->makeInClause($value);
		}
		// do parameter binding
		else {
			$value = $this->bindParam(
				$this->getParameterName($column, $operator),
				$value
			);
		}

		$this->where[] = [$this->quoteIdentifier($column), $operator, $value];

		return $this;

	}

	public function whereArray( array $where ) {

		foreach( $where as $k => $v ) {
			$operator = is_array($v) ? 'IN' : '=';
			$this->where($k, $operator, $v);
		}

		return $this;

	}

	public function whereRaw( $sql, $parameters = [] ) {
		$this->where[] = $sql;
		$this->params = array_merge($this->params, $parameters);
		return $this;
	}

	public function orderBy( $column, $ascending = true ) {
		$column = $this->quoteIdentifier($column);
		$this->order[$column] = (bool) $ascending ? 'ASC' : 'DESC';
		return $this;
	}

	public function offset( $offset ) {
		$this->offset = max(0, (int) $offset);
		return $this;
	}

	public function limit( $limit ) {
		$this->limit = max(1, (int) $limit);
		return $this;
	}

	public function getParameters() {
		return $this->params;
	}

	public function setParameters( array $params, $replace = false ) {

		if( $replace )
			$this->params = $params;
		else
			$this->params = array_merge($this->params, $params);
		return $this;
	}

	/**
	 * Generate a SQL string as an array.
	 * @return array
	 */
	abstract protected function compile();

	protected function compileJoins() {

		$sql = [];

		foreach( $this->joins as $join ) {
			if( is_array($join) ) {
				list($type, $table, $on) = $join;
				$join = sprintf("%s JOIN %s\nON %s", $type, $this->quoteIdentifier($table), $this->compileOn($on));
			}
			$sql[] = $join;
		}

		return $sql;

	}

	protected function compileOn( array $on ) {

		$sql = [];

		foreach( $on as $column => $value ) {
			// if it's not a number or a quoted sring it much be an identifier, so quote it
			if( !is_numeric($value) && !preg_match('/^\'.*\'$/', $value) )
				$value = $this->quoteIdentifier($value);
			$sql[] = sprintf("%s = %s", $this->quoteIdentifier($column), $value);
		}

		return implode("\nAND ", $sql);

	}

	protected function compileWhere() {

		$sql = [];

		foreach( $this->where as $i => $clause ) {
			if( is_array($clause) )
				$clause = implode(' ', $clause);
			$sql[] = ($i ? 'AND ' : 'WHERE '). $clause;
		}

		return $sql;

	}

	protected function compileOrderBy() {

		$sql = [];

		if( $this->order ) {

			$order = 'ORDER BY ';
			foreach( $this->order as $column => $dir ) {
				$order .= $column. ' '. $dir. ', ';
			}
			$sql[] = trim($order, ', ');
		}

		return $sql;

	}

	protected function compileOffsetLimit() {

		$sql = [];

		$limit  = $this->limit;
		$offset = $this->offset;

		if( $limit || $offset ) {
0   == false // true
0   == null  // true
123 == false // false
123 == null  // false

// It is often better to use strict comparison
0 === false // false
0 === null  // false

			if( !$limit )
0   == false // true
0   == null  // true
123 == false // false
123 == null  // false

// It is often better to use strict comparison
0 === false // false
0 === null  // false
				$limit = PHP_INT_MAX;

			$sql[] = sprintf(
				"LIMIT %s OFFSET %s",
				$this->bindParam('_limit', $limit),
				$this->bindParam('_offset', $offset)
			);

		}

		return $sql;

	}

	protected function quoteIdentifier( $spec ) {

		// don't quote things that are functions/expressions
		if( strpos($spec, '(') !== false )
			return $spec;

		foreach( [' AS ', ' ', '.'] as $sep) {
			if( $pos = strripos($spec, $sep) ) {
				return
					$this->quoteIdentifier(substr($spec, 0, $pos)).
					$sep.
					$this->db->quoteIdentifier(substr($spec, $pos + strlen($sep)));
			}
		}

		return $this->db->quoteIdentifier($spec);

	}

	/**
	 * Join an array of values to form a string suitable for use in a SQL IN clause.
	 * The numeric parameter determines whether values are escaped and quoted;
	 * a null value (the default) will cause the function to auto-detect whether
	 * values should be escaped and quoted.
	 * 
	 * @param  array          $values
	 * @param  null|boolean   $numeric
	 * @return string
	 */
	protected function makeInClause( array $values, $numeric = null ) {

		// if numeric flag wasn't specified then detected it
		// by checking all items in the array are numeric
		if( $numeric === null ) {
			$numeric = count(array_filter($values, 'is_numeric')) == count($values);
		}

		// not numeric so we need to escape all the values
		if( !$numeric ) {
			$values = array_map([$this->db, 'quote'], $values);
		}
			
		return sprintf('(%s)', implode(', ', $values));

	}

	protected function getParameterName( $column, $operator ) {

		$suffixes = [
			'='    => 'eq',
			'!='   => 'neq',
			'<>'   => 'neq',
			'<'    => 'max',
			'<='   => 'max',
			'>'    => 'min',
			'>='   => 'min',
			'LIKE' => 'like',
			'NOT LIKE' => 'notlike',
		];

		$name = $column;

		// strip the table identifier
		if( $pos = strpos($name, '.') )
			$name = substr($name, $pos + 1);

		if( isset($suffixes[$operator]) )
			$name .= '_'. $suffixes[$operator];

		return $name;

	}

	/**
	 * Add a parameter and return the placeholder to be inserted into the query string.
	 * @param  string $name
	 * @param  mixed  $value
	 * @return string
	 */
	protected function bindParam( $name, $value ) {

		if( isset($this->params[$name]) )
			throw new \LogicException("Parameter: {$name} has already been defined");

		$this->params[$name] = $value;

		return ":{$name}";

	}

}

// EOF

1		<?php
2		/*
3		* This file is part of Yolk - Gamer Network's PHP Framework.
4		*
5		* Copyright (c) 2015 Gamer Network Ltd.
6		*
7		* Distributed under the MIT License, a copy of which is available in the
8		* LICENSE file that was bundled with this package, or online at:
9		* https://github.com/gamernetwork/yolk-database
10		*/
11
12		namespace yolk\database\query;
13
14		use yolk\contracts\database\DatabaseConnection;
15		use yolk\contracts\database\Query;
16
17		/**
18		* Generic.
19		*/
20		abstract class BaseQuery implements Query {
21
22		/**
23		* Database connection the query is associated with.
24		* @var DatabaseConnection
25		*/
26		protected $db;
27
28		/**
29		* Array of join clauses.
30		* @var array
31		*/
32		protected $joins;
33
34		/**
35		* Array of where clauses.
36		* @var array
37		*/
38		protected $where;
39
40		/**
41		* Array of order by clauses.
42		* @var array
43		*/
44		protected $order;
45
46		/**
47		* Query offset.
48		* @var integer
49		*/
50		protected $offset;
51
52		/**
53		* Query limit.
54		* @var integer\|null
55		*/
56		protected $limit;
57
58		/**
59		* Array of query parameters
60		* @var array
61		*/
62		protected $params;
63
64		public function __construct( DatabaseConnection $db ) {
65		$this->db = $db;
66		$this->joins = [];
67		$this->where = [];
68		$this->order = [];
69		$this->offset = 0;
70		$this->limit = null;
71		$this->params = [];
72		}
73
74		public function __toString() {
75		return implode("\n", $this->compile());
76		return d(implode("\n", $this->compile()), $this->params);
		0 ignored issues – show Unused Code introduced 2016-02-25 17:13 UTC by Report Bug Copy Issue Report `return d(implode(' ', $t...ile()), $this->params);` does not seem to be reachable. This check looks for unreachable code. It uses sophisticated control flow analysis techniques to find statements which will never be executed. Unreachable code is most often the result of `return`, `die` or `exit` statements that have been added for debug purposes. function fx() { try { doSomething(); return true; } catch (\Exception $e) { return false; } return false; } In the above example, the last `return false` will never be executed, because a return statement has already been met in every possible execution path. Loading history...
77		}
78
79		public function innerJoin( $table, array $on ) {
80		$this->joins[] = ['INNER', $table, $on];
81		return $this;
82		}
83
84		public function leftJoin( $table, array $on ) {
85		$this->joins[] = ['LEFT', $table, $on];
86		return $this;
87		}
88
89		public function joinRaw( $sql, $parameters = [] ) {
90		$this->joins[] = $sql;
91		$this->params = array_merge($this->params, $parameters);
92		return $this;
93		}
94
95		public function where( $column, $operator, $value = null ) {
96
97		// shortcut for equals
98		if( func_num_args() == 2 ) {
99		$value = $operator;
100		$operator = '=';
101		}
102
103		$operator = trim(strtoupper($operator));
104
105		// can't bind IN values as parameters so we escape them and embed them directly
106		if( in_array($operator, ['IN', 'NOT IN']) && is_array($value) ) {
107		$value = $this->makeInClause($value);
108		}
109		// do parameter binding
110		else {
111		$value = $this->bindParam(
112		$this->getParameterName($column, $operator),
113		$value
114		);
115		}
116
117		$this->where[] = [$this->quoteIdentifier($column), $operator, $value];
118
119		return $this;
120
121		}
122
123		public function whereArray( array $where ) {
124
125		foreach( $where as $k => $v ) {
126		$operator = is_array($v) ? 'IN' : '=';
127		$this->where($k, $operator, $v);
128		}
129
130		return $this;
131
132		}
133
134		public function whereRaw( $sql, $parameters = [] ) {
135		$this->where[] = $sql;
136		$this->params = array_merge($this->params, $parameters);
137		return $this;
138		}
139
140		public function orderBy( $column, $ascending = true ) {
141		$column = $this->quoteIdentifier($column);
142		$this->order[$column] = (bool) $ascending ? 'ASC' : 'DESC';
143		return $this;
144		}
145
146		public function offset( $offset ) {
147		$this->offset = max(0, (int) $offset);
148		return $this;
149		}
150
151		public function limit( $limit ) {
152		$this->limit = max(1, (int) $limit);
153		return $this;
154		}
155
156		public function getParameters() {
157		return $this->params;
158		}
159
160	View Code Duplication	public function setParameters( array $params, $replace = false ) {
		0 ignored issues – show Duplication introduced 2016-02-25 17:13 UTC by Report Bug Copy Issue Report This method seems to be duplicated in your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
161		if( $replace )
162		$this->params = $params;
163		else
164		$this->params = array_merge($this->params, $params);
165		return $this;
166		}
167
168		/**
169		* Generate a SQL string as an array.
170		* @return array
171		*/
172		abstract protected function compile();
173
174		protected function compileJoins() {
175
176		$sql = [];
177
178		foreach( $this->joins as $join ) {
179		if( is_array($join) ) {
180		list($type, $table, $on) = $join;
181		$join = sprintf("%s JOIN %s\nON %s", $type, $this->quoteIdentifier($table), $this->compileOn($on));
182		}
183		$sql[] = $join;
184		}
185
186		return $sql;
187
188		}
189
190		protected function compileOn( array $on ) {
191
192		$sql = [];
193
194		foreach( $on as $column => $value ) {
195		// if it's not a number or a quoted sring it much be an identifier, so quote it
196		if( !is_numeric($value) && !preg_match('/^\'.*\'$/', $value) )
197		$value = $this->quoteIdentifier($value);
198		$sql[] = sprintf("%s = %s", $this->quoteIdentifier($column), $value);
199		}
200
201		return implode("\nAND ", $sql);
202
203		}
204
205		protected function compileWhere() {
206
207		$sql = [];
208
209		foreach( $this->where as $i => $clause ) {
210		if( is_array($clause) )
211		$clause = implode(' ', $clause);
212		$sql[] = ($i ? 'AND ' : 'WHERE '). $clause;
213		}
214
215		return $sql;
216
217		}
218
219		protected function compileOrderBy() {
220
221		$sql = [];
222
223		if( $this->order ) {
		0 ignored issues – show Bug Best Practice introduced 2016-02-25 17:13 UTC by Report Bug Copy Issue Report The expression `$this->order` of type `array` is implicitly converted to a boolean; are you sure this is intended? If so, consider using `! empty($expr)` instead to make it clear that you intend to check for an array without elements. This check marks implicit conversions of arrays to boolean values in a comparison. While in PHP an empty array is considered to be equal (but not identical) to false, this is not always apparent. Consider making the comparison explicit by using `empty(..)` or `! empty(...)` instead. Loading history...
224		$order = 'ORDER BY ';
225		foreach( $this->order as $column => $dir ) {
226		$order .= $column. ' '. $dir. ', ';
227		}
228		$sql[] = trim($order, ', ');
229		}
230
231		return $sql;
232
233		}
234
235		protected function compileOffsetLimit() {
236
237		$sql = [];
238
239		$limit = $this->limit;
240		$offset = $this->offset;
241
242		if( $limit \|\| $offset ) {
		0 ignored issues – show Bug Best Practice introduced 2016-02-25 17:13 UTC by Report Bug Copy Issue Report The expression `$limit` of type `integer\|null` is loosely compared to `true`; this is ambiguous if the integer can be zero. You might want to explicitly use `!== null` instead. In PHP, under loose comparison (like `==`, or `!=`, or `switch` conditions), values of different types might be equal. For `integer` values, zero is a special case, in particular the following results might be unexpected: 0 == false // true 0 == null // true 123 == false // false 123 == null // false // It is often better to use strict comparison 0 === false // false 0 === null // false Loading history...
243
244		if( !$limit )
		0 ignored issues – show Bug Best Practice introduced 2016-02-25 17:13 UTC by Report Bug Copy Issue Report The expression `$limit` of type `integer\|null` is loosely compared to `false`; this is ambiguous if the integer can be zero. You might want to explicitly use `=== null` instead. In PHP, under loose comparison (like `==`, or `!=`, or `switch` conditions), values of different types might be equal. For `integer` values, zero is a special case, in particular the following results might be unexpected: 0 == false // true 0 == null // true 123 == false // false 123 == null // false // It is often better to use strict comparison 0 === false // false 0 === null // false Loading history...
245		$limit = PHP_INT_MAX;
246
247		$sql[] = sprintf(
248		"LIMIT %s OFFSET %s",
249		$this->bindParam('_limit', $limit),
250		$this->bindParam('_offset', $offset)
251		);
252
253		}
254
255		return $sql;
256
257		}
258
259		protected function quoteIdentifier( $spec ) {
260
261		// don't quote things that are functions/expressions
262		if( strpos($spec, '(') !== false )
263		return $spec;
264
265		foreach( [' AS ', ' ', '.'] as $sep) {
266		if( $pos = strripos($spec, $sep) ) {
267		return
268		$this->quoteIdentifier(substr($spec, 0, $pos)).
269		$sep.
270		$this->db->quoteIdentifier(substr($spec, $pos + strlen($sep)));
271		}
272		}
273
274		return $this->db->quoteIdentifier($spec);
275
276		}
277
278		/**
279		* Join an array of values to form a string suitable for use in a SQL IN clause.
280		* The numeric parameter determines whether values are escaped and quoted;
281		* a null value (the default) will cause the function to auto-detect whether
282		* values should be escaped and quoted.
283		*
284		* @param array $values
285		* @param null\|boolean $numeric
286		* @return string
287		*/
288		protected function makeInClause( array $values, $numeric = null ) {
289
290		// if numeric flag wasn't specified then detected it
291		// by checking all items in the array are numeric
292		if( $numeric === null ) {
293		$numeric = count(array_filter($values, 'is_numeric')) == count($values);
294		}
295
296		// not numeric so we need to escape all the values
297		if( !$numeric ) {
298		$values = array_map([$this->db, 'quote'], $values);
299		}
300
301		return sprintf('(%s)', implode(', ', $values));
302
303		}
304
305		protected function getParameterName( $column, $operator ) {
306
307		$suffixes = [
308		'=' => 'eq',
309		'!=' => 'neq',
310		'<>' => 'neq',
311		'<' => 'max',
312		'<=' => 'max',
313		'>' => 'min',
314		'>=' => 'min',
315		'LIKE' => 'like',
316		'NOT LIKE' => 'notlike',
317		];
318
319		$name = $column;
320
321		// strip the table identifier
322		if( $pos = strpos($name, '.') )
323		$name = substr($name, $pos + 1);
324
325		if( isset($suffixes[$operator]) )
326		$name .= '_'. $suffixes[$operator];
327
328		return $name;
329
330		}
331
332		/**
333		* Add a parameter and return the placeholder to be inserted into the query string.
334		* @param string $name
335		* @param mixed $value
336		* @return string
337		*/
338		protected function bindParam( $name, $value ) {
339
340		if( isset($this->params[$name]) )
341		throw new \LogicException("Parameter: {$name} has already been defined");
342
343		$this->params[$name] = $value;
344
345		return ":{$name}";
346
347		}
348
349		}
350
351		// EOF

gamernetwork / yolk-database

BaseQuery::compileJoins() A last analyzed 2016-05-18 08:44 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

BaseQuery::compileJoins() A
last analyzed 2016-05-18 08:44 UTC