MySQLData::addFilter() - Code Metrics - Inspection of "don't crash if filtering by a many field" - philiplb/CRUDlex - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 74b649...753ce2 )

by Philip

created 2016-09-10 21:55 UTC

MySQLData::addFilter() B

↳ Parent: MySQLData

Complexity

Conditions	5
Paths	5

Size

Total Lines	17
Code Lines	13

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	17
rs	8.8571
c	0
b	0
f	0
cc	5
eloc	13
nc	5
nop	3

<?php

/*
 * This file is part of the CRUDlex package.
 *
 * (c) Philip Lehmann-Böhm <[email protected]>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace CRUDlex;

use Doctrine\DBAL\Query\QueryBuilder;
use Doctrine\DBAL\Connection;

/**
 * MySQL Data implementation using a given Doctrine DBAL instance.
 */
class MySQLData extends AbstractData {

    /**
     * Holds the Doctrine DBAL instance.
     */
    protected $database;

    /**
     * Flag whether to use UUIDs as primary key.
     */
    protected $useUUIDs;


    protected function getManyFields() {
        $fields = $this->definition->getFieldNames(true);
        return array_filter($fields, function($field) {
            return $this->definition->getType($field) === 'many';
        });
    }

    protected function getFormFields() {
        $manyFields = $this->getManyFields();
        $formFields = [];
        foreach ($this->definition->getEditableFieldNames() as $field) {
            if (!in_array($field, $manyFields)) {
                $formFields[] = $field;
            }
        }
        return $formFields;
    }

    /**
     * Sets the values and parameters of the upcoming given query according
     * to the entity.
     *
     * @param Entity $entity
     * the entity with its fields and values
     * @param QueryBuilder $queryBuilder
     * the upcoming query
     * @param string $setMethod
     * what method to use on the QueryBuilder: 'setValue' or 'set'
     */
    protected function setValuesAndParameters(Entity $entity, QueryBuilder $queryBuilder, $setMethod) {
        $formFields = $this->getFormFields();
        $count      = count($formFields);
        for ($i = 0; $i < $count; ++$i) {
            $type  = $this->definition->getType($formFields[$i]);
            $value = $entity->get($formFields[$i]);
            if ($type == 'boolean') {
                $value = $value ? 1 : 0;
            }
            $queryBuilder->$setMethod('`'.$formFields[$i].'`', '?');
            $queryBuilder->setParameter($i, $value);
        }
    }

    /**
     * Performs the cascading children deletion.
     *
     * @param integer $id
     * the current entities id
     * @param boolean $deleteCascade
     * whether to delete children and sub children
     */
    protected function deleteChildren($id, $deleteCascade) {
        foreach ($this->definition->getChildren() as $childArray) {
            $childData = $this->definition->getServiceProvider()->getData($childArray[2]);
            $children  = $childData->listEntries([$childArray[1] => $id]);
            foreach ($children as $child) {
                $childData->doDelete($child, $deleteCascade);
            }
        }
    }

    /**
     * Checks whether the by id given entity still has children referencing it.
     *
     * @param integer $id
     * the current entities id
     *
     * @return boolean
     * true if the entity still has children
     */
    protected function hasChildren($id) {
        foreach ($this->definition->getChildren() as $child) {
            $queryBuilder = $this->database->createQueryBuilder();
            $queryBuilder
                ->select('COUNT(id)')
                ->from('`'.$child[0].'`', '`'.$child[0].'`')
                ->where('`'.$child[1].'` = ?')
                ->andWhere('deleted_at IS NULL')
                ->setParameter(0, $id);
            $queryResult = $queryBuilder->execute();
            $result      = $queryResult->fetch(\PDO::FETCH_NUM);
            if ($result[0] > 0) {
                return true;
            }
        }
        return false;
    }

    /**
     * {@inheritdoc}
     */
    protected function doDelete(Entity $entity, $deleteCascade) {
        $result = $this->shouldExecuteEvents($entity, 'before', 'delete');
        if (!$result) {
            return static::DELETION_FAILED_EVENT;
        }
        $id = $entity->get('id');
        if ($deleteCascade) {
            $this->deleteChildren($id, $deleteCascade);
        } elseif ($this->hasChildren($id)) {
            return static::DELETION_FAILED_STILL_REFERENCED;
        }

        $query = $this->database->createQueryBuilder();
        $query
            ->update('`'.$this->definition->getTable().'`')
            ->set('deleted_at', 'UTC_TIMESTAMP()')
            ->where('id = ?')
            ->setParameter(0, $id);

        $query->execute();
        $this->shouldExecuteEvents($entity, 'after', 'delete');
        return static::DELETION_SUCCESS;
    }

