AbstractSqlRepository::buildQueryFromRules() - Code Metrics - Inspection of "Allow for fulltext search" - acredula/percy - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#30)

by Phil

created 2016-03-09 13:28 UTC

AbstractSqlRepository::buildQueryFromRules() D

↳ Parent: AbstractSqlRepository

Complexity

Conditions	9
Paths	20

Size

Total Lines	30
Code Lines	18

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	15
CRAP Score	10.8889

Importance

Changes	9
Bugs	1	Features	1

Metric	Value
c	9
b	1
f	1
dl	0
loc	30
ccs	15
cts	21
cp	0.7143
rs	4.909
cc	9
eloc	18
nc	20
nop	2
crap	10.8889

<?php

namespace Percy\Repository;

use Aura\Sql\ExtendedPdoInterface;
use InvalidArgumentException;
use Percy\Decorator\DecoratorTrait;
use Percy\Entity\Collection;
use Percy\Entity\CollectionBuilderTrait;
use Percy\Entity\EntityInterface;
use Percy\Http\QueryStringParserTrait;
use Percy\Store\StoreInterface;
use Psr\Http\Message\ServerRequestInterface;
use RuntimeException;

abstract class AbstractSqlRepository implements RepositoryInterface
{
    use CollectionBuilderTrait;
    use DecoratorTrait;
    use QueryStringParserTrait;

    /**
     * @var \Aura\Sql\ExtendedPdoInterface
     */
    protected $dbal;

    /**
     *
     * @var mixed
     */
    protected $relationships = [];

    /**
     * Construct.
     *
     * @param \Aura\Sql\ExtendedPdoInterface $dbal
     */
    public function __construct(ExtendedPdoInterface $dbal)
    {
        $this->dbal = $dbal;
    }

    /**
     * {@inheritdoc}
     */
    public function countFromRequest(ServerRequestInterface $request)
    {
        $rules = $this->parseQueryString($request->getUri()->getQuery());
        list($query, $params) = $this->buildQueryFromRules($rules, true);

        return (int) $this->dbal->fetchOne($query, $params)['total'];
    }

    /**
     * {@inheritdoc}
     */
    public function getFromRequest(ServerRequestInterface $request)
    {
        $rules = $this->parseQueryString($request->getUri()->getQuery());

        list($query, $params) = $this->buildQueryFromRules($rules);

        if (array_key_exists('sort', $rules) && ! array_key_exists('search', $rules)) {
            $query .= $this->buildSortPart($rules['sort'], $this->getTable());
        }

        if (array_key_exists('search', $rules)) {
            $query .= sprintf(' ORDER BY MATCH (%s) AGAINST (:match_bind) > :score_bind', $rules['search']['fields']);
        }

        if (array_key_exists('limit', $rules)) {
            $query .= ' LIMIT ';
            $query .= (array_key_exists('offset', $rules)) ? sprintf('%d,', $rules['offset']) : '';
            $query .= $rules['limit'];
        }

        $query = trim(preg_replace('!\s+!', ' ', $query));

        $collection = $this->buildCollection($this->dbal->fetchAll($query, $params))
                           ->setTotal($this->countFromRequest($request));

        $this->decorate($collection, StoreInterface::ON_READ);

        return $collection;
    }

    /**
     * Build the sort part of the query.
     *
     * @param array|string $sorts
     * @param string $table
     *
     * @return string
     */
    protected function buildSortPart($sorts, $table)
    {
        if (is_string($sorts) && $sorts === 'RAND()') {
            return ' ORDER BY RAND()';
        }

        $fields = [];

        foreach ($sorts as $sort) {
$collection = json_decode($data, true);
if ( ! is_array($collection)) {
    throw new \RuntimeException('$collection must be an array.');
}

foreach ($collection as $item) { /** ... */ }
            if (substr($sort['field'], 0, strlen($table)) !== $table) {
                continue;
            }

            $fields[] = sprintf('%s %s', $sort['field'], strtoupper($sort['direction']));
        }

        return (empty($fields)) ? '' : sprintf(' ORDER BY %s', implode(', ', $fields));
    }

