WhereStatement::addExistsCondition() - Code Metrics - Inspection of "exists where conditions (resolves #15)" - jaredtking/jaqb - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 07eb26...46e6ae )

by Jared

created 2016-04-23 19:12 UTC

WhereStatement::addExistsCondition() A

↳ Parent: WhereStatement

Complexity

Conditions	1
Paths	1

Size

Total Lines	6
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

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

<?php

/**
 * @author Jared King <[email protected]>
 *
 * @link http://jaredtking.com
 *
 * @copyright 2015 Jared King
 * @license MIT
 */
namespace JAQB\Statement;

use JAQB\Query\SelectQuery;

class WhereStatement extends Statement
{
    /**
     * @var bool
     */
    protected $having;

    /**
     * @var array
     */
    protected $conditions = [];

    /**
     * @param bool $having when true, statement becomes a having statement
     */
    public function __construct($having = false)
    {
        $this->having = $having;
    }

    /**
     * Tells whether this statement is a HAVING statement.
     *
     * @return bool true: is HAVING, false: is WHERE
     */
    public function isHaving()
    {
        return $this->having;
    }

    /**
     * Adds a condition to the statement.
     *
     * Accepts the following forms:
     * 1. Equality comparison:
     *      addCondition('username', 'john')
     * 2. Comparison with custom operator:
     *      addCondition('balance', 100, '>')
     * 3. IN statement:
     *      addCondition('group', ['admin', 'owner'])
     * 4. SQL fragment:
     *      addCondition('name LIKE "%john%"')
     * 5. List of conditions to add:
     *      addCondition([['balance', 100, '>'],
     *                    ['user_id', 5]])
     * 6. Map of equality comparisons:
     *      addCondition(['username' => 'john',
     *                    'user_id' => 5])
     * 7. List of SQL fragments:
     *      addCondition(['first_name LIKE "%john%"',
     *                    'last_name LIKE "%doe%"'])
     *
     * @param array|string $field
     * @param string|bool  $value    condition value (optional)
     * @param string       $operator operator (optional)
     *
     * @return self
     */
    public function addCondition($field, $value = false, $operator = '=')
    {
        if (is_array($field) && !$value) {
            foreach ($field as $key => $value) {
                // handles #5
                if (is_array($value)) {
                    call_user_func_array([$this, 'addCondition'], $value);
                // handles #6
                } elseif (!is_numeric($key)) {
                    $this->addCondition($key, $value);
                // handles #7
                } else {
                    $this->addCondition($value);
                }
            }

            return $this;
        }

        // handles #4
        $condition = [$field];

        if (func_num_args() >= 2) {
            // handles #3
            if (is_array($value) && $operator === '=') {
                $operator = 'IN';
            } elseif (is_array($value) && $operator === '<>') {
                $operator = 'NOT IN';
            }

            // handles #1 and #2
            $condition[] = $operator;
            $condition[] = $value;
        }

        $this->conditions[] = $condition;

        return $this;
    }

    public function addConditionOr($field, $value = false, $operator = '=')

    {
        $this->conditions[] = ['OR'];

        return call_user_func_array([$this, 'addCondition'], func_get_args());
    }

    /**
     * Adds a between condition to the query.
     *
     * @param string $field
     * @param mixed  $a     first between value
     * @param mixed  $b     second between value
     *
     * @return self
     */
    public function addBetweenCondition($field, $a, $b)
    {
        $this->conditions[] = [$field, 'BETWEEN', $a, $b];

        return $this;
    }

    /**
     * Adds a not between condition to the query.
     *
     * @param string $field
     * @param mixed  $a     first between value
     * @param mixed  $b     second between value
     *
     * @return self
     */
    public function addNotBetweenCondition($field, $a, $b)
    {
        $this->conditions[] = [$field, 'NOT BETWEEN', $a, $b];

        return $this;
    }

    /**
     * Adds an exists condition to the query.
     *
     * @param callable $f
     *
     * @return self
     */
    public function addExistsCondition(callable $f)
    {
        $this->conditions[] = ['EXISTS', $f];

        return $this;
    }