    /**
     * Adds sorting parameters to the query.
     *
     * @param QueryBuilder $queryBuilder
     * the query
     * @param $filter
     * the filter all resulting entities must fulfill, the keys as field names
     * @param $filterOperators
     * the operators of the filter like "=" defining the full condition of the field
     */
    protected function addFilter(QueryBuilder $queryBuilder, array $filter, array $filterOperators) {
        $i = 0;
        foreach ($filter as $field => $value) {
            if ($this->definition->getType($field) === 'many') {
                continue;
            }
            if ($value === null) {
                $queryBuilder->andWhere('`'.$field.'` IS NULL');
            } else {
                $operator = array_key_exists($field, $filterOperators) ? $filterOperators[$field] : '=';
                $queryBuilder
                    ->andWhere('`'.$field.'` '.$operator.' ?')
                    ->setParameter($i, $value);
            }
            $i++;
        }
    }

    /**
     * Adds pagination parameters to the query.
     *
     * @param QueryBuilder $queryBuilder
     * the query
     * @param integer|null $skip
     * the rows to skip
     * @param integer|null $amount
     * the maximum amount of rows
     */
    protected function addPagination(QueryBuilder $queryBuilder, $skip, $amount) {
        $queryBuilder->setMaxResults(9999999999);
        if ($amount !== null) {
            $queryBuilder->setMaxResults(abs(intval($amount)));
        }
        if ($skip !== null) {
            $queryBuilder->setFirstResult(abs(intval($skip)));
        }
    }

    /**
     * Adds sorting parameters to the query.
     *
     * @param QueryBuilder $queryBuilder
     * the query
     * @param string|null $sortField
     * the sort field
     * @param boolean|null $sortAscending
     * true if sort ascending, false if descending
     */
    protected function addSort(QueryBuilder $queryBuilder, $sortField, $sortAscending) {
        if ($sortField !== null) {

            $type = $this->definition->getType($sortField);
            if ($type === 'many') {
                $sortField = $this->definition->getInitialSortField();
            }

            $order = $sortAscending === true ? 'ASC' : 'DESC';
            $queryBuilder->orderBy('`'.$sortField.'`', $order);
        }
    }

    /**
     * Adds the id and name of referenced entities to the given entities. The
     * reference field is before the raw id of the referenced entity and after
     * the fetch, it's an array with the keys id and name.
     *
     * @param Entity[] &$entities
     * the entities to fetch the references for
     * @param string $field
     * the reference field
     */
    protected function fetchReferencesForField(array &$entities, $field) {
        $nameField    = $this->definition->getReferenceNameField($field);
        $queryBuilder = $this->database->createQueryBuilder();

        $ids = array_map(function(Entity $entity) use ($field) {
            return $entity->get($field);
        }, $entities);

        $referenceEntity = $this->definition->getReferenceEntity($field);
        $table           = $this->definition->getServiceProvider()->getData($referenceEntity)->getDefinition()->getTable();
        $queryBuilder
            ->from('`'.$table.'`', '`'.$table.'`')
            ->where('id IN (?)')
            ->andWhere('deleted_at IS NULL');
        if ($nameField) {
            $queryBuilder->select('id', $nameField);
        } else {
            $queryBuilder->select('id');
        }

        $queryBuilder->setParameter(0, $ids, Connection::PARAM_INT_ARRAY);

        $queryResult = $queryBuilder->execute();
        $rows        = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
        $amount      = count($entities);
        foreach ($rows as $row) {
            for ($i = 0; $i < $amount; ++$i) {
                if ($entities[$i]->get($field) == $row['id']) {
                    $value = ['id' => $entities[$i]->get($field)];
                    if ($nameField) {
                        $value['name'] = $row[$nameField];
                    }
                    $entities[$i]->set($field, $value);
                }
            }
        }
    }

    /**
     * Generates a new UUID.
     *
     * @return string|null
     * the new UUID or null if this instance isn't configured to do so
     */
    protected function generateUUID() {
        $uuid = null;
        if ($this->useUUIDs) {
            $sql    = 'SELECT UUID() as id';
            $result = $this->database->fetchAssoc($sql);
            $uuid   = $result['id'];
        }
        return $uuid;
    }

    protected function enrichWithMany(array $rows) {
        $manyFields = $this->getManyFields();
        $mapping = [];
$a = "a";
$ab = "ab";
$abc = "abc";
        foreach ($rows as $row) {
            foreach ($manyFields as $manyField) {
                $row[$manyField] = [];
            }
            $mapping[$row['id']] = $row;
        }
        foreach ($manyFields as $manyField) {
            $queryBuilder = $this->database->createQueryBuilder();
            $nameField    = $this->definition->getManyNameField($manyField);
            $thisField    = $this->definition->getManyThisField($manyField);
            $thatField    = $this->definition->getManyThatField($manyField);
            $entity       = $this->definition->getManyEntity($manyField);
            $entityTable  = $this->definition->getServiceProvider()->getData($entity)->getDefinition()->getTable();
            $nameSelect   = $nameField !== null ? ', t2.`'.$nameField.'` AS name' : '';
            $queryBuilder
                ->select('t1.`'.$thisField.'` AS this, t1.`'.$thatField.'` AS that'.$nameSelect)
                ->from('`'.$manyField.'`', 't1')
                ->leftJoin('t1', '`'.$entityTable.'`', 't2', 't2.id = t1.`'.$thatField.'`')
                ->where('t1.`'.$thisField.'` IN (?)')
                ->andWhere('t2.deleted_at IS NULL');
            $queryBuilder->setParameter(0, array_keys($mapping), Connection::PARAM_INT_ARRAY);
            $queryResult    = $queryBuilder->execute();
            $manyReferences = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
            foreach ($manyReferences as $manyReference) {
                $many = ['id' => $manyReference['that']];
                if ($nameField !== null) {
                    $many['name'] = $manyReference['name'];
                }
                $mapping[$manyReference['this']][$manyField][] = $many;
            }
        }
        return array_values($mapping);
    }