    /**
     * Build a base query without sorting and limits from filter rules.
     *
     * @param array   $rules
     * @param boolean $count
     *
     * @return array
     */
    protected function buildQueryFromRules(array $rules, $count = false)
    {
        $start = ($count === false) ? 'SELECT * FROM ' : 'SELECT *, COUNT(*) as total FROM ';

        $query = $start . $this->getTable();

        $params = [];

        if (array_key_exists('filter', $rules)) {
            foreach ($rules['filter'] as $key => $where) {
                $keyword   = ($key === 0) ? ' WHERE' : ' AND';
                $delimiter = strtoupper($where['delimiter']);
                $binding   = (in_array($delimiter, ['IN', 'NOT IN'])) ? sprintf('(:%s)', $where['binding']) : ':' . $where['binding'];
                $query    .= sprintf('%s %s %s %s', $keyword, $where['field'], $delimiter, $binding);

                $params[$where['binding']] = $where['value'];
            }
        }

        if (array_key_exists('search', $rules)) {
            $keyword = (array_key_exists('filter', $rules)) ? ' AND' : ' WHERE';
            $query  .= sprintf('%s MATCH (%s) AGAINST (:match_bind IN BOOLEAN MODE)', $keyword, $rules['search']['columns']);
            $query  .= sprintf(' HAVING MATCH (%s) AGAINST (:match_bind) > :score_bind', $rules['search']['columns']);

            $params['match_bind'] = $rules['search']['term'];
            $params['score_bind'] = (array_key_exists('minscore', $rules)) ? $rules['minscore'] : 0;
        }

        return [$query, $params];
    }

    /**
     * {@inheritdoc}
     */
    public function countByField($field, $value, ServerRequestInterface $request = null)
    {
        $query = sprintf(
            "SELECT COUNT(*) as total FROM %s WHERE %s.%s IN (:%s)",
            $this->getTable(),
            $this->getTable(),
            $field,
            $field
        );

        $params = [
            $field => implode(',', (array) $value)
        ];

        return (int) $this->dbal->fetchOne($query, $params)['total'];
    }

    /**
     * {@inheritdoc}
     */
    public function getByField($field, $value, ServerRequestInterface $request = null)
    {
        $query = sprintf(
            'SELECT * FROM %s WHERE %s.%s IN (:%s)',
            $this->getTable(),
            $this->getTable(),
            $field,
            $field
        );

        // @todo - allow extra filtering from request

        $params = [
            $field => implode(',', (array) $value)
        ];

        $collection = $this->buildCollection($this->dbal->fetchAll($query, $params))
                           ->setTotal($this->countByField($field, $value));

        $this->decorate($collection, StoreInterface::ON_READ);

        return $collection;
    }

    /**
     * {@inheritdoc}
     */
    public function attachRelationships(
        Collection $collection,
        $include                        = null,
        ServerRequestInterface $request = null
    ) {
        if (is_null($include)) {
            return;
        }

        $rules = $this->parseQueryString($request->getUri()->getQuery());
function someFunction(A $objectMaybe = null)
{
    if ($objectMaybe instanceof A) {
        $objectMaybe->doSomething();
    }
}

        foreach ($this->getRelationshipMap() as $key => $map) {
            if (is_array($include) && ! in_array($key, $include)) {
                continue;
            }

            $binds = $this->getRelationshipBinds($collection, $key, $map['defined_in']['entity']);

            if (empty($binds)) {
                continue;
            }

            $query = sprintf(
                'SELECT * FROM %s LEFT JOIN %s ON %s.%s = %s.%s WHERE %s.%s IN (%s)',
                $map['defined_in']['table'],
                $map['target']['table'],
                $map['target']['table'],
                $map['target']['primary'],
                $map['defined_in']['table'],
                $map['target']['relationship'],
                $map['defined_in']['table'],
                $map['defined_in']['primary'],
                implode(',', $binds)
            );

            if (array_key_exists('sort', $rules)) {
                $query .= $this->buildSortPart($rules['sort'], $map['target']['table']);
            }

            $result = $this->dbal->fetchAll($query, []);

            $this->attachRelationshipsToCollection($collection, $key, $result);
        }
    }