    /**
     * Adds a not exists condition to the query.
     *
     * @param callable $f
     *
     * @return self
     */
    public function addNotExistsCondition(callable $f)
    {
        $this->conditions[] = ['NOT EXISTS', $f];

        return $this;
    }

    /**
     * Gets the conditions for this statement.
     *
     * @return array
     */
    public function getConditions()
    {
        return $this->conditions;
    }

    public function build()
    {
        // reset the parameterized values
        $this->values = [];

        // build clause from conditions
        $clauses = [];
        foreach ($this->conditions as $condition) {
            $clauses[] = $this->buildClause($condition);
        }

        // remove empty values
        $clauses = array_filter($clauses);

        if (count($clauses) == 0) {
            return '';
        }

        $sql = (!$this->having) ? 'WHERE ' : 'HAVING ';

        return $sql.$this->implodeClauses($clauses);
    }

    /**
     * Builds a parameterized and escaped SQL fragment
     * for a condition that uses our own internal
     * representation.
     *
     * A condition is represented by an array, and can be
     * have one of the following forms:
     * i)   ['SQL fragment']
     * ii)  ['identifier', '=', 'value']
     * iii) ['identifier', 'BETWEEN', 'value', 'value']
     * iv)  ['EXISTS', function(SelectQuery $query) {}]
     *
     * @param array $cond
     *
     * @return string generated SQL fragment
     */
    protected function buildClause(array $cond)
    {
        // handle SQL fragments
        if (count($cond) == 1) {
            return $cond[0];
        }

        // handle EXISTS conditions
        if (in_array($cond[0], ['EXISTS', 'NOT EXISTS'])) {
            $f = $cond[1];
            $query = new SelectQuery();
            $f($query);
            $sql = $query->build();
            $this->values = array_merge($this->values, $query->getValues());

            return $cond[0].' ('.$sql.')';
        }

        // escape the identifier
        $cond[0] = $this->escapeIdentifier($cond[0]);
        if (empty($cond[0])) {
            return '';
        }

        // handle BETWEEN conditions
        if ($cond[1] === 'BETWEEN') {
            return $cond[0].' BETWEEN '.$this->parameterize($cond[2]).' AND '.$this->parameterize($cond[3]);
        } elseif ($cond[1] === 'NOT BETWEEN') {
            return $cond[0].' NOT BETWEEN '.$this->parameterize($cond[2]).' AND '.$this->parameterize($cond[3]);
        }

        // handle NULL values
        if ($cond[1] === '=' && $cond[2] === null) {
            return $cond[0].' IS NULL';
        } elseif ($cond[1] === '<>' && $cond[2] === null) {
            return $cond[0].' IS NOT NULL';
        }

        // handle array values, i.e. for IN conditions
        if (is_array($cond[2])) {
            foreach ($cond[2] as &$value) {
                $value = $this->parameterize($value);
            }
            $cond[2] = '('.implode(',', $cond[2]).')';

        // otherwise parameterize the value
        } else {
            $cond[2] = $this->parameterize($cond[2]);
        }

        return implode(' ', $cond);
    }

    protected function implodeClauses(array $clauses)
    {
        $str = false;
        $or = false;
        foreach ($clauses as $clause) {
            // an 'OR' token will change the operator used
            // when concatenating the next clause
            if ($clause == 'OR') {
                $or = true;
                continue;
            }

            if (!$str) { // first clause needs no operator
                $str = $clause;
            } elseif ($or) {
                $str .= " OR $clause";
            } else {
                $str .= " AND $clause";
            }

            $or = false;
        }

        return $str;
    }
}