    protected function populateMany(Entity $entity) {
        $manyFields = $this->getManyFields();
        $id = $entity->get('id');
$a = "a";
$ab = "ab";
$abc = "abc";
        foreach ($manyFields as $manyField) {
            $thisField = $this->definition->getManyThisField($manyField);
            $thatField = $this->definition->getManyThatField($manyField);
            $this->database->delete($manyField, [$thisField => $id]);
            foreach ($entity->get($manyField) as $thatId) {
                $this->database->insert($manyField, [
                    $thisField => $id,
                    $thatField => $thatId['id']
                ]);
            }
        }
    }

    /**
     * Constructor.
     *
     * @param EntityDefinition $definition
     * the entity definition
     * @param FileProcessorInterface $fileProcessor
     * the file processor to use
     * @param $database
     * the Doctrine DBAL instance to use
     * @param boolean $useUUIDs
     * flag whether to use UUIDs as primary key
     */
    public function __construct(EntityDefinition $definition, FileProcessorInterface $fileProcessor, $database, $useUUIDs) {
        $this->definition    = $definition;
        $this->fileProcessor = $fileProcessor;
        $this->database      = $database;
        $this->useUUIDs      = $useUUIDs;
    }

    /**
     * {@inheritdoc}
     */
    public function get($id) {
        $entities = $this->listEntries(['id' => $id]);
        if (count($entities) == 0) {
            return null;
        }
        return $entities[0];
    }

    /**
     * {@inheritdoc}
     */
    public function listEntries(array $filter = [], array $filterOperators = [], $skip = null, $amount = null, $sortField = null, $sortAscending = null) {
        $fieldNames = $this->definition->getFieldNames();

        $queryBuilder = $this->database->createQueryBuilder();
        $table        = $this->definition->getTable();
        $queryBuilder
            ->select('`'.implode('`,`', $fieldNames).'`')
            ->from('`'.$table.'`', '`'.$table.'`')
            ->where('deleted_at IS NULL');

        $this->addFilter($queryBuilder, $filter, $filterOperators);
        $this->addPagination($queryBuilder, $skip, $amount);
        $this->addSort($queryBuilder, $sortField, $sortAscending);

        $queryResult = $queryBuilder->execute();
        $rows        = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
        $rows        = $this->enrichWithMany($rows);
        $entities    = [];
        foreach ($rows as $row) {
            $entities[] = $this->hydrate($row);
        }
        return $entities;
    }

    /**
     * {@inheritdoc}
     */
    public function create(Entity $entity) {

        $result = $this->shouldExecuteEvents($entity, 'before', 'create');
        if (!$result) {
            return false;
        }

        $queryBuilder = $this->database->createQueryBuilder();
        $queryBuilder
            ->insert('`'.$this->definition->getTable().'`')
            ->setValue('created_at', 'UTC_TIMESTAMP()')
            ->setValue('updated_at', 'UTC_TIMESTAMP()')
            ->setValue('version', 0);


        $this->setValuesAndParameters($entity, $queryBuilder, 'setValue');

        $id = $this->generateUUID();
        if ($this->useUUIDs) {
            $queryBuilder->setValue('`id`', '?');
            $uuidI = count($this->getFormFields());
            $queryBuilder->setParameter($uuidI, $id);
        }

        $queryBuilder->execute();

        if (!$this->useUUIDs) {
            $id = $this->database->lastInsertId();
        }

        $entity->set('id', $id);

        $createdEntity = $this->get($entity->get('id'));
        $entity->set('version', $createdEntity->get('version'));
        $entity->set('created_at', $createdEntity->get('created_at'));
        $entity->set('updated_at', $createdEntity->get('updated_at'));

        $this->populateMany($entity);

        $this->shouldExecuteEvents($entity, 'after', 'create');

        return true;
    }

    /**
     * {@inheritdoc}
     */
    public function update(Entity $entity) {

        $result = $this->shouldExecuteEvents($entity, 'before', 'update');
        if (!$result) {
            return false;
        }

        $formFields   = $this->getFormFields();
        $queryBuilder = $this->database->createQueryBuilder();
        $queryBuilder
            ->update('`'.$this->definition->getTable().'`')
            ->set('updated_at', 'UTC_TIMESTAMP()')
            ->set('version', 'version + 1')
            ->where('id = ?')
            ->setParameter(count($formFields), $entity->get('id'));

        $this->setValuesAndParameters($entity, $queryBuilder, 'set');
        $affected = $queryBuilder->execute();

        $this->populateMany($entity);

        $this->shouldExecuteEvents($entity, 'after', 'update');

        return $affected;
    }