    /**
     * Iterate a result set and attach the relationship to it's correct entity
     * within a collection.
     *
     * @param \Percy\Entity\Collection $collection
     * @param string                   $relationship
     * @param array                    $data
     *
     * @return void
     */
    protected function attachRelationshipsToCollection(Collection $collection, $relationship, array $data)
    {
        $map           = $this->getRelationshipMap($relationship);
        $relationships = array_column($data, $map['defined_in']['primary']);

        $remove = [$map['defined_in']['primary'], $map['target']['relationship']];

        foreach ($data as &$resource) {
            $resource = array_filter($resource, function ($key) use ($remove) {
                return (! in_array($key, $remove));
            }, ARRAY_FILTER_USE_KEY);
        }

        foreach ($collection->getIterator() as $entity) {
            $entityRels = $entity->getRelationshipMap();

            if (! array_key_exists($relationship, $entityRels)) {
                continue;
            }

            $keys = array_keys(preg_grep("/{$entity[$map['defined_in']['entity']]}/", $relationships));
            $rels = array_filter($data, function ($key) use ($keys) {
                return in_array($key, $keys);
            }, ARRAY_FILTER_USE_KEY);

            $rels = $this->buildCollection($rels, $entityRels[$relationship])->setTotal(count($rels));
            $this->decorate($rels, StoreInterface::ON_READ);

            $entity->addRelationship($relationship, $rels);
        }
    }

    /**
     * Return relationship bind conditional.
     *
     * @param \Percy\Entity\Collection $collection
     * @param string                   $relationship
     * @param string                   $key
     *
     * @return string
     */
    protected function getRelationshipBinds(Collection $collection, $relationship, $key)
    {
        $primaries = [];

        foreach ($collection->getIterator() as $entity) {
            if (! array_key_exists($relationship, $entity->getRelationshipMap())) {
                continue;
            }

            $primaries[] = "'{$entity[$key]}'";
        }

        return $primaries;
    }

    /**
     * Get possible relationships and the properties attached to them.
     *
     * @param string $relationship
     *
     * @throws \InvalidArgumentException when requested relationship is not defined
     * @throws \RuntimeException when map structure is defined incorrectly
     *
     * @return array
     */
    public function getRelationshipMap($relationship = null)
    {
        if (is_null($relationship)) {
            return $this->relationships;
        }

        if (! array_key_exists($relationship, $this->relationships)) {
            throw new InvalidArgumentException(
                sprintf('(%s) is not defined in the relationship map on (%s)', $relationship, get_class($this))
            );
        }

        $map = $this->relationships[$relationship];

        foreach ([
            'defined_in' => ['table', 'primary', 'entity'],
            'target'     => ['table', 'primary', 'relationship']
        ] as $key => $value) {
            if (! array_key_exists($key, $map) || ! is_array($map[$key])) {
                throw new RuntimeException(
                    sprintf(
                        'Relationship (%s) should contain the (%s) key and should be of type array on (%s)',
                        $relationship, $key, get_class($this)
                    )
                );
            }

            if (! empty(array_diff($value, array_keys($map[$key])))) {
                throw new RuntimeException(
                    sprintf(
                        '(%s) for relationship (%s) should contain keys (%s) on (%s)',
                        $key, $relationship, implode(', ', $value), get_class($this)
                    )
                );
            }
        }

        return $map;
    }

    /**
     * Returns table that repository is reading from.
     *
     * @return string
     */
    abstract protected function getTable();
}