1			<?php
2
3			/**
4			* @author Jared King <[email protected]>
5			*
6			* @link http://jaredtking.com
7			*
8			* @copyright 2015 Jared King
9			* @license MIT
10			*/
11			namespace JAQB\Statement;
12
13			use JAQB\Query\SelectQuery;
14
15			class WhereStatement extends Statement
16			{
17			/**
18			* @var bool
19			*/
20			protected $having;
21
22			/**
23			* @var array
24			*/
25			protected $conditions = [];
26
27			/**
28			* @param bool $having when true, statement becomes a having statement
29			*/
30			public function __construct($having = false)
31			{
32			$this->having = $having;
33			}
34
35			/**
36			* Tells whether this statement is a HAVING statement.
37			*
38			* @return bool true: is HAVING, false: is WHERE
39			*/
40			public function isHaving()
41			{
42			return $this->having;
43			}
44
45			/**
46			* Adds a condition to the statement.
47			*
48			* Accepts the following forms:
49			* 1. Equality comparison:
50			* addCondition('username', 'john')
51			* 2. Comparison with custom operator:
52			* addCondition('balance', 100, '>')
53			* 3. IN statement:
54			* addCondition('group', ['admin', 'owner'])
55			* 4. SQL fragment:
56			* addCondition('name LIKE "%john%"')
57			* 5. List of conditions to add:
58			* addCondition([['balance', 100, '>'],
59			* ['user_id', 5]])
60			* 6. Map of equality comparisons:
61			* addCondition(['username' => 'john',
62			* 'user_id' => 5])
63			* 7. List of SQL fragments:
64			* addCondition(['first_name LIKE "%john%"',
65			* 'last_name LIKE "%doe%"'])
66			*
67			* @param array\|string $field
68			* @param string\|bool $value condition value (optional)
69			* @param string $operator operator (optional)
70			*
71			* @return self
72			*/
73			public function addCondition($field, $value = false, $operator = '=')
74			{
75			if (is_array($field) && !$value) {
76			foreach ($field as $key => $value) {
77			// handles #5
78			if (is_array($value)) {
79			call_user_func_array([$this, 'addCondition'], $value);
80			// handles #6
81			} elseif (!is_numeric($key)) {
82			$this->addCondition($key, $value);
83			// handles #7
84			} else {
85			$this->addCondition($value);
86			}
87			}
88
89			return $this;
90			}
91
92			// handles #4
93			$condition = [$field];
94
95			if (func_num_args() >= 2) {
96			// handles #3
97			if (is_array($value) && $operator === '=') {
98			$operator = 'IN';
99			} elseif (is_array($value) && $operator === '<>') {
100			$operator = 'NOT IN';
101			}
102
103			// handles #1 and #2
104			$condition[] = $operator;
105			$condition[] = $value;
106			}
107
108			$this->conditions[] = $condition;
109
110			return $this;
111			}
112
113			public function addConditionOr($field, $value = false, $operator = '=')
			0 ignored issues – show Unused Code introduced 2016-04-20 03:58 UTC by Report Bug Copy Issue Report The parameter `$field` is not used and could be removed. This check looks from parameters that have been defined for a function or method, but which are not used in the method body. Loading history... Unused Code introduced 2016-04-20 03:58 UTC by Report Bug Copy Issue Report The parameter `$value` is not used and could be removed. This check looks from parameters that have been defined for a function or method, but which are not used in the method body. Loading history... Unused Code introduced 2016-04-20 03:58 UTC by Report Bug Copy Issue Report The parameter `$operator` is not used and could be removed. This check looks from parameters that have been defined for a function or method, but which are not used in the method body. Loading history...
114			{
115			$this->conditions[] = ['OR'];
116
117			return call_user_func_array([$this, 'addCondition'], func_get_args());
118			}
119
120			/**
121			* Adds a between condition to the query.
122			*
123			* @param string $field
124			* @param mixed $a first between value
125			* @param mixed $b second between value
126			*
127			* @return self
128			*/
129			public function addBetweenCondition($field, $a, $b)
130			{
131			$this->conditions[] = [$field, 'BETWEEN', $a, $b];
132
133			return $this;
134			}
135
136			/**
137			* Adds a not between condition to the query.
138			*
139			* @param string $field
140			* @param mixed $a first between value
141			* @param mixed $b second between value
142			*
143			* @return self
144			*/
145			public function addNotBetweenCondition($field, $a, $b)
146			{
147			$this->conditions[] = [$field, 'NOT BETWEEN', $a, $b];
148
149			return $this;
150			}
151
152			/**
153			* Adds an exists condition to the query.
154			*
155			* @param callable $f
156			*
157			* @return self
158			*/
159			public function addExistsCondition(callable $f)
160			{
161			$this->conditions[] = ['EXISTS', $f];
162
163			return $this;
164			}
165
166			/**
167			* Adds a not exists condition to the query.
168			*
169			* @param callable $f
170			*
171			* @return self
172			*/
173			public function addNotExistsCondition(callable $f)
174			{
175			$this->conditions[] = ['NOT EXISTS', $f];
176
177			return $this;
178			}
179
180			/**
181			* Gets the conditions for this statement.
182			*
183			* @return array
184			*/
185			public function getConditions()
186			{
187			return $this->conditions;
188			}
189
190			public function build()
191			{
192			// reset the parameterized values
193			$this->values = [];
194
195			// build clause from conditions
196			$clauses = [];
197			foreach ($this->conditions as $condition) {
198			$clauses[] = $this->buildClause($condition);
199			}
200
201			// remove empty values
202			$clauses = array_filter($clauses);
203
204			if (count($clauses) == 0) {
205			return '';
206			}
207
208			$sql = (!$this->having) ? 'WHERE ' : 'HAVING ';
209
210			return $sql.$this->implodeClauses($clauses);
211			}
212
213			/**
214			* Builds a parameterized and escaped SQL fragment
215			* for a condition that uses our own internal
216			* representation.
217			*
218			* A condition is represented by an array, and can be
219			* have one of the following forms:
220			* i) ['SQL fragment']
221			* ii) ['identifier', '=', 'value']
222			* iii) ['identifier', 'BETWEEN', 'value', 'value']
223			* iv) ['EXISTS', function(SelectQuery $query) {}]
224			*
225			* @param array $cond
226			*
227			* @return string generated SQL fragment
228			*/
229			protected function buildClause(array $cond)
230			{
231			// handle SQL fragments
232			if (count($cond) == 1) {
233			return $cond[0];
234			}
235
236			// handle EXISTS conditions
237			if (in_array($cond[0], ['EXISTS', 'NOT EXISTS'])) {
238			$f = $cond[1];
239			$query = new SelectQuery();
240			$f($query);
241			$sql = $query->build();
242			$this->values = array_merge($this->values, $query->getValues());
243
244			return $cond[0].' ('.$sql.')';
245			}
246
247			// escape the identifier
248			$cond[0] = $this->escapeIdentifier($cond[0]);
249			if (empty($cond[0])) {
250			return '';
251			}
252
253			// handle BETWEEN conditions
254			if ($cond[1] === 'BETWEEN') {
255			return $cond[0].' BETWEEN '.$this->parameterize($cond[2]).' AND '.$this->parameterize($cond[3]);
256			} elseif ($cond[1] === 'NOT BETWEEN') {
257			return $cond[0].' NOT BETWEEN '.$this->parameterize($cond[2]).' AND '.$this->parameterize($cond[3]);
258			}
259
260			// handle NULL values
261			if ($cond[1] === '=' && $cond[2] === null) {
262			return $cond[0].' IS NULL';
263			} elseif ($cond[1] === '<>' && $cond[2] === null) {
264			return $cond[0].' IS NOT NULL';
265			}
266
267			// handle array values, i.e. for IN conditions
268			if (is_array($cond[2])) {
269			foreach ($cond[2] as &$value) {
270			$value = $this->parameterize($value);
271			}
272			$cond[2] = '('.implode(',', $cond[2]).')';
273
274			// otherwise parameterize the value
275			} else {
276			$cond[2] = $this->parameterize($cond[2]);
277			}
278
279			return implode(' ', $cond);
280			}
281
282			protected function implodeClauses(array $clauses)
283			{
284			$str = false;
285			$or = false;
286			foreach ($clauses as $clause) {
287			// an 'OR' token will change the operator used
288			// when concatenating the next clause
289			if ($clause == 'OR') {
290			$or = true;
291			continue;
292			}
293
294			if (!$str) { // first clause needs no operator
295			$str = $clause;
296			} elseif ($or) {
297			$str .= " OR $clause";
298			} else {
299			$str .= " AND $clause";
300			}
301
302			$or = false;
303			}
304
305			return $str;
306			}
307			}
308

jaredtking / jaqb

Push — master ( 07eb26...46e6ae )

WhereStatement::addExistsCondition() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like