    /**
     * {@inheritdoc}
     */
    public function getIdToNameMap($entity, $nameField) {
        $table = $this->definition->getServiceProvider()->getData($entity)->getDefinition()->getTable();
$a = "a";
$ab = "ab";
$abc = "abc";
        $queryBuilder = $this->database->createQueryBuilder();
        $nameSelect   = $nameField !== null ? ',`'.$nameField.'`' : '';
        $queryBuilder
            ->select('id'.$nameSelect)
            ->from('`'.$table.'`', 't1')
            ->where('deleted_at IS NULL');
        if ($nameField) {
            $queryBuilder->orderBy($nameField);
        } else {
            $queryBuilder->orderBy('id');
        }
        $queryResult    = $queryBuilder->execute();
        $manyReferences = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
        $result         = [];
        foreach ($manyReferences as $manyReference) {
            $result[$manyReference['id']] = $nameField ? $manyReference[$nameField] : $manyReference['id'];
        }
        return $result;
    }

    /**
     * {@inheritdoc}
     */
    public function countBy($table, array $params, array $paramsOperators, $excludeDeleted) {
        $queryBuilder = $this->database->createQueryBuilder();
        $queryBuilder
            ->select('COUNT(id)')
            ->from('`'.$table.'`', '`'.$table.'`');

        $deletedExcluder = 'where';
        $i               = 0;
        foreach ($params as $name => $value) {
            if ($this->definition->getType($name) === 'many') {
                continue;
            }
            $queryBuilder
                ->andWhere('`'.$name.'`'.$paramsOperators[$name].'?')
                ->setParameter($i, $value);
            $i++;
            $deletedExcluder = 'andWhere';
        }

        if ($excludeDeleted) {
            $queryBuilder->$deletedExcluder('deleted_at IS NULL');
        }

        $queryResult = $queryBuilder->execute();
        $result      = $queryResult->fetch(\PDO::FETCH_NUM);
        return intval($result[0]);
    }

    /**
     * {@inheritdoc}
     */
    public function fetchReferences(array &$entities = null) {
        if (!$entities) {
            return;
        }
        foreach ($this->definition->getFieldNames() as $field) {
            if ($this->definition->getType($field) !== 'reference') {
                continue;
            }
            $this->fetchReferencesForField($entities, $field);
        }
    }


    /**
     * {@inheritdoc}
     */
    public function hasManySet($field, array $thatIds) {
        $thisField    = $this->definition->getManyThisField($field);
        $thatField    = $this->definition->getManyThatField($field);
        $thatEntity   = $this->definition->getManyEntity($field);
        $entityTable  = $this->definition->getServiceProvider()->getData($thatEntity)->getDefinition()->getTable();
        $queryBuilder = $this->database->createQueryBuilder();
        $queryBuilder
            ->select('t1.`'.$thisField.'` AS this, t1.`'.$thatField.'` AS that')
            ->from('`'.$field.'`', 't1')
            ->leftJoin('t1', '`'.$entityTable.'`', 't2', 't2.id = t1.`'.$thatField.'`')
            ->andWhere('t2.deleted_at IS NULL')
            ->orderBy('this, that');
        $queryResult  = $queryBuilder->execute();
        $existingMany = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
        $existingMap  = [];
        foreach ($existingMany as $existing) {
            $existingMap[$existing['this']][] = $existing['that'];
        }
        sort($thatIds);
        return in_array($thatIds, array_values($existingMap));
    }

}


