SQLQueryBuilder - Code Metrics - Inspection of "Join support added" - soupmix/sql - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 065b63...856c58 )

by Mehmet

created 2016-07-13 14:19 UTC

SQLQueryBuilder B

↳ Parent: Project

Complexity

Total Complexity

Size/Duplication

Total Lines	238
Duplicated Lines	4.2 %

Coupling/Cohesion

Components	1
Dependencies	1

Test Coverage

Coverage

92.09%

Importance

Changes	12
Bugs	4	Features	2

Metric	Value
wmc	46
c	12
b	4
f	2
lcom	1
cbo	1
dl	10
loc	238
ccs	163
cts	177
cp	0.9209
rs	8.3999

15 Methods

Rating	Name	Duplication	Size	Complexity
A	getQueryBuilder()	0	8	2
A	getCount()	0	7	1
A	setSortOrders()	0	8	3
A	run()	0	21	4
A	setJoins()	0	7	1
B	setJoinsForType()	0	26	6
A	returnFieldsForJoin()	0	8	3
A	setReturnFields()	0	9	3
A	setOffsetAndLimit()	0	5	1
C	addAlias()	0	23	7
A	buildQuery()	0	9	2
A	buildQueryFilters()	0	11	4
A	buildQueryForAnd()	6	16	2
A	buildQueryForOr()	4	21	3
B	buildFilter()	0	39	4

How to fix Duplicated Code Complexity

Duplicated Code

Duplicate code is one of the most pungent code smells. A rule that is often used is to re-structure code once it is duplicated in three or more places.

Common duplication problems, and corresponding solutions are:

If you have the same expression in different places: Extract expression to a method
If you have the same method in different sub-classes: Extract method, and pull up field to the parent class
If you have the same code in unrelated classes: Consider extracting the code to a new class, and injecting that class

Complex Class

Tip: Before tackling complexity, make sure that you eliminate any duplication first. This often can reduce the size of classes significantly.

Complex classes like SQLQueryBuilder often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes. You can also have a look at the cohesion graph to spot any un-connected, or weakly-connected components.

Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.

While breaking up the class, it is a good idea to analyze how other classes use SQLQueryBuilder, and based on these observations, apply Extract Interface, too.

<?php

namespace Soupmix;


class SQLQueryBuilder extends AbstractQueryBuilder
{

    private $queryBuilder = null;

    public function run() {
        $this->queryBuilder = $this->getQueryBuilder();
        $this->setJoins();
        $count = $this->getCount();
        if (!isset($count[0]['total']) || ($count[0]['total']==0)) {
            return ['total' => 0, 'data' => null];
        }
        $numberOfRows = $count[0]['total'];
        $this->setSortOrders();
        $this->setOffsetAndLimit();
        $this->setReturnFields();
        $stmt = $this->conn->executeQuery(
            $this->queryBuilder->getSql(),
            $this->queryBuilder->getParameters()
        );
        $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        if ($this->distinctFieldName !== null) {
                $numberOfRows = count($result);
        }
        return ['total' => $numberOfRows, 'data' => $result];
    }

    private function getQueryBuilder()
    {
        if ($this->orFilters !== null) {
            $this->andFilters[] = $this->orFilters;
        }
        $this->filters      = $this->andFilters;
        return $this->buildQuery($this->collection, $this->filters);
    }

