AbstractSqlRepository::buildQueryFromRules() - Code Metrics - hmsweb/percy - Measure and Improve Code Quality continuously with Scrutinizer

AbstractSqlRepository::buildQueryFromRules() C
last analyzed 2020-02-26 16:57 UTC

↳ Parent: AbstractSqlRepository

Complexity

Conditions	17
Paths	60

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	47
CRAP Score	17

Importance

Changes

Metric	Value
dl	0
loc	63
ccs	47
cts	47
cp	1
rs	5.2166
c	0
b	0
f	0
cc	17
nc	60
nop	2
crap	17

How to fix Long Method Complexity

<?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)) {
            $entity    = $this->getEntityType();
            $entity    = new $entity;
            $mapping   = $entity->getMapping();
            $whitelist = array_keys($mapping);

            $query .= $this->buildSortPart($rules['sort'], $this->getTable(), $whitelist);
        }

        if (array_key_exists('search', $rules) && $this->acceptableField($rules['search']['fields'])) {
            $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 .= sprintf('%d', $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
     * @param array        $whitelist
     *
     * @return string
     */
    protected function buildSortPart($sorts, $table, array $whitelist)
    {
        if (is_string($sorts) && $sorts === 'RAND()') {
            return ' ORDER BY RAND()';
        }

        if (! is_array($sorts)) {
            return '';
        }

        $fields = [];

        foreach ($sorts as $sort) {
            $field = explode('.', $sort['field']);

            if (count($field) !== 2) {
                throw new InvalidArgumentException('Sort paramater is formatted incorrectly');
            }

            if ($field[0] !== $table && count($sorts) > 1) {
                continue;
            }

            if ($field[0] !== $table && count($sorts) < 2 && $field[0] === $this->getTable()) {
                continue;
            }

            if ($field[0] !== $table && count($sorts) < 2) {
                throw new InvalidArgumentException(
                    sprintf('(%s) is not a whitelisted field to sort by', $sort['field'])
                );
            }

            if (! in_array($field[1], $whitelist)) {
                throw new InvalidArgumentException(
                    sprintf('(%s) is not a whitelisted field to sort by', $sort['field'])
                );
            }

            $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) {
                $this->acceptableField($where['field']);

                $isNull = ($where['value'] === 'null') ? true : false;

                $keyword   = ($key === 0) ? ' WHERE' : ' AND';
                $delimiter = strtoupper($where['delimiter']);
                $binding   = (in_array($delimiter, ['IN', 'NOT IN'])) ? '(' . $this->dbal->quote(explode(',', $where['value'])) . ')' : ':' . $where['binding'];

                if ($isNull === true) {
                    $delimiter = ($delimiter === '=') ? 'IS' : 'IS NOT';
                    $binding   = 'null';
                }

                $query .= sprintf('%s %s %s %s', $keyword, $where['field'], $delimiter, $binding);

                if (! in_array($delimiter, ['IN', 'NOT IN'])) {
                    $params[$where['binding']] = $where['value'];
                }
            }
        }

        if (array_key_exists('has', $rules)) {
            $keyword = (array_key_exists('filter', $rules)) ? ' AND' : ' WHERE';

            foreach ($rules['has'] as $has) {
                $relationship = $this->getRelationshipMap($has);

                $query .= sprintf(
                    '%s (SELECT COUNT(%s.%s) FROM %s WHERE %s.%s = %s.%s) > 0',
                    $keyword,
                    $relationship['defined_in']['table'],
                    $relationship['defined_in']['primary'],
                    $relationship['defined_in']['table'],
                    $relationship['defined_in']['table'],
                    $relationship['defined_in']['primary'],
                    $this->getTable(),
                    $relationship['defined_in']['entity']
                );

                $keyword = ' AND';
            }
        }

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

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

        return [$query, $params];
    }

    /**
     * Asserts that a field is acceptable to filter on.
     *
     * @param string $name
     *
     * @return boolean
     */
    protected function acceptableField($name)
    {
        $entity = $this->getEntityType();
        $entity = new $entity;

        foreach (explode(',', $name) as $field) {
            if (! array_key_exists($field, $entity->getMapping())) {
                throw new InvalidArgumentException(
                    sprintf('(%s) is not a whitelisted field to filter, search or sort by', $field)
                );
            }
        }

        return true;
    }

    /**
     * {@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 => $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
        );

        $rules = [];

        if ($request instanceof ServerRequestInterface) {
            $rules = $this->parseQueryString($request->getUri()->getQuery());
        }

        if (is_array($value)) {
            if (! array_key_exists('sort', $rules)) {
                $query .= sprintf(
                    ' ORDER BY FIND_IN_SET(%s.%s, ' . $this->dbal->quote(implode(',', $value)) . ')',
                    $this->getTable(),
                    $field
                );
            } else {
                $entity    = $this->getEntityType();
                $entity    = new $entity;
                $mapping   = $entity->getMapping();
                $whitelist = array_keys($mapping);
                $query    .= $this->buildSortPart($rules['sort'], $this->getTable(), $whitelist);
            }
        }

        // @todo - allow extra filtering from request

        $params = [
            $field => $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 (count($collection) === 0) {
            return;
        }

        $bind = [];

        $rels = $collection->getIterator()->current()->getRelationshipMap();

        $rules = ($request instanceof ServerRequestInterface)
               ? $this->parseQueryString($request->getUri()->getQuery())
               : [];

        if (is_string($include)) {
            $include = explode(',', $include);
        }

        if (! array_key_exists('include', $rules) && is_array($include)) {
            $rules['include'] = [];

            foreach ($include as $key) {
                $rules['include'][$key] = [];
            }
        }

        foreach ($this->getRelationshipMap() as $key => $map) {
            if (! array_key_exists('include', $rules)) {
                continue;
            }

            if (! array_key_exists($key, $rules['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 (:relationships)',
                $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']
            );

            $options = (array_key_exists('include', $rules)) ? $rules['include'][$key] : [];

            if (! empty($options['filter'])) {
                $query .= $this->buildRelationshipFilterQueryPart($map, $options['filter']);

                foreach ($options['filter'] as $filter) {
                    $bind[$filter['binding']] = $filter['value'];
                }
            }

            if (array_key_exists('sort', $rules)) {
                $whitelist = [];

                if (array_key_exists($key, $rels)) {
                    $entity    = $rels[$key];
                    $whitelist = array_keys((new $entity)->getMapping());
                }

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

            if (array_key_exists('limit', $options) && ! is_null($options['limit'])) {
                $query .= ' LIMIT ' . (int) $options['limit'];
            }

            $bind['relationships'] = $binds;

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

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

    /**
     * Build conditionals part of query to filter relationships.
     *
     * @param array $map
     * @param array $filters
     *
     * @return string
     */
    protected function buildRelationshipFilterQueryPart($map, $filters)
    {
        $query = '';

        foreach ($filters as $filter) {
            $query .= sprintf(
                ' AND %s.%s %s :%s',
                $map['target']['table'],
                $filter['field'],
                $filter['delimiter'],
                $filter['binding']
            );
        }

        return $query;
    }

    /**
     * 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	6	public function __construct(ExtendedPdoInterface $dbal)
39		{
40	6	$this->dbal = $dbal;
41	6	}
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	2	public function getFromRequest(ServerRequestInterface $request)
58		{
59	2	$rules = $this->parseQueryString($request->getUri()->getQuery());
60
61	2	list($query, $params) = $this->buildQueryFromRules($rules);
62
63	2	if (array_key_exists('sort', $rules) && ! array_key_exists('search', $rules)) {
64	2	$entity = $this->getEntityType();
65	2	$entity = new $entity;
66	2	$mapping = $entity->getMapping();
67	2	$whitelist = array_keys($mapping);
68
69	2	$query .= $this->buildSortPart($rules['sort'], $this->getTable(), $whitelist);
70	1	}
71
72	1	if (array_key_exists('search', $rules) && $this->acceptableField($rules['search']['fields'])) {
73	1	$query .= sprintf(' ORDER BY MATCH (%s) AGAINST (:match_bind) > :score_bind', $rules['search']['fields']);
74	1	}
75
76	1	if (array_key_exists('limit', $rules)) {
77	1	$query .= ' LIMIT ';
78	1	$query .= (array_key_exists('offset', $rules)) ? sprintf('%d,', $rules['offset']) : '';
79	1	$query .= sprintf('%d', $rules['limit']);
80	1	}
81
82	1	$query = trim(preg_replace('!\s+!', ' ', $query));
83
84	1	$collection = $this->buildCollection($this->dbal->fetchAll($query, $params))
85	1	->setTotal($this->countFromRequest($request));
86
87	1	$this->decorate($collection, StoreInterface::ON_READ);
88
89	1	return $collection;
90		}
91
92		/**
93		* Build the sort part of the query.
94		*
95		* @param array\|string $sorts
96		* @param string $table
97		* @param array $whitelist
98		*
99		* @return string
100		*/
101	4	protected function buildSortPart($sorts, $table, array $whitelist)
102		{
103	4	if (is_string($sorts) && $sorts === 'RAND()') {
104	1	return ' ORDER BY RAND()';
105		}
106
107	4	if (! is_array($sorts)) {
108		return '';
109		}
110
111	4	$fields = [];
112
113	4	foreach ($sorts as $sort) {
114	4	$field = explode('.', $sort['field']);
115
116	4	if (count($field) !== 2) {
117	1	throw new InvalidArgumentException('Sort paramater is formatted incorrectly');
118		}
119
120	4	if ($field[0] !== $table && count($sorts) > 1) {
121	2	continue;
122		}
123
124	4	if ($field[0] !== $table && count($sorts) < 2 && $field[0] === $this->getTable()) {
125		continue;
126		}
127
128	4	if ($field[0] !== $table && count($sorts) < 2) {
129	1	throw new InvalidArgumentException(
130	1	sprintf('(%s) is not a whitelisted field to sort by', $sort['field'])
131	1	);
132		}
133
134	4	if (! in_array($field[1], $whitelist)) {
135	1	throw new InvalidArgumentException(
136	1	sprintf('(%s) is not a whitelisted field to sort by', $sort['field'])
137	1	);
138		}
139
140	3	$fields[] = sprintf('%s %s', $sort['field'], strtoupper($sort['direction']));
141	3	}
142
143	3	return (empty($fields)) ? '' : sprintf(' ORDER BY %s', implode(', ', $fields));
144		}
145
146		/**
147		* Build a base query without sorting and limits from filter rules.
148		*
149		* @param array $rules
150		* @param boolean $count
151		*
152		* @return array
153		*/
154	2	protected function buildQueryFromRules(array $rules, $count = false)
155		{
156	2	$start = ($count === false) ? 'SELECT * FROM ' : 'SELECT , COUNT() as total FROM ';
157	2	$query = $start . $this->getTable();
158
159	2	$params = [];
160
161	2	if (array_key_exists('filter', $rules)) {
162	2	foreach ($rules['filter'] as $key => $where) {
163	2	$this->acceptableField($where['field']);
164
165	1	$isNull = ($where['value'] === 'null') ? true : false;
166
167	1	$keyword = ($key === 0) ? ' WHERE' : ' AND';
168	1	$delimiter = strtoupper($where['delimiter']);
169	1	$binding = (in_array($delimiter, ['IN', 'NOT IN'])) ? '(' . $this->dbal->quote(explode(',', $where['value'])) . ')' : ':' . $where['binding'];
170
171	1	if ($isNull === true) {
172	1	$delimiter = ($delimiter === '=') ? 'IS' : 'IS NOT';
173	1	$binding = 'null';
174	1	}
175
176	1	$query .= sprintf('%s %s %s %s', $keyword, $where['field'], $delimiter, $binding);
177
178	1	if (! in_array($delimiter, ['IN', 'NOT IN'])) {
179	1	$params[$where['binding']] = $where['value'];
180	1	}
181	1	}
182	1	}
183
184	2	if (array_key_exists('has', $rules)) {
185	1	$keyword = (array_key_exists('filter', $rules)) ? ' AND' : ' WHERE';
186
187	1	foreach ($rules['has'] as $has) {
188	1	$relationship = $this->getRelationshipMap($has);
189
190	1	$query .= sprintf(
191	1	'%s (SELECT COUNT(%s.%s) FROM %s WHERE %s.%s = %s.%s) > 0',
192	1	$keyword,
193	1	$relationship['defined_in']['table'],
194	1	$relationship['defined_in']['primary'],
195	1	$relationship['defined_in']['table'],
196	1	$relationship['defined_in']['table'],
197	1	$relationship['defined_in']['primary'],
198	1	$this->getTable(),
199	1	$relationship['defined_in']['entity']
200	1	);
201
202	1	$keyword = ' AND';
203	1	}
204	1	}
205
206	2	if (array_key_exists('search', $rules) && $this->acceptableField($rules['search']['fields'])) {
207	1	$keyword = (array_key_exists('filter', $rules)) ? ' AND' : ' WHERE';
208	1	$query .= sprintf('%s MATCH (%s) AGAINST (:match_bind IN BOOLEAN MODE)', $keyword, $rules['search']['fields']);
209	1	$query .= sprintf(' HAVING MATCH (%s) AGAINST (:match_bind) > :score_bind', $rules['search']['fields']);
210
211	1	$params['match_bind'] = $rules['search']['term'];
212	1	$params['score_bind'] = (array_key_exists('minscore', $rules)) ? $rules['minscore'] : 0;
213	1	}
214
215	2	return [$query, $params];
216		}
217
218		/**
219		* Asserts that a field is acceptable to filter on.
220		*
221		* @param string $name
222		*
223		* @return boolean
224		*/
225	2	protected function acceptableField($name)
226		{
227	2	$entity = $this->getEntityType();
228	2	$entity = new $entity;
229
230	2	foreach (explode(',', $name) as $field) {
231	2	if (! array_key_exists($field, $entity->getMapping())) {
232	1	throw new InvalidArgumentException(
233	1	sprintf('(%s) is not a whitelisted field to filter, search or sort by', $field)
234	1	);
235		}
236	1	}
237
238	1	return true;
239		}
240
241		/**
242		* {@inheritdoc}
243		*/
244	2	public function countByField($field, $value, ServerRequestInterface $request = null)
245		{
246	2	$query = sprintf(
247	2	"SELECT COUNT(*) as total FROM %s WHERE %s.%s IN (:%s)",
248	2	$this->getTable(),
249	2	$this->getTable(),
250	2	$field,
251		$field
252	2	);
253
254		$params = [
255		$field => $value
256	2	];
257
258	2	return (int) $this->dbal->fetchOne($query, $params)['total'];
259		}
260
261		/**
262		* {@inheritdoc}
263		*/
264	2	public function getByField($field, $value, ServerRequestInterface $request = null)
265		{
266	2	$query = sprintf(
267	2	'SELECT * FROM %s WHERE %s.%s IN (:%s)',
268	2	$this->getTable(),
269	2	$this->getTable(),
270	2	$field,
271		$field
272	2	);
273
274	2	$rules = [];
275
276	2	if ($request instanceof ServerRequestInterface) {
277	2	$rules = $this->parseQueryString($request->getUri()->getQuery());
278	2	}
279
280	2	if (is_array($value)) {
281	2	if (! array_key_exists('sort', $rules)) {
282	1	$query .= sprintf(
283	1	' ORDER BY FIND_IN_SET(%s.%s, ' . $this->dbal->quote(implode(',', $value)) . ')',
284	1	$this->getTable(),
285		$field
286	1	);
287	1	} else {
288	1	$entity = $this->getEntityType();
289	1	$entity = new $entity;
290	1	$mapping = $entity->getMapping();
291	1	$whitelist = array_keys($mapping);
292	1	$query .= $this->buildSortPart($rules['sort'], $this->getTable(), $whitelist);
293		}
294	2	}
295
296		// @todo - allow extra filtering from request
297
298		$params = [
299		$field => $value
300	2	];
301
302	2	$collection = $this->buildCollection($this->dbal->fetchAll($query, $params))
303	2	->setTotal($this->countByField($field, $value));
304
305	2	$this->decorate($collection, StoreInterface::ON_READ);
306
307	2	return $collection;
308		}
309
310		/**
311		* {@inheritdoc}
312		*/
313	2	public function attachRelationships(
314		Collection $collection,
315		$include = null,
316		ServerRequestInterface $request = null
317		) {
318	2	if (count($collection) === 0) {
319	1	return;
320		}
321
322	1	$bind = [];
323
324	1	$rels = $collection->getIterator()->current()->getRelationshipMap();
325
326	1	$rules = ($request instanceof ServerRequestInterface)
327	1	? $this->parseQueryString($request->getUri()->getQuery())
328	1	: [];
329
330	1	if (is_string($include)) {
331		$include = explode(',', $include);
332		}
333
334	1	if (! array_key_exists('include', $rules) && is_array($include)) {
335		$rules['include'] = [];
336
337		foreach ($include as $key) {
338		$rules['include'][$key] = [];
339		}
340		}
341
342	1	foreach ($this->getRelationshipMap() as $key => $map) {
343	1	if (! array_key_exists('include', $rules)) {
344		continue;
345		}
346
347	1	if (! array_key_exists($key, $rules['include'])) {
348		continue;
349		}
350
351	1	$binds = $this->getRelationshipBinds($collection, $key, $map['defined_in']['entity']);
352
353	1	if (empty($binds)) {
354		continue;
355		}
356
357	1	$query = sprintf(
358	1	'SELECT * FROM %s LEFT JOIN %s ON %s.%s = %s.%s WHERE %s.%s IN (:relationships)',
359	1	$map['defined_in']['table'],
360	1	$map['target']['table'],
361	1	$map['target']['table'],
362	1	$map['target']['primary'],
363	1	$map['defined_in']['table'],
364	1	$map['target']['relationship'],
365	1	$map['defined_in']['table'],
366	1	$map['defined_in']['primary']
367	1	);
368
369	1	$options = (array_key_exists('include', $rules)) ? $rules['include'][$key] : [];
370
371	1	if (! empty($options['filter'])) {
372	1	$query .= $this->buildRelationshipFilterQueryPart($map, $options['filter']);
373
374	1	foreach ($options['filter'] as $filter) {
375	1	$bind[$filter['binding']] = $filter['value'];
376	1	}
377	1	}
378
379	1	if (array_key_exists('sort', $rules)) {
380	1	$whitelist = [];
381
382	1	if (array_key_exists($key, $rels)) {
383	1	$entity = $rels[$key];
384	1	$whitelist = array_keys((new $entity)->getMapping());
385	1	}
386
387	1	$query .= $this->buildSortPart($rules['sort'], $map['target']['table'], $whitelist);
388	1	}
389
390	1	if (array_key_exists('limit', $options) && ! is_null($options['limit'])) {
391		$query .= ' LIMIT ' . (int) $options['limit'];
392		}
393
394	1	$bind['relationships'] = $binds;
395
396	1	$result = $this->dbal->fetchAll($query, $bind);
397
398	1	$this->attachRelationshipsToCollection($collection, $key, $result);
399	1	}
400	1	}
401
402		/**
403		* Build conditionals part of query to filter relationships.
404		*
405		* @param array $map
406		* @param array $filters
407		*
408		* @return string
409		*/
410	1	protected function buildRelationshipFilterQueryPart($map, $filters)
411		{
412	1	$query = '';
413
414	1	foreach ($filters as $filter) {
415	1	$query .= sprintf(
416	1	' AND %s.%s %s :%s',
417	1	$map['target']['table'],
418	1	$filter['field'],
419	1	$filter['delimiter'],
420	1	$filter['binding']
421	1	);
422	1	}
423
424	1	return $query;
425		}
426
427		/**
428		* Iterate a result set and attach the relationship to it's correct entity
429		* within a collection.
430		*
431		* @param \Percy\Entity\Collection $collection
432		* @param string $relationship
433		* @param array $data
434		*
435		* @return void
436		*/
437	1	protected function attachRelationshipsToCollection(Collection $collection, $relationship, array $data)
438		{
439	1	$map = $this->getRelationshipMap($relationship);
440	1	$relationships = array_column($data, $map['defined_in']['primary']);
441
442	1	$remove = [$map['defined_in']['primary'], $map['target']['relationship']];
443
444	1	foreach ($data as &$resource) {
445		$resource = array_filter($resource, function ($key) use ($remove) {
446		return (! in_array($key, $remove));
447		}, ARRAY_FILTER_USE_KEY);
448	1	}
449
450	1	foreach ($collection->getIterator() as $entity) {
451	1	$entityRels = $entity->getRelationshipMap();
452
453	1	if (! array_key_exists($relationship, $entityRels)) {
454		continue;
455		}
456
457	1	$keys = array_keys(preg_grep("/{$entity[$map['defined_in']['entity']]}/", $relationships));
458	1	$rels = array_filter($data, function ($key) use ($keys) {
459		return in_array($key, $keys);
460	1	}, ARRAY_FILTER_USE_KEY);
461
462	1	$rels = $this->buildCollection($rels, $entityRels[$relationship])->setTotal(count($rels));
463	1	$this->decorate($rels, StoreInterface::ON_READ);
464
465	1	$entity->addRelationship($relationship, $rels);
466	1	}
467	1	}
468
469		/**
470		* Return relationship bind conditional.
471		*
472		* @param \Percy\Entity\Collection $collection
473		* @param string $relationship
474		* @param string $key
475		*
476		* @return string
477		*/
478	1	protected function getRelationshipBinds(Collection $collection, $relationship, $key)
479		{
480	1	$primaries = [];
481
482	1	foreach ($collection->getIterator() as $entity) {
483	1	if (! array_key_exists($relationship, $entity->getRelationshipMap())) {
484		continue;
485		}
486
487	1	$primaries[] = $entity[$key];
488	1	}
489
490	1	return $primaries;
491		}
492
493		/**
494		* Get possible relationships and the properties attached to them.
495		*
496		* @param string $relationship
497		*
498		* @throws \InvalidArgumentException when requested relationship is not defined
499		* @throws \RuntimeException when map structure is defined incorrectly
500		*
501		* @return array
502		*/
503	2	public function getRelationshipMap($relationship = null)
504		{
505	2	if (is_null($relationship)) {
506	1	return $this->relationships;
507		}
508
509	2	if (! array_key_exists($relationship, $this->relationships)) {
510		throw new InvalidArgumentException(
511		sprintf('(%s) is not defined in the relationship map on (%s)', $relationship, get_class($this))
512		);
513		}
514
515	2	$map = $this->relationships[$relationship];
516
517		foreach ([
518	2	'defined_in' => ['table', 'primary', 'entity'],
519	2	'target' => ['table', 'primary', 'relationship']
520	2	] as $key => $value) {
521	2	if (! array_key_exists($key, $map) \|\| ! is_array($map[$key])) {
522		throw new RuntimeException(
523		sprintf(
524		'Relationship (%s) should contain the (%s) key and should be of type array on (%s)',
525		$relationship, $key, get_class($this)
526		)
527		);
528		}
529
530	2	if (! empty(array_diff($value, array_keys($map[$key])))) {
531		throw new RuntimeException(
532		sprintf(
533		'(%s) for relationship (%s) should contain keys (%s) on (%s)',
534		$key, $relationship, implode(', ', $value), get_class($this)
535		)
536		);
537		}
538	2	}
539
540	2	return $map;
541		}
542
543		/**
544		* Returns table that repository is reading from.
545		*
546		* @return string
547		*/
548		abstract protected function getTable();
549		}
550

hmsweb / percy

AbstractSqlRepository::buildQueryFromRules() C last analyzed 2020-02-26 16:57 UTC

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like

AbstractSqlRepository::buildQueryFromRules() C
last analyzed 2020-02-26 16:57 UTC