1			<?php
2
3			/*
4			* This file is part of the CRUDlex package.
5			*
6			* (c) Philip Lehmann-Böhm <[email protected]>
7			*
8			* For the full copyright and license information, please view the LICENSE
9			* file that was distributed with this source code.
10			*/
11
12			namespace CRUDlex;
13
14			use Doctrine\DBAL\Query\QueryBuilder;
15			use Doctrine\DBAL\Connection;
16
17			/**
18			* MySQL Data implementation using a given Doctrine DBAL instance.
19			*/
20			class MySQLData extends AbstractData {
21
22			/**
23			* Holds the Doctrine DBAL instance.
24			*/
25			protected $database;
26
27			/**
28			* Flag whether to use UUIDs as primary key.
29			*/
30			protected $useUUIDs;
31
32
33			protected function getManyFields() {
34			$fields = $this->definition->getFieldNames(true);
35			return array_filter($fields, function($field) {
36			return $this->definition->getType($field) === 'many';
37			});
38			}
39
40			protected function getFormFields() {
41			$manyFields = $this->getManyFields();
42			$formFields = [];
43			foreach ($this->definition->getEditableFieldNames() as $field) {
44			if (!in_array($field, $manyFields)) {
45			$formFields[] = $field;
46			}
47			}
48			return $formFields;
49			}
50
51			/**
52			* Sets the values and parameters of the upcoming given query according
53			* to the entity.
54			*
55			* @param Entity $entity
56			* the entity with its fields and values
57			* @param QueryBuilder $queryBuilder
58			* the upcoming query
59			* @param string $setMethod
60			* what method to use on the QueryBuilder: 'setValue' or 'set'
61			*/
62			protected function setValuesAndParameters(Entity $entity, QueryBuilder $queryBuilder, $setMethod) {
63			$formFields = $this->getFormFields();
64			$count = count($formFields);
65			for ($i = 0; $i < $count; ++$i) {
66			$type = $this->definition->getType($formFields[$i]);
67			$value = $entity->get($formFields[$i]);
68			if ($type == 'boolean') {
69			$value = $value ? 1 : 0;
70			}
71			$queryBuilder->$setMethod('`'.$formFields[$i].'`', '?');
72			$queryBuilder->setParameter($i, $value);
73			}
74			}
75
76			/**
77			* Performs the cascading children deletion.
78			*
79			* @param integer $id
80			* the current entities id
81			* @param boolean $deleteCascade
82			* whether to delete children and sub children
83			*/
84			protected function deleteChildren($id, $deleteCascade) {
85			foreach ($this->definition->getChildren() as $childArray) {
86			$childData = $this->definition->getServiceProvider()->getData($childArray[2]);
87			$children = $childData->listEntries([$childArray[1] => $id]);
88			foreach ($children as $child) {
89			$childData->doDelete($child, $deleteCascade);
90			}
91			}
92			}
93
94			/**
95			* Checks whether the by id given entity still has children referencing it.
96			*
97			* @param integer $id
98			* the current entities id
99			*
100			* @return boolean
101			* true if the entity still has children
102			*/
103			protected function hasChildren($id) {
104			foreach ($this->definition->getChildren() as $child) {
105			$queryBuilder = $this->database->createQueryBuilder();
106			$queryBuilder
107			->select('COUNT(id)')
108			->from('`'.$child[0].'`', '`'.$child[0].'`')
109			->where('`'.$child[1].'` = ?')
110			->andWhere('deleted_at IS NULL')
111			->setParameter(0, $id);
112			$queryResult = $queryBuilder->execute();
113			$result = $queryResult->fetch(\PDO::FETCH_NUM);
114			if ($result[0] > 0) {
115			return true;
116			}
117			}
118			return false;
119			}
120
121			/**
122			* {@inheritdoc}
123			*/
124			protected function doDelete(Entity $entity, $deleteCascade) {
125			$result = $this->shouldExecuteEvents($entity, 'before', 'delete');
126			if (!$result) {
127			return static::DELETION_FAILED_EVENT;
128			}
129			$id = $entity->get('id');
130			if ($deleteCascade) {
131			$this->deleteChildren($id, $deleteCascade);
132			} elseif ($this->hasChildren($id)) {
133			return static::DELETION_FAILED_STILL_REFERENCED;
134			}
135
136			$query = $this->database->createQueryBuilder();
137			$query
138			->update('`'.$this->definition->getTable().'`')
139			->set('deleted_at', 'UTC_TIMESTAMP()')
140			->where('id = ?')
141			->setParameter(0, $id);
142
143			$query->execute();
144			$this->shouldExecuteEvents($entity, 'after', 'delete');
145			return static::DELETION_SUCCESS;
146			}
147
148			/**
149			* Adds sorting parameters to the query.
150			*
151			* @param QueryBuilder $queryBuilder
152			* the query
153			* @param $filter
154			* the filter all resulting entities must fulfill, the keys as field names
155			* @param $filterOperators
156			* the operators of the filter like "=" defining the full condition of the field
157			*/
158			protected function addFilter(QueryBuilder $queryBuilder, array $filter, array $filterOperators) {
159			$i = 0;
160			foreach ($filter as $field => $value) {
161			if ($this->definition->getType($field) === 'many') {
162			continue;
163			}
164			if ($value === null) {
165			$queryBuilder->andWhere('`'.$field.'` IS NULL');
166			} else {
167			$operator = array_key_exists($field, $filterOperators) ? $filterOperators[$field] : '=';
168			$queryBuilder
169			->andWhere('`'.$field.'` '.$operator.' ?')
170			->setParameter($i, $value);
171			}
172			$i++;
173			}
174			}
175
176			/**
177			* Adds pagination parameters to the query.
178			*
179			* @param QueryBuilder $queryBuilder
180			* the query
181			* @param integer\|null $skip
182			* the rows to skip
183			* @param integer\|null $amount
184			* the maximum amount of rows
185			*/
186			protected function addPagination(QueryBuilder $queryBuilder, $skip, $amount) {
187			$queryBuilder->setMaxResults(9999999999);
188			if ($amount !== null) {
189			$queryBuilder->setMaxResults(abs(intval($amount)));
190			}
191			if ($skip !== null) {
192			$queryBuilder->setFirstResult(abs(intval($skip)));
193			}
194			}
195
196			/**
197			* Adds sorting parameters to the query.
198			*
199			* @param QueryBuilder $queryBuilder
200			* the query
201			* @param string\|null $sortField
202			* the sort field
203			* @param boolean\|null $sortAscending
204			* true if sort ascending, false if descending
205			*/
206			protected function addSort(QueryBuilder $queryBuilder, $sortField, $sortAscending) {
207			if ($sortField !== null) {
208
209			$type = $this->definition->getType($sortField);
210			if ($type === 'many') {
211			$sortField = $this->definition->getInitialSortField();
212			}
213
214			$order = $sortAscending === true ? 'ASC' : 'DESC';
215			$queryBuilder->orderBy('`'.$sortField.'`', $order);
216			}
217			}
218
219			/**
220			* Adds the id and name of referenced entities to the given entities. The
221			* reference field is before the raw id of the referenced entity and after
222			* the fetch, it's an array with the keys id and name.
223			*
224			* @param Entity[] &$entities
225			* the entities to fetch the references for
226			* @param string $field
227			* the reference field
228			*/
229			protected function fetchReferencesForField(array &$entities, $field) {
230			$nameField = $this->definition->getReferenceNameField($field);
231			$queryBuilder = $this->database->createQueryBuilder();
232
233			$ids = array_map(function(Entity $entity) use ($field) {
234			return $entity->get($field);
235			}, $entities);
236
237			$referenceEntity = $this->definition->getReferenceEntity($field);
238			$table = $this->definition->getServiceProvider()->getData($referenceEntity)->getDefinition()->getTable();
239			$queryBuilder
240			->from('`'.$table.'`', '`'.$table.'`')
241			->where('id IN (?)')
242			->andWhere('deleted_at IS NULL');
243			if ($nameField) {
244			$queryBuilder->select('id', $nameField);
245			} else {
246			$queryBuilder->select('id');
247			}
248
249			$queryBuilder->setParameter(0, $ids, Connection::PARAM_INT_ARRAY);
250
251			$queryResult = $queryBuilder->execute();
252			$rows = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
253			$amount = count($entities);
254			foreach ($rows as $row) {
255			for ($i = 0; $i < $amount; ++$i) {
256			if ($entities[$i]->get($field) == $row['id']) {
257			$value = ['id' => $entities[$i]->get($field)];
258			if ($nameField) {
259			$value['name'] = $row[$nameField];
260			}
261			$entities[$i]->set($field, $value);
262			}
263			}
264			}
265			}
266
267			/**
268			* Generates a new UUID.
269			*
270			* @return string\|null
271			* the new UUID or null if this instance isn't configured to do so
272			*/
273			protected function generateUUID() {
274			$uuid = null;
275			if ($this->useUUIDs) {
276			$sql = 'SELECT UUID() as id';
277			$result = $this->database->fetchAssoc($sql);
278			$uuid = $result['id'];
279			}
280			return $uuid;
281			}
282
283			protected function enrichWithMany(array $rows) {
284			$manyFields = $this->getManyFields();
285			$mapping = [];
			0 ignored issues – show Coding Style introduced 2016-09-09 20:12 UTC by Report Bug Copy Issue Report Equals sign not aligned with surrounding assignments; expected 4 spaces but found 1 space This check looks for multiple assignments in successive lines of code. It will report an issue if the operators are not in a straight line. To visualize $a = "a"; $ab = "ab"; $abc = "abc"; will produce issues in the first and second line, while this second example $a = "a"; $ab = "ab"; $abc = "abc"; will produce no issues. Loading history...
286			foreach ($rows as $row) {
287			foreach ($manyFields as $manyField) {
288			$row[$manyField] = [];
289			}
290			$mapping[$row['id']] = $row;
291			}
292			foreach ($manyFields as $manyField) {
293			$queryBuilder = $this->database->createQueryBuilder();
294			$nameField = $this->definition->getManyNameField($manyField);
295			$thisField = $this->definition->getManyThisField($manyField);
296			$thatField = $this->definition->getManyThatField($manyField);
297			$entity = $this->definition->getManyEntity($manyField);
298			$entityTable = $this->definition->getServiceProvider()->getData($entity)->getDefinition()->getTable();
299			$nameSelect = $nameField !== null ? ', t2.`'.$nameField.'` AS name' : '';
300			$queryBuilder
301			->select('t1.`'.$thisField.'` AS this, t1.`'.$thatField.'` AS that'.$nameSelect)
302			->from('`'.$manyField.'`', 't1')
303			->leftJoin('t1', '`'.$entityTable.'`', 't2', 't2.id = t1.`'.$thatField.'`')
304			->where('t1.`'.$thisField.'` IN (?)')
305			->andWhere('t2.deleted_at IS NULL');
306			$queryBuilder->setParameter(0, array_keys($mapping), Connection::PARAM_INT_ARRAY);
307			$queryResult = $queryBuilder->execute();
308			$manyReferences = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
309			foreach ($manyReferences as $manyReference) {
310			$many = ['id' => $manyReference['that']];
311			if ($nameField !== null) {
312			$many['name'] = $manyReference['name'];
313			}
314			$mapping[$manyReference['this']][$manyField][] = $many;
315			}
316			}
317			return array_values($mapping);
318			}
319
320			protected function populateMany(Entity $entity) {
321			$manyFields = $this->getManyFields();
322			$id = $entity->get('id');
			0 ignored issues – show Coding Style introduced 2016-09-09 20:29 UTC by Report Bug Copy Issue Report Equals sign not aligned with surrounding assignments; expected 9 spaces but found 1 space This check looks for multiple assignments in successive lines of code. It will report an issue if the operators are not in a straight line. To visualize $a = "a"; $ab = "ab"; $abc = "abc"; will produce issues in the first and second line, while this second example $a = "a"; $ab = "ab"; $abc = "abc"; will produce no issues. Loading history...
323			foreach ($manyFields as $manyField) {
324			$thisField = $this->definition->getManyThisField($manyField);
325			$thatField = $this->definition->getManyThatField($manyField);
326			$this->database->delete($manyField, [$thisField => $id]);
327			foreach ($entity->get($manyField) as $thatId) {
328			$this->database->insert($manyField, [
329			$thisField => $id,
330			$thatField => $thatId['id']
331			]);
332			}
333			}
334			}
335
336			/**
337			* Constructor.
338			*
339			* @param EntityDefinition $definition
340			* the entity definition
341			* @param FileProcessorInterface $fileProcessor
342			* the file processor to use
343			* @param $database
344			* the Doctrine DBAL instance to use
345			* @param boolean $useUUIDs
346			* flag whether to use UUIDs as primary key
347			*/
348			public function __construct(EntityDefinition $definition, FileProcessorInterface $fileProcessor, $database, $useUUIDs) {
349			$this->definition = $definition;
350			$this->fileProcessor = $fileProcessor;
351			$this->database = $database;
352			$this->useUUIDs = $useUUIDs;
353			}
354
355			/**
356			* {@inheritdoc}
357			*/
358			public function get($id) {
359			$entities = $this->listEntries(['id' => $id]);
360			if (count($entities) == 0) {
361			return null;
362			}
363			return $entities[0];
364			}
365
366			/**
367			* {@inheritdoc}
368			*/
369			public function listEntries(array $filter = [], array $filterOperators = [], $skip = null, $amount = null, $sortField = null, $sortAscending = null) {
370			$fieldNames = $this->definition->getFieldNames();
371
372			$queryBuilder = $this->database->createQueryBuilder();
373			$table = $this->definition->getTable();
374			$queryBuilder
375			->select('`'.implode('`,`', $fieldNames).'`')
376			->from('`'.$table.'`', '`'.$table.'`')
377			->where('deleted_at IS NULL');
378
379			$this->addFilter($queryBuilder, $filter, $filterOperators);
380			$this->addPagination($queryBuilder, $skip, $amount);
381			$this->addSort($queryBuilder, $sortField, $sortAscending);
382
383			$queryResult = $queryBuilder->execute();
384			$rows = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
385			$rows = $this->enrichWithMany($rows);
386			$entities = [];
387			foreach ($rows as $row) {
388			$entities[] = $this->hydrate($row);
389			}
390			return $entities;
391			}
392
393			/**
394			* {@inheritdoc}
395			*/
396			public function create(Entity $entity) {
397
398			$result = $this->shouldExecuteEvents($entity, 'before', 'create');
399			if (!$result) {
400			return false;
401			}
402
403			$queryBuilder = $this->database->createQueryBuilder();
404			$queryBuilder
405			->insert('`'.$this->definition->getTable().'`')
406			->setValue('created_at', 'UTC_TIMESTAMP()')
407			->setValue('updated_at', 'UTC_TIMESTAMP()')
408			->setValue('version', 0);
409
410
411			$this->setValuesAndParameters($entity, $queryBuilder, 'setValue');
412
413			$id = $this->generateUUID();
414			if ($this->useUUIDs) {
415			$queryBuilder->setValue('`id`', '?');
416			$uuidI = count($this->getFormFields());
417			$queryBuilder->setParameter($uuidI, $id);
418			}
419
420			$queryBuilder->execute();
421
422			if (!$this->useUUIDs) {
423			$id = $this->database->lastInsertId();
424			}
425
426			$entity->set('id', $id);
427
428			$createdEntity = $this->get($entity->get('id'));
429			$entity->set('version', $createdEntity->get('version'));
430			$entity->set('created_at', $createdEntity->get('created_at'));
431			$entity->set('updated_at', $createdEntity->get('updated_at'));
432
433			$this->populateMany($entity);
434
435			$this->shouldExecuteEvents($entity, 'after', 'create');
436
437			return true;
438			}
439
440			/**
441			* {@inheritdoc}
442			*/
443			public function update(Entity $entity) {
444
445			$result = $this->shouldExecuteEvents($entity, 'before', 'update');
446			if (!$result) {
447			return false;
448			}
449
450			$formFields = $this->getFormFields();
451			$queryBuilder = $this->database->createQueryBuilder();
452			$queryBuilder
453			->update('`'.$this->definition->getTable().'`')
454			->set('updated_at', 'UTC_TIMESTAMP()')
455			->set('version', 'version + 1')
456			->where('id = ?')
457			->setParameter(count($formFields), $entity->get('id'));
458
459			$this->setValuesAndParameters($entity, $queryBuilder, 'set');
460			$affected = $queryBuilder->execute();
461
462			$this->populateMany($entity);
463
464			$this->shouldExecuteEvents($entity, 'after', 'update');
465
466			return $affected;
467			}
468
469			/**
470			* {@inheritdoc}
471			*/
472			public function getIdToNameMap($entity, $nameField) {
473			$table = $this->definition->getServiceProvider()->getData($entity)->getDefinition()->getTable();
			0 ignored issues – show Coding Style introduced 2016-09-09 19:38 UTC by Report Bug Copy Issue Report Equals sign not aligned with surrounding assignments; expected 8 spaces but found 1 space This check looks for multiple assignments in successive lines of code. It will report an issue if the operators are not in a straight line. To visualize $a = "a"; $ab = "ab"; $abc = "abc"; will produce issues in the first and second line, while this second example $a = "a"; $ab = "ab"; $abc = "abc"; will produce no issues. Loading history...
474			$queryBuilder = $this->database->createQueryBuilder();
475			$nameSelect = $nameField !== null ? ',`'.$nameField.'`' : '';
476			$queryBuilder
477			->select('id'.$nameSelect)
478			->from('`'.$table.'`', 't1')
479			->where('deleted_at IS NULL');
480			if ($nameField) {
481			$queryBuilder->orderBy($nameField);
482			} else {
483			$queryBuilder->orderBy('id');
484			}
485			$queryResult = $queryBuilder->execute();
486			$manyReferences = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
487			$result = [];
488			foreach ($manyReferences as $manyReference) {
489			$result[$manyReference['id']] = $nameField ? $manyReference[$nameField] : $manyReference['id'];
490			}
491			return $result;
492			}
493
494			/**
495			* {@inheritdoc}
496			*/
497			public function countBy($table, array $params, array $paramsOperators, $excludeDeleted) {
498			$queryBuilder = $this->database->createQueryBuilder();
499			$queryBuilder
500			->select('COUNT(id)')
501			->from('`'.$table.'`', '`'.$table.'`');
502
503			$deletedExcluder = 'where';
504			$i = 0;
505			foreach ($params as $name => $value) {
506			if ($this->definition->getType($name) === 'many') {
507			continue;
508			}
509			$queryBuilder
510			->andWhere('`'.$name.'`'.$paramsOperators[$name].'?')
511			->setParameter($i, $value);
512			$i++;
513			$deletedExcluder = 'andWhere';
514			}
515
516			if ($excludeDeleted) {
517			$queryBuilder->$deletedExcluder('deleted_at IS NULL');
518			}
519
520			$queryResult = $queryBuilder->execute();
521			$result = $queryResult->fetch(\PDO::FETCH_NUM);
522			return intval($result[0]);
523			}
524
525			/**
526			* {@inheritdoc}
527			*/
528			public function fetchReferences(array &$entities = null) {
529			if (!$entities) {
530			return;
531			}
532			foreach ($this->definition->getFieldNames() as $field) {
533			if ($this->definition->getType($field) !== 'reference') {
534			continue;
535			}
536			$this->fetchReferencesForField($entities, $field);
537			}
538			}
539
540
541			/**
542			* {@inheritdoc}
543			*/
544			public function hasManySet($field, array $thatIds) {
545			$thisField = $this->definition->getManyThisField($field);
546			$thatField = $this->definition->getManyThatField($field);
547			$thatEntity = $this->definition->getManyEntity($field);
548			$entityTable = $this->definition->getServiceProvider()->getData($thatEntity)->getDefinition()->getTable();
549			$queryBuilder = $this->database->createQueryBuilder();
550			$queryBuilder
551			->select('t1.`'.$thisField.'` AS this, t1.`'.$thatField.'` AS that')
552			->from('`'.$field.'`', 't1')
553			->leftJoin('t1', '`'.$entityTable.'`', 't2', 't2.id = t1.`'.$thatField.'`')
554			->andWhere('t2.deleted_at IS NULL')
555			->orderBy('this, that');
556			$queryResult = $queryBuilder->execute();
557			$existingMany = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
558			$existingMap = [];
559			foreach ($existingMany as $existing) {
560			$existingMap[$existing['this']][] = $existing['that'];
561			}
562			sort($thatIds);
563			return in_array($thatIds, array_values($existingMap));
564			}
565
566			}
567

philiplb / CRUDlex

Push — master ( 74b649...753ce2 )

MySQLData::addFilter() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like