    private function getCount()
    {
        $queryBuilderCount = clone $this->queryBuilder;
        $queryBuilderCount->select(" COUNT(*) AS total ");
        $stmt = $this->conn->executeQuery($queryBuilderCount->getSql(), $queryBuilderCount->getParameters());
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    private function setSortOrders()
    {
        if ($this->sortFields !== null) {
            foreach ($this->addAlias($this->sortFields) as $sortKey => $sortDir) {
$collection = json_decode($data, true);
if ( ! is_array($collection)) {
    throw new \RuntimeException('$collection must be an array.');
}

foreach ($collection as $item) { /** ... */ }
                $this->queryBuilder->addOrderBy($sortKey, $sortDir);
            }
        }
    }

    private function setJoins()
    {
        $this->setJoinsForType('innerJoin');
        $this->setJoinsForType('leftJoin');
        $this->setJoinsForType('rightJoin');
        $this->setJoinsForType('outerJoin');
    }

    private function setJoinsForType($joinType)
    {
        if (is_null($this->{$joinType})) {
           return;
        }
        foreach ($this->{$joinType} as $collectionName => $collection) {
            $fieldNames = $this->addAlias($collection['returnFields'], $collectionName);
            $this->returnFieldsForJoin($fieldNames);
/**
 * @return array|string
 */
function returnsDifferentValues($x) {
    if ($x) {
        return 'foo';
    }

    return array();
}

$x = returnsDifferentValues($y);
if (is_array($x)) {
    // $x is an array.
}
            $joinCondition = '';
            foreach ($collection['relations'] as $relation) {
                $joinCondition .= ($joinCondition=='') ? '':' AND ';
                $relationType = array_keys($relation)[0];
                $source = array_keys($relation[$relationType])[0];
                $condition = $collectionName . "." . $source
                    . " = " . $this->collection . "." . $relation[$relationType][$source];
                if($relationType != 'field') {
                    $condition = $collectionName . "." . $source . " "
                        . $relationType . " " . $relation[$relationType][$source];
                }
                $joinCondition .= $condition;
            }
            $this->queryBuilder->$joinType($this->collection, $collectionName, $collectionName, $joinCondition);

        }
        return $this;
    }
    public function returnFieldsForJoin(array $fieldNames=null)
    {
        if($fieldNames !== null ) {
            foreach ($fieldNames as $fieldName) {
                $this->fieldNames[] = $fieldName;
            }
        }
    }

    private function setReturnFields()
    {
        if ($this->distinctFieldName === null) {
            $fieldNames = ($this->fieldNames === null) ? $this->addAlias("*") : $this->addAlias($this->fieldNames);
            $this->queryBuilder->select($fieldNames);
            return;
        }
        $this->queryBuilder->select('DISTINCT (`' . $this->collection . "`.`" . $this->distinctFieldName . '`)');
    }

    private function setOffsetAndLimit()
    {
        $this->queryBuilder->setFirstResult($this->offset)
            ->setMaxResults($this->limit);
    }

    private function addAlias($fields, $collection=null)
    {
        $collection = (!is_null($collection)) ? $collection : $this->collection;

        if (!is_array($fields)) {
            return  $collection . "." . $fields;
        }
        if (!is_array($fields)) {
           return  $collection . "." . $fields;
        }
        $newFields = [];
        foreach ($fields as $field => $value){
            if(is_int($field)){
                if (!is_array($fields)) {
                    $newFields[] = $collection . "." . $fields;
                    continue;
                }
                $newFields[] = $collection . "." . $value;
                continue;
            }
            $newFields[$collection.".".$field] = $value;
        }
        return $newFields;
    }

    protected function buildQuery($collection, $filters)
    {
        $queryBuilder = $this->conn->createQueryBuilder();
        $queryBuilder->from($collection, $collection);
        if ($filters === null) {
            return $queryBuilder;
        }
        return $this->buildQueryFilters($queryBuilder, $filters);
    }

    protected function buildQueryFilters($queryBuilder, $filters)
    {
        foreach ($filters as $key => $value) {
            if (strpos($key, '__') === false && is_array($value)) {
                $queryBuilder = $this->buildQueryForOr($queryBuilder, $value);

                continue;
            }
            $queryBuilder = $this->buildQueryForAnd($queryBuilder, $key, $value);

        }
        return $queryBuilder;
    }

    protected function buildQueryForAnd($queryBuilder, $key, $value)
    {
        $sqlOptions = self::buildFilter([$key => $value]);
        if (in_array($sqlOptions['method'], ['in', 'notIn'])) {

            $queryBuilder->andWhere(
                $queryBuilder->expr()->{$sqlOptions['method']}($this->collection . "." . $sqlOptions['key'], $sqlOptions['value'])
            );
            return $queryBuilder;
        }
        $queryBuilder->andWhere(
            '`' . $this->collection . "`.`" . $sqlOptions['key'].'`'
            . ' ' . $sqlOptions['operand']
            . ' ' . $queryBuilder->createNamedParameter($sqlOptions['value'])
        );
        return $queryBuilder;
    }
    protected function buildQueryForOr($queryBuilder, $value)
    {
        $orQuery =[];
        foreach ($value as $orValue) {
            $subKey = array_keys($orValue)[0];
            $subValue = $orValue[$subKey];
            $sqlOptions = self::buildFilter([$subKey => $subValue]);
            if (in_array($sqlOptions['method'], ['in', 'notIn'])) {

                $orQuery[] =  $queryBuilder->expr()->{$sqlOptions['method']}($this->collection . "." . $sqlOptions['key'], $sqlOptions['value']);
                continue;
            }
            $orQuery[] =
                '`' . $this->collection . "`.`" . $sqlOptions['key'].'`'
                . ' ' . $sqlOptions['operand']
                . ' ' . $queryBuilder->createNamedParameter($sqlOptions['value']);
        }
        $queryBuilder->andWhere(
            '(' . implode(' OR ', $orQuery) . ')'
        );
        return $queryBuilder;
    }


    public static function buildFilter($filter)
    {
        $key = array_keys($filter)[0];
        $value = $filter[$key];
        $operator = ' = ';
        $method = 'eq';
        $options =[
            'gte'       => ['method' => 'gte', 'operand' => ' >= '],
            'gt'        => ['method' => 'gt', 'operand' => ' > '],
            'lte'       => ['method' => 'lte', 'operand' => ' <= '],
            'lt'        => ['method' => 'lt', 'operand' => ' < '],
            'in'        => ['method' => 'in', 'operand' => ' IN '],
            '!in'       => ['method' => 'notIn', 'operand' => ' NOT IN '],
            'not'       => ['method' => 'not', 'operand' => ' NOT '],
            'wildcard'  => ['method' => 'like', 'operand' => ' LIKE '],
            'prefix'    => ['method' => 'like', 'operand' => ' LIKE '],
        ];
        if (strpos($key, '__') !== false) {
            preg_match('/__(.*?)$/i', $key, $matches);
            $key        = str_replace($matches[0], '', $key);
            $queryOperator   = $matches[1];
            $method     = $options[$queryOperator]['method'];
            $operator   = $options[$queryOperator]['operand'];
            switch ($queryOperator) {
                case 'wildcard':
                    $value = '%'.str_replace(array('?', '*'), array('_', '%'), $value).'%';
                    break;
                case 'prefix':
                    $value = $value.'%';
                    break;
            }
        }
        return [
            'key'       => $key,
            'operand'   => $operator,
            'method'    => $method,
            'value'     => $value
        ];
    }

}

1			<?php
2
3			namespace Soupmix;
4
5
6			class SQLQueryBuilder extends AbstractQueryBuilder
7			{
8
9			private $queryBuilder = null;
10
11	2		public function run() {
12	2		$this->queryBuilder = $this->getQueryBuilder();
13	2		$this->setJoins();
14	2		$count = $this->getCount();
15	2		if (!isset($count[0]['total']) \|\| ($count[0]['total']==0)) {
16			return ['total' => 0, 'data' => null];
17			}
18	2		$numberOfRows = $count[0]['total'];
19	2		$this->setSortOrders();
20	2		$this->setOffsetAndLimit();
21	2		$this->setReturnFields();
22	2		$stmt = $this->conn->executeQuery(
23	2		$this->queryBuilder->getSql(),
24	2		$this->queryBuilder->getParameters()
25	2		);
26	2		$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
27	2		if ($this->distinctFieldName !== null) {
28	1		$numberOfRows = count($result);
29	1		}
30	2		return ['total' => $numberOfRows, 'data' => $result];
31			}
32
33	2		private function getQueryBuilder()
34			{
35	2		if ($this->orFilters !== null) {
36	2		$this->andFilters[] = $this->orFilters;
37	2		}
38	2		$this->filters = $this->andFilters;
39	2		return $this->buildQuery($this->collection, $this->filters);
40			}
41
42	2		private function getCount()
43			{
44	2		$queryBuilderCount = clone $this->queryBuilder;
45	2		$queryBuilderCount->select(" COUNT(*) AS total ");
46	2		$stmt = $this->conn->executeQuery($queryBuilderCount->getSql(), $queryBuilderCount->getParameters());
47	2		return $stmt->fetchAll(\PDO::FETCH_ASSOC);
48			}
49
50	2		private function setSortOrders()
51			{
52	2		if ($this->sortFields !== null) {
53			foreach ($this->addAlias($this->sortFields) as $sortKey => $sortDir) {
			0 ignored issues – show Bug introduced 2016-07-13 14:23 UTC by Report Bug Copy Issue Report The expression `$this->addAlias($this->sortFields)` of type `string\|array` is not guaranteed to be traversable. How about adding an additional type check? There are different options of fixing this problem. If you want to be on the safe side, you can add an additional type-check: $collection = json_decode($data, true); if ( ! is_array($collection)) { throw new \RuntimeException('$collection must be an array.'); } foreach ($collection as $item) { /** ... / } If you are sure that the expression is traversable, you might want to add a doc comment cast* to improve IDE auto-completion and static analysis: /** @var array $collection / $collection = json_decode($data, true); foreach ($collection as $item) { /* .. / } Mark the issue as a false-positive*: Just hover the remove button, in the top-right corner of this issue for more options. Loading history...
54			$this->queryBuilder->addOrderBy($sortKey, $sortDir);
55			}
56			}
57	2		}
58
59	2		private function setJoins()
60			{
61	2		$this->setJoinsForType('innerJoin');
62	2		$this->setJoinsForType('leftJoin');
63	2		$this->setJoinsForType('rightJoin');
64	2		$this->setJoinsForType('outerJoin');
65	2		}
66
67	2		private function setJoinsForType($joinType)
68			{
69	2		if (is_null($this->{$joinType})) {
70	2		return;
71			}
72	1		foreach ($this->{$joinType} as $collectionName => $collection) {
73	1		$fieldNames = $this->addAlias($collection['returnFields'], $collectionName);
74	1		$this->returnFieldsForJoin($fieldNames);
			0 ignored issues – show Bug introduced 2016-07-13 14:23 UTC by Report Bug Copy Issue Report It seems like `$fieldNames` defined by `$this->addAlias($collect...lds'], $collectionName)` on line `73` can also be of type `string`; however, `Soupmix\SQLQueryBuilder::returnFieldsForJoin()` does only seem to accept `null\|array`, maybe add an additional type check? If a method or function can return multiple different values and unless you are sure that you only can receive a single value in this context, we recommend to add an additional type check: /** * @return array\|string */ function returnsDifferentValues($x) { if ($x) { return 'foo'; } return array(); } $x = returnsDifferentValues($y); if (is_array($x)) { // $x is an array. } If this a common case that PHP Analyzer should handle natively, please let us know by opening an issue. Loading history...
75	1		$joinCondition = '';
76	1		foreach ($collection['relations'] as $relation) {
77	1		$joinCondition .= ($joinCondition=='') ? '':' AND ';
78	1		$relationType = array_keys($relation)[0];
79	1		$source = array_keys($relation[$relationType])[0];
80	1		$condition = $collectionName . "." . $source
81	1		. " = " . $this->collection . "." . $relation[$relationType][$source];
82	1		if($relationType != 'field') {
83	1		$condition = $collectionName . "." . $source . " "
84	1		. $relationType . " " . $relation[$relationType][$source];
85	1		}
86	1		$joinCondition .= $condition;
87	1		}
88	1		$this->queryBuilder->$joinType($this->collection, $collectionName, $collectionName, $joinCondition);
89
90	1		}
91	1		return $this;
92			}
93	1		public function returnFieldsForJoin(array $fieldNames=null)
94			{
95	1		if($fieldNames !== null ) {
96	1		foreach ($fieldNames as $fieldName) {
97	1		$this->fieldNames[] = $fieldName;
98	1		}
99	1		}
100	1		}
101
102	2		private function setReturnFields()
103			{
104	2		if ($this->distinctFieldName === null) {
105	2		$fieldNames = ($this->fieldNames === null) ? $this->addAlias("*") : $this->addAlias($this->fieldNames);
106	2		$this->queryBuilder->select($fieldNames);
107	2		return;
108			}
109	1		$this->queryBuilder->select('DISTINCT (`' . $this->collection . "`.`" . $this->distinctFieldName . '`)');
110	1		}
111
112	2		private function setOffsetAndLimit()
113			{
114	2		$this->queryBuilder->setFirstResult($this->offset)
115	2		->setMaxResults($this->limit);
116	2		}
117
118	2		private function addAlias($fields, $collection=null)
119			{
120	2		$collection = (!is_null($collection)) ? $collection : $this->collection;
			0 ignored issues – show Coding Style introduced 2016-07-13 14:23 UTC by Report Bug Copy Issue Report Consider using a different name than the parameter `$collection`. This often makes code more readable. Loading history...
121	2		if (!is_array($fields)) {
122	1		return $collection . "." . $fields;
123			}
124	1		if (!is_array($fields)) {
125			return $collection . "." . $fields;
126			}
127	1		$newFields = [];
128	1		foreach ($fields as $field => $value){
129	1		if(is_int($field)){
130	1		if (!is_array($fields)) {
131			$newFields[] = $collection . "." . $fields;
132			continue;
133			}
134	1		$newFields[] = $collection . "." . $value;
135	1		continue;
136			}
137			$newFields[$collection.".".$field] = $value;
138	1		}
139	1		return $newFields;
140			}
141
142	2		protected function buildQuery($collection, $filters)
143			{
144	2		$queryBuilder = $this->conn->createQueryBuilder();
145	2		$queryBuilder->from($collection, $collection);
146	2		if ($filters === null) {
147			return $queryBuilder;
148			}
149	2		return $this->buildQueryFilters($queryBuilder, $filters);
150			}
151
152	2		protected function buildQueryFilters($queryBuilder, $filters)
153			{
154	2		foreach ($filters as $key => $value) {
155	2		if (strpos($key, '__') === false && is_array($value)) {
156	2		$queryBuilder = $this->buildQueryForOr($queryBuilder, $value);
			0 ignored issues – show Coding Style introduced 2016-07-13 14:23 UTC by Report Bug Copy Issue Report Consider using a different name than the parameter `$queryBuilder`. This often makes code more readable. Loading history...
157	2		continue;
158			}
159	2		$queryBuilder = $this->buildQueryForAnd($queryBuilder, $key, $value);
			0 ignored issues – show Coding Style introduced 2016-07-13 14:23 UTC by Report Bug Copy Issue Report Consider using a different name than the parameter `$queryBuilder`. This often makes code more readable. Loading history...
160	2		}
161	2		return $queryBuilder;
162			}
163
164	2		protected function buildQueryForAnd($queryBuilder, $key, $value)
165			{
166	2		$sqlOptions = self::buildFilter([$key => $value]);
167	2	View Code Duplication	if (in_array($sqlOptions['method'], ['in', 'notIn'])) {
			0 ignored issues – show Duplication introduced 2016-06-27 07:38 UTC by Report Bug Copy Issue Report This code seems to be duplicated across 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...
168			$queryBuilder->andWhere(
169			$queryBuilder->expr()->{$sqlOptions['method']}($this->collection . "." . $sqlOptions['key'], $sqlOptions['value'])
170			);
171			return $queryBuilder;
172			}
173	2		$queryBuilder->andWhere(
174	2		'`' . $this->collection . "`.`" . $sqlOptions['key'].'`'
175	2		. ' ' . $sqlOptions['operand']
176	2		. ' ' . $queryBuilder->createNamedParameter($sqlOptions['value'])
177	2		);
178	2		return $queryBuilder;
179			}
180	2		protected function buildQueryForOr($queryBuilder, $value)
181			{
182	2		$orQuery =[];
183	2		foreach ($value as $orValue) {
184	2		$subKey = array_keys($orValue)[0];
185	2		$subValue = $orValue[$subKey];
186	2		$sqlOptions = self::buildFilter([$subKey => $subValue]);
187	2	View Code Duplication	if (in_array($sqlOptions['method'], ['in', 'notIn'])) {
			0 ignored issues – show Duplication introduced 2016-06-27 07:38 UTC by Report Bug Copy Issue Report This code seems to be duplicated across 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...
188	1		$orQuery[] = $queryBuilder->expr()->{$sqlOptions['method']}($this->collection . "." . $sqlOptions['key'], $sqlOptions['value']);
189	1		continue;
190			}
191	2		$orQuery[] =
192	2		'`' . $this->collection . "`.`" . $sqlOptions['key'].'`'
193	2		. ' ' . $sqlOptions['operand']
194	2		. ' ' . $queryBuilder->createNamedParameter($sqlOptions['value']);
195	2		}
196	2		$queryBuilder->andWhere(
197	2		'(' . implode(' OR ', $orQuery) . ')'
198	2		);
199	2		return $queryBuilder;
200			}
201
202
203	2		public static function buildFilter($filter)
204			{
205	2		$key = array_keys($filter)[0];
206	2		$value = $filter[$key];
207	2		$operator = ' = ';
208	2		$method = 'eq';
209			$options =[
210	2		'gte' => ['method' => 'gte', 'operand' => ' >= '],
211	2		'gt' => ['method' => 'gt', 'operand' => ' > '],
212	2		'lte' => ['method' => 'lte', 'operand' => ' <= '],
213	2		'lt' => ['method' => 'lt', 'operand' => ' < '],
214	2		'in' => ['method' => 'in', 'operand' => ' IN '],
215	2		'!in' => ['method' => 'notIn', 'operand' => ' NOT IN '],
216	2		'not' => ['method' => 'not', 'operand' => ' NOT '],
217	2		'wildcard' => ['method' => 'like', 'operand' => ' LIKE '],
218	2		'prefix' => ['method' => 'like', 'operand' => ' LIKE '],
219	2		];
220	2		if (strpos($key, '__') !== false) {
221	2		preg_match('/__(.*?)$/i', $key, $matches);
222	2		$key = str_replace($matches[0], '', $key);
223	2		$queryOperator = $matches[1];
224	2		$method = $options[$queryOperator]['method'];
225	2		$operator = $options[$queryOperator]['operand'];
226			switch ($queryOperator) {
227	2		case 'wildcard':
228	1		$value = '%'.str_replace(array('?', '*'), array('_', '%'), $value).'%';
229	1		break;
230	2		case 'prefix':
231	1		$value = $value.'%';
232	1		break;
233			}
234	2		}
235			return [
236	2		'key' => $key,
237	2		'operand' => $operator,
238	2		'method' => $method,
239			'value' => $value
240	2		];
241			}
242
243			}

soupmix / sql