1		<?php
2
3		namespace Percy\Repository;
4
5		use Aura\Sql\ExtendedPdoInterface;
6		use InvalidArgumentException;
7		use Percy\Decorator\DecoratorTrait;
8		use Percy\Entity\Collection;
9		use Percy\Entity\CollectionBuilderTrait;
10		use Percy\Entity\EntityInterface;
11		use Percy\Http\QueryStringParserTrait;
12		use Percy\Store\StoreInterface;
13		use Psr\Http\Message\ServerRequestInterface;
14		use RuntimeException;
15
16		abstract class AbstractSqlRepository implements RepositoryInterface
17		{
18		use CollectionBuilderTrait;
19		use DecoratorTrait;
20		use QueryStringParserTrait;
21
22		/**
23		* @var \Aura\Sql\ExtendedPdoInterface
24		*/
25		protected $dbal;
26
27		/**
28		*
29		* @var mixed
30		*/
31		protected $relationships = [];
32
33		/**
34		* Construct.
35		*
36		* @param \Aura\Sql\ExtendedPdoInterface $dbal
37		*/
38	2	public function __construct(ExtendedPdoInterface $dbal)
39		{
40	2	$this->dbal = $dbal;
41	2	}
42
43		/**
44		* {@inheritdoc}
45		*/
46	1	public function countFromRequest(ServerRequestInterface $request)
47		{
48	1	$rules = $this->parseQueryString($request->getUri()->getQuery());
49	1	list($query, $params) = $this->buildQueryFromRules($rules, true);
50
51	1	return (int) $this->dbal->fetchOne($query, $params)['total'];
52		}
53
54		/**
55		* {@inheritdoc}
56		*/
57	1	public function getFromRequest(ServerRequestInterface $request)
58		{
59	1	$rules = $this->parseQueryString($request->getUri()->getQuery());
60
61	1	list($query, $params) = $this->buildQueryFromRules($rules);
62
63	1	if (array_key_exists('sort', $rules) && ! array_key_exists('search', $rules)) {
64	1	$query .= $this->buildSortPart($rules['sort'], $this->getTable());
65	1	}
66
67	1	if (array_key_exists('search', $rules)) {
68		$query .= sprintf(' ORDER BY MATCH (%s) AGAINST (:match_bind) > :score_bind', $rules['search']['fields']);
69		}
70
71	1	if (array_key_exists('limit', $rules)) {
72	1	$query .= ' LIMIT ';
73	1	$query .= (array_key_exists('offset', $rules)) ? sprintf('%d,', $rules['offset']) : '';
74	1	$query .= $rules['limit'];
75	1	}
76
77	1	$query = trim(preg_replace('!\s+!', ' ', $query));
78
79	1	$collection = $this->buildCollection($this->dbal->fetchAll($query, $params))
80	1	->setTotal($this->countFromRequest($request));
81
82	1	$this->decorate($collection, StoreInterface::ON_READ);
83
84	1	return $collection;
85		}
86
87		/**
88		* Build the sort part of the query.
89		*
90		* @param array\|string $sorts
91		* @param string $table
92		*
93		* @return string
94		*/
95	1	protected function buildSortPart($sorts, $table)
96		{
97	1	if (is_string($sorts) && $sorts === 'RAND()') {
98		return ' ORDER BY RAND()';
99		}
100
101	1	$fields = [];
102
103	1	foreach ($sorts as $sort) {
		0 ignored issues – show Bug introduced 2016-03-03 16:55 UTC by Report Bug Copy Issue Report The expression `$sorts` of type `array\|string` 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...
104	1	if (substr($sort['field'], 0, strlen($table)) !== $table) {
105		continue;
106		}
107
108	1	$fields[] = sprintf('%s %s', $sort['field'], strtoupper($sort['direction']));
109	1	}
110
111	1	return (empty($fields)) ? '' : sprintf(' ORDER BY %s', implode(', ', $fields));
112		}
113
114		/**
115		* Build a base query without sorting and limits from filter rules.
116		*
117		* @param array $rules
118		* @param boolean $count
119		*
120		* @return array
121		*/
122	1	protected function buildQueryFromRules(array $rules, $count = false)
123		{
124	1	$start = ($count === false) ? 'SELECT * FROM ' : 'SELECT , COUNT() as total FROM ';
125
126	1	$query = $start . $this->getTable();
127
128	1	$params = [];
129
130	1	if (array_key_exists('filter', $rules)) {
131	1	foreach ($rules['filter'] as $key => $where) {
132	1	$keyword = ($key === 0) ? ' WHERE' : ' AND';
133	1	$delimiter = strtoupper($where['delimiter']);
134	1	$binding = (in_array($delimiter, ['IN', 'NOT IN'])) ? sprintf('(:%s)', $where['binding']) : ':' . $where['binding'];
135	1	$query .= sprintf('%s %s %s %s', $keyword, $where['field'], $delimiter, $binding);
136
137	1	$params[$where['binding']] = $where['value'];
138	1	}
139	1	}
140
141	1	if (array_key_exists('search', $rules)) {
142		$keyword = (array_key_exists('filter', $rules)) ? ' AND' : ' WHERE';
143		$query .= sprintf('%s MATCH (%s) AGAINST (:match_bind IN BOOLEAN MODE)', $keyword, $rules['search']['columns']);
144		$query .= sprintf(' HAVING MATCH (%s) AGAINST (:match_bind) > :score_bind', $rules['search']['columns']);
145
146		$params['match_bind'] = $rules['search']['term'];
147		$params['score_bind'] = (array_key_exists('minscore', $rules)) ? $rules['minscore'] : 0;
148		}
149
150	1	return [$query, $params];
151		}
152
153		/**
154		* {@inheritdoc}
155		*/
156	1	public function countByField($field, $value, ServerRequestInterface $request = null)
157		{
158	1	$query = sprintf(
159	1	"SELECT COUNT(*) as total FROM %s WHERE %s.%s IN (:%s)",
160	1	$this->getTable(),
161	1	$this->getTable(),
162	1	$field,
163		$field
164	1	);
165
166		$params = [
167	1	$field => implode(',', (array) $value)
168	1	];
169
170	1	return (int) $this->dbal->fetchOne($query, $params)['total'];
171		}
172
173		/**
174		* {@inheritdoc}
175		*/
176	1	public function getByField($field, $value, ServerRequestInterface $request = null)
177		{
178	1	$query = sprintf(
179	1	'SELECT * FROM %s WHERE %s.%s IN (:%s)',
180	1	$this->getTable(),
181	1	$this->getTable(),
182	1	$field,
183		$field
184	1	);
185
186		// @todo - allow extra filtering from request
187
188		$params = [
189	1	$field => implode(',', (array) $value)
190	1	];
191
192	1	$collection = $this->buildCollection($this->dbal->fetchAll($query, $params))
193	1	->setTotal($this->countByField($field, $value));
194
195	1	$this->decorate($collection, StoreInterface::ON_READ);
196
197	1	return $collection;
198		}
199
200		/**
201		* {@inheritdoc}
202		*/
203		public function attachRelationships(
204		Collection $collection,
205		$include = null,
206		ServerRequestInterface $request = null
207		) {
208		if (is_null($include)) {
209		return;
210		}
211
212		$rules = $this->parseQueryString($request->getUri()->getQuery());
		0 ignored issues – show Bug introduced 2016-03-03 16:55 UTC by Report Bug Copy Issue Report It seems like `$request` is not always an object, but can also be of type `null`. Maybe add an additional type check? If a variable is not always an object, we recommend to add an additional type check to ensure your method call is safe: function someFunction(A $objectMaybe = null) { if ($objectMaybe instanceof A) { $objectMaybe->doSomething(); } } Loading history...
213
214		foreach ($this->getRelationshipMap() as $key => $map) {
215		if (is_array($include) && ! in_array($key, $include)) {
216		continue;
217		}
218
219		$binds = $this->getRelationshipBinds($collection, $key, $map['defined_in']['entity']);
220
221		if (empty($binds)) {
222		continue;
223		}
224
225		$query = sprintf(
226		'SELECT * FROM %s LEFT JOIN %s ON %s.%s = %s.%s WHERE %s.%s IN (%s)',
227		$map['defined_in']['table'],
228		$map['target']['table'],
229		$map['target']['table'],
230		$map['target']['primary'],
231		$map['defined_in']['table'],
232		$map['target']['relationship'],
233		$map['defined_in']['table'],
234		$map['defined_in']['primary'],
235		implode(',', $binds)
236		);
237
238		if (array_key_exists('sort', $rules)) {
239		$query .= $this->buildSortPart($rules['sort'], $map['target']['table']);
240		}
241
242		$result = $this->dbal->fetchAll($query, []);
243
244		$this->attachRelationshipsToCollection($collection, $key, $result);
245		}
246		}
247
248		/**
249		* Iterate a result set and attach the relationship to it's correct entity
250		* within a collection.
251		*
252		* @param \Percy\Entity\Collection $collection
253		* @param string $relationship
254		* @param array $data
255		*
256		* @return void
257		*/
258		protected function attachRelationshipsToCollection(Collection $collection, $relationship, array $data)
259		{
260		$map = $this->getRelationshipMap($relationship);
261		$relationships = array_column($data, $map['defined_in']['primary']);
262
263		$remove = [$map['defined_in']['primary'], $map['target']['relationship']];
264
265		foreach ($data as &$resource) {
266		$resource = array_filter($resource, function ($key) use ($remove) {
267		return (! in_array($key, $remove));
268		}, ARRAY_FILTER_USE_KEY);
269		}
270
271		foreach ($collection->getIterator() as $entity) {
272		$entityRels = $entity->getRelationshipMap();
273
274		if (! array_key_exists($relationship, $entityRels)) {
275		continue;
276		}
277
278		$keys = array_keys(preg_grep("/{$entity[$map['defined_in']['entity']]}/", $relationships));
279		$rels = array_filter($data, function ($key) use ($keys) {
280		return in_array($key, $keys);
281		}, ARRAY_FILTER_USE_KEY);
282
283		$rels = $this->buildCollection($rels, $entityRels[$relationship])->setTotal(count($rels));
284		$this->decorate($rels, StoreInterface::ON_READ);
285
286		$entity->addRelationship($relationship, $rels);
287		}
288		}
289
290		/**
291		* Return relationship bind conditional.
292		*
293		* @param \Percy\Entity\Collection $collection
294		* @param string $relationship
295		* @param string $key
296		*
297		* @return string
298		*/
299		protected function getRelationshipBinds(Collection $collection, $relationship, $key)
300		{
301		$primaries = [];
302
303		foreach ($collection->getIterator() as $entity) {
304		if (! array_key_exists($relationship, $entity->getRelationshipMap())) {
305		continue;
306		}
307
308		$primaries[] = "'{$entity[$key]}'";
309		}
310
311		return $primaries;
312		}
313
314		/**
315		* Get possible relationships and the properties attached to them.
316		*
317		* @param string $relationship
318		*
319		* @throws \InvalidArgumentException when requested relationship is not defined
320		* @throws \RuntimeException when map structure is defined incorrectly
321		*
322		* @return array
323		*/
324		public function getRelationshipMap($relationship = null)
325		{
326		if (is_null($relationship)) {
327		return $this->relationships;
328		}
329
330		if (! array_key_exists($relationship, $this->relationships)) {
331		throw new InvalidArgumentException(
332		sprintf('(%s) is not defined in the relationship map on (%s)', $relationship, get_class($this))
333		);
334		}
335
336		$map = $this->relationships[$relationship];
337
338		foreach ([
339		'defined_in' => ['table', 'primary', 'entity'],
340		'target' => ['table', 'primary', 'relationship']
341		] as $key => $value) {
342		if (! array_key_exists($key, $map) \|\| ! is_array($map[$key])) {
343		throw new RuntimeException(
344		sprintf(
345		'Relationship (%s) should contain the (%s) key and should be of type array on (%s)',
346		$relationship, $key, get_class($this)
347		)
348		);
349		}
350
351		if (! empty(array_diff($value, array_keys($map[$key])))) {
352		throw new RuntimeException(
353		sprintf(
354		'(%s) for relationship (%s) should contain keys (%s) on (%s)',
355		$key, $relationship, implode(', ', $value), get_class($this)
356		)
357		);
358		}
359		}
360
361		return $map;
362		}
363
364		/**
365		* Returns table that repository is reading from.
366		*
367		* @return string
368		*/
369		abstract protected function getTable();
370		}
371

acredula / percy

Pull Request — master (#30)

AbstractSqlRepository::buildQueryFromRules() D

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like