MySQLData::enrichWithMany() - Code Metrics - Inspection of "added a first draft of the "many" datatype coverin..." - philiplb/CRUDlex - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 4e93f3...8a5e52 )

by Philip

created 2016-09-08 22:05 UTC

MySQLData::enrichWithMany() B

↳ Parent: MySQLData

Complexity

Conditions	6
Paths	14

Size

Total Lines	37
Code Lines	31

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	1

Metric	Value
c	1
b	0
f	1
dl	0
loc	37
rs	8.439
cc	6
eloc	31
nc	14
nop	1

<?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;

    /**
     * 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->definition->getEditableFieldNames();
        $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 ($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) {
            $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) {
        $fields     = $this->definition->getFieldNames(true);
        $manyFields = array_filter($fields, function($field) {
            return $this->definition->getType($field) === 'many';
        });
        $mapping = [];
        foreach ($rows as $row) {
            $mapping[$row['id']] = $row;
        }
        foreach ($manyFields as $manyField) {
            $queryBuilder = $this->database->createQueryBuilder();
            $table        = $this->definition->getManyTable($manyField);
            $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('`'.$table.'`', 't1')
                ->leftJoin('t1', '`'.$entityTable.'`', 't2', 't2.id = t1.`'.$thatField.'`')
                ->where('t1.`'.$thisField.'` IN (?)')
                ->where('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);
    }

    /**
     * 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->definition->getEditableFieldNames());
            $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->shouldExecuteEvents($entity, 'after', 'create');

        return true;
    }

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

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

        $formFields   = $this->definition->getEditableFieldNames();
        $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->shouldExecuteEvents($entity, 'after', 'update');

        return $affected;
    }

    /**
     * {@inheritdoc}
     */
    public function getReferences($referenceEntity, $nameField) {

        $table = $this->definition->getServiceProvider()->getData($referenceEntity)->getDefinition()->getTable();

        $queryBuilder = $this->database->createQueryBuilder();
        if ($nameField) {
            $queryBuilder->select('id', $nameField);
        } else {
            $queryBuilder->select('id');
        }
        $queryBuilder->from('`'.$table.'`', '`'.$table.'`')->where('deleted_at IS NULL');
        if ($nameField) {
            $queryBuilder->orderBy($nameField);
        } else {
            $queryBuilder->orderBy('id');
        }
        $queryResult = $queryBuilder->execute();
        $entries     = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
        $result      = [];
        foreach ($entries as $entry) {
            $result[$entry['id']] = $nameField ? $entry[$nameField] : $entry['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) {
            $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);
        }
    }

}


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			* Sets the values and parameters of the upcoming given query according
34			* to the entity.
35			*
36			* @param Entity $entity
37			* the entity with its fields and values
38			* @param QueryBuilder $queryBuilder
39			* the upcoming query
40			* @param string $setMethod
41			* what method to use on the QueryBuilder: 'setValue' or 'set'
42			*/
43			protected function setValuesAndParameters(Entity $entity, QueryBuilder $queryBuilder, $setMethod) {
44			$formFields = $this->definition->getEditableFieldNames();
45			$count = count($formFields);
46			for ($i = 0; $i < $count; ++$i) {
47			$type = $this->definition->getType($formFields[$i]);
48			$value = $entity->get($formFields[$i]);
49			if ($type == 'boolean') {
50			$value = $value ? 1 : 0;
51			}
52			$queryBuilder->$setMethod('`'.$formFields[$i].'`', '?');
53			$queryBuilder->setParameter($i, $value);
54			}
55			}
56
57			/**
58			* Performs the cascading children deletion.
59			*
60			* @param integer $id
61			* the current entities id
62			* @param boolean $deleteCascade
63			* whether to delete children and sub children
64			*/
65			protected function deleteChildren($id, $deleteCascade) {
66			foreach ($this->definition->getChildren() as $childArray) {
67			$childData = $this->definition->getServiceProvider()->getData($childArray[2]);
68			$children = $childData->listEntries([$childArray[1] => $id]);
69			foreach ($children as $child) {
70			$childData->doDelete($child, $deleteCascade);
71			}
72			}
73			}
74
75			/**
76			* Checks whether the by id given entity still has children referencing it.
77			*
78			* @param integer $id
79			* the current entities id
80			*
81			* @return boolean
82			* true if the entity still has children
83			*/
84			protected function hasChildren($id) {
85			foreach ($this->definition->getChildren() as $child) {
86			$queryBuilder = $this->database->createQueryBuilder();
87			$queryBuilder
88			->select('COUNT(id)')
89			->from('`'.$child[0].'`', '`'.$child[0].'`')
90			->where('`'.$child[1].'` = ?')
91			->andWhere('deleted_at IS NULL')
92			->setParameter(0, $id);
93			$queryResult = $queryBuilder->execute();
94			$result = $queryResult->fetch(\PDO::FETCH_NUM);
95			if ($result[0] > 0) {
96			return true;
97			}
98			}
99			return false;
100			}
101
102			/**
103			* {@inheritdoc}
104			*/
105			protected function doDelete(Entity $entity, $deleteCascade) {
106			$result = $this->shouldExecuteEvents($entity, 'before', 'delete');
107			if (!$result) {
108			return static::DELETION_FAILED_EVENT;
109			}
110			$id = $entity->get('id');
111			if ($deleteCascade) {
112			$this->deleteChildren($id, $deleteCascade);
113			} elseif ($this->hasChildren($id)) {
114			return static::DELETION_FAILED_STILL_REFERENCED;
115			}
116
117			$query = $this->database->createQueryBuilder();
118			$query
119			->update('`'.$this->definition->getTable().'`')
120			->set('deleted_at', 'UTC_TIMESTAMP()')
121			->where('id = ?')
122			->setParameter(0, $id);
123
124			$query->execute();
125			$this->shouldExecuteEvents($entity, 'after', 'delete');
126			return static::DELETION_SUCCESS;
127			}
128
129			/**
130			* Adds sorting parameters to the query.
131			*
132			* @param QueryBuilder $queryBuilder
133			* the query
134			* @param $filter
135			* the filter all resulting entities must fulfill, the keys as field names
136			* @param $filterOperators
137			* the operators of the filter like "=" defining the full condition of the field
138			*/
139			protected function addFilter(QueryBuilder $queryBuilder, array $filter, array $filterOperators) {
140			$i = 0;
141			foreach ($filter as $field => $value) {
142			if ($value === null) {
143			$queryBuilder->andWhere('`'.$field.'` IS NULL');
144			} else {
145			$operator = array_key_exists($field, $filterOperators) ? $filterOperators[$field] : '=';
146			$queryBuilder
147			->andWhere('`'.$field.'` '.$operator.' ?')
148			->setParameter($i, $value);
149			}
150			$i++;
151			}
152			}
153
154			/**
155			* Adds pagination parameters to the query.
156			*
157			* @param QueryBuilder $queryBuilder
158			* the query
159			* @param integer\|null $skip
160			* the rows to skip
161			* @param integer\|null $amount
162			* the maximum amount of rows
163			*/
164			protected function addPagination(QueryBuilder $queryBuilder, $skip, $amount) {
165			$queryBuilder->setMaxResults(9999999999);
166			if ($amount !== null) {
167			$queryBuilder->setMaxResults(abs(intval($amount)));
168			}
169			if ($skip !== null) {
170			$queryBuilder->setFirstResult(abs(intval($skip)));
171			}
172			}
173
174			/**
175			* Adds sorting parameters to the query.
176			*
177			* @param QueryBuilder $queryBuilder
178			* the query
179			* @param string\|null $sortField
180			* the sort field
181			* @param boolean\|null $sortAscending
182			* true if sort ascending, false if descending
183			*/
184			protected function addSort(QueryBuilder $queryBuilder, $sortField, $sortAscending) {
185			if ($sortField !== null) {
186			$order = $sortAscending === true ? 'ASC' : 'DESC';
187			$queryBuilder->orderBy('`'.$sortField.'`', $order);
188			}
189			}
190
191			/**
192			* Adds the id and name of referenced entities to the given entities. The
193			* reference field is before the raw id of the referenced entity and after
194			* the fetch, it's an array with the keys id and name.
195			*
196			* @param Entity[] &$entities
197			* the entities to fetch the references for
198			* @param string $field
199			* the reference field
200			*/
201			protected function fetchReferencesForField(array &$entities, $field) {
202			$nameField = $this->definition->getReferenceNameField($field);
203			$queryBuilder = $this->database->createQueryBuilder();
204
205			$ids = array_map(function(Entity $entity) use ($field) {
206			return $entity->get($field);
207			}, $entities);
208
209			$referenceEntity = $this->definition->getReferenceEntity($field);
210			$table = $this->definition->getServiceProvider()->getData($referenceEntity)->getDefinition()->getTable();
211			$queryBuilder
212			->from('`'.$table.'`', '`'.$table.'`')
213			->where('id IN (?)')
214			->andWhere('deleted_at IS NULL');
215			if ($nameField) {
216			$queryBuilder->select('id', $nameField);
217			} else {
218			$queryBuilder->select('id');
219			}
220
221			$queryBuilder->setParameter(0, $ids, Connection::PARAM_INT_ARRAY);
222
223			$queryResult = $queryBuilder->execute();
224			$rows = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
225			$amount = count($entities);
226			foreach ($rows as $row) {
227			for ($i = 0; $i < $amount; ++$i) {
228			if ($entities[$i]->get($field) == $row['id']) {
229			$value = ['id' => $entities[$i]->get($field)];
230			if ($nameField) {
231			$value['name'] = $row[$nameField];
232			}
233			$entities[$i]->set($field, $value);
234			}
235			}
236			}
237			}
238
239			/**
240			* Generates a new UUID.
241			*
242			* @return string\|null
243			* the new UUID or null if this instance isn't configured to do so
244			*/
245			protected function generateUUID() {
246			$uuid = null;
247			if ($this->useUUIDs) {
248			$sql = 'SELECT UUID() as id';
249			$result = $this->database->fetchAssoc($sql);
250			$uuid = $result['id'];
251			}
252			return $uuid;
253			}
254
255			protected function enrichWithMany(array $rows) {
256			$fields = $this->definition->getFieldNames(true);
257			$manyFields = array_filter($fields, function($field) {
258			return $this->definition->getType($field) === 'many';
259			});
260			$mapping = [];
261			foreach ($rows as $row) {
262			$mapping[$row['id']] = $row;
263			}
264			foreach ($manyFields as $manyField) {
265			$queryBuilder = $this->database->createQueryBuilder();
266			$table = $this->definition->getManyTable($manyField);
267			$nameField = $this->definition->getManyNameField($manyField);
268			$thisField = $this->definition->getManyThisField($manyField);
269			$thatField = $this->definition->getManyThatField($manyField);
270			$entity = $this->definition->getManyEntity($manyField);
271			$entityTable = $this->definition->getServiceProvider()->getData($entity)->getDefinition()->getTable();
272			$nameSelect = $nameField !== null ? ', t2.`'.$nameField.'` AS name' : '';
273			$queryBuilder
274			->select('t1.`'.$thisField.'` AS this, t1.`'.$thatField.'` AS that'.$nameSelect)
275			->from('`'.$table.'`', 't1')
276			->leftJoin('t1', '`'.$entityTable.'`', 't2', 't2.id = t1.`'.$thatField.'`')
277			->where('t1.`'.$thisField.'` IN (?)')
278			->where('t2.deleted_at IS NULL');
279			$queryBuilder->setParameter(0, array_keys($mapping), Connection::PARAM_INT_ARRAY);
280			$queryResult = $queryBuilder->execute();
281			$manyReferences = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
282			foreach ($manyReferences as $manyReference) {
283			$many = ['id' => $manyReference['that']];
284			if ($nameField !== null) {
285			$many['name'] = $manyReference['name'];
286			}
287			$mapping[$manyReference['this']][$manyField][] = $many;
288			}
289			}
290			return array_values($mapping);
291			}
292
293			/**
294			* Constructor.
295			*
296			* @param EntityDefinition $definition
297			* the entity definition
298			* @param FileProcessorInterface $fileProcessor
299			* the file processor to use
300			* @param $database
301			* the Doctrine DBAL instance to use
302			* @param boolean $useUUIDs
303			* flag whether to use UUIDs as primary key
304			*/
305			public function __construct(EntityDefinition $definition, FileProcessorInterface $fileProcessor, $database, $useUUIDs) {
306			$this->definition = $definition;
307			$this->fileProcessor = $fileProcessor;
308			$this->database = $database;
309			$this->useUUIDs = $useUUIDs;
310			}
311
312			/**
313			* {@inheritdoc}
314			*/
315			public function get($id) {
316			$entities = $this->listEntries(['id' => $id]);
317			if (count($entities) == 0) {
318			return null;
319			}
320			return $entities[0];
321			}
322
323			/**
324			* {@inheritdoc}
325			*/
326			public function listEntries(array $filter = [], array $filterOperators = [], $skip = null, $amount = null, $sortField = null, $sortAscending = null) {
327			$fieldNames = $this->definition->getFieldNames();
328
329			$queryBuilder = $this->database->createQueryBuilder();
330			$table = $this->definition->getTable();
331			$queryBuilder
332			->select('`'.implode('`,`', $fieldNames).'`')
333			->from('`'.$table.'`', '`'.$table.'`')
334			->where('deleted_at IS NULL');
335
336			$this->addFilter($queryBuilder, $filter, $filterOperators);
337			$this->addPagination($queryBuilder, $skip, $amount);
338			$this->addSort($queryBuilder, $sortField, $sortAscending);
339
340			$queryResult = $queryBuilder->execute();
341			$rows = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
342			$rows = $this->enrichWithMany($rows);
343			$entities = [];
344			foreach ($rows as $row) {
345			$entities[] = $this->hydrate($row);
346			}
347			return $entities;
348			}
349
350			/**
351			* {@inheritdoc}
352			*/
353			public function create(Entity $entity) {
354
355			$result = $this->shouldExecuteEvents($entity, 'before', 'create');
356			if (!$result) {
357			return false;
358			}
359
360			$queryBuilder = $this->database->createQueryBuilder();
361			$queryBuilder
362			->insert('`'.$this->definition->getTable().'`')
363			->setValue('created_at', 'UTC_TIMESTAMP()')
364			->setValue('updated_at', 'UTC_TIMESTAMP()')
365			->setValue('version', 0);
366
367
368			$this->setValuesAndParameters($entity, $queryBuilder, 'setValue');
369
370			$id = $this->generateUUID();
371			if ($this->useUUIDs) {
372			$queryBuilder->setValue('`id`', '?');
373			$uuidI = count($this->definition->getEditableFieldNames());
374			$queryBuilder->setParameter($uuidI, $id);
375			}
376
377			$queryBuilder->execute();
378
379			if (!$this->useUUIDs) {
380			$id = $this->database->lastInsertId();
381			}
382
383			$entity->set('id', $id);
384
385			$createdEntity = $this->get($entity->get('id'));
386			$entity->set('version', $createdEntity->get('version'));
387			$entity->set('created_at', $createdEntity->get('created_at'));
388			$entity->set('updated_at', $createdEntity->get('updated_at'));
389
390			$this->shouldExecuteEvents($entity, 'after', 'create');
391
392			return true;
393			}
394
395			/**
396			* {@inheritdoc}
397			*/
398			public function update(Entity $entity) {
399
400			$result = $this->shouldExecuteEvents($entity, 'before', 'update');
401			if (!$result) {
402			return false;
403			}
404
405			$formFields = $this->definition->getEditableFieldNames();
406			$queryBuilder = $this->database->createQueryBuilder();
407			$queryBuilder
408			->update('`'.$this->definition->getTable().'`')
409			->set('updated_at', 'UTC_TIMESTAMP()')
410			->set('version', 'version + 1')
411			->where('id = ?')
412			->setParameter(count($formFields), $entity->get('id'));
413
414			$this->setValuesAndParameters($entity, $queryBuilder, 'set');
415			$affected = $queryBuilder->execute();
416
417			$this->shouldExecuteEvents($entity, 'after', 'update');
418
419			return $affected;
420			}
421
422			/**
423			* {@inheritdoc}
424			*/
425			public function getReferences($referenceEntity, $nameField) {
426
427			$table = $this->definition->getServiceProvider()->getData($referenceEntity)->getDefinition()->getTable();
428
429			$queryBuilder = $this->database->createQueryBuilder();
430			if ($nameField) {
431			$queryBuilder->select('id', $nameField);
432			} else {
433			$queryBuilder->select('id');
434			}
435			$queryBuilder->from('`'.$table.'`', '`'.$table.'`')->where('deleted_at IS NULL');
436			if ($nameField) {
437			$queryBuilder->orderBy($nameField);
438			} else {
439			$queryBuilder->orderBy('id');
440			}
441			$queryResult = $queryBuilder->execute();
442			$entries = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
443			$result = [];
444			foreach ($entries as $entry) {
445			$result[$entry['id']] = $nameField ? $entry[$nameField] : $entry['id'];
446			}
447			return $result;
448			}
449
450			/**
451			* {@inheritdoc}
452			*/
453			public function countBy($table, array $params, array $paramsOperators, $excludeDeleted) {
454			$queryBuilder = $this->database->createQueryBuilder();
455			$queryBuilder
456			->select('COUNT(id)')
457			->from('`'.$table.'`', '`'.$table.'`');
458
459			$deletedExcluder = 'where';
460			$i = 0;
461			foreach ($params as $name => $value) {
462			$queryBuilder
463			->andWhere('`'.$name.'`'.$paramsOperators[$name].'?')
464			->setParameter($i, $value);
465			$i++;
466			$deletedExcluder = 'andWhere';
467			}
468
469			if ($excludeDeleted) {
470			$queryBuilder->$deletedExcluder('deleted_at IS NULL');
471			}
472
473			$queryResult = $queryBuilder->execute();
474			$result = $queryResult->fetch(\PDO::FETCH_NUM);
475			return intval($result[0]);
476			}
477
478			/**
479			* {@inheritdoc}
480			*/
481			public function fetchReferences(array &$entities = null) {
482			if (!$entities) {
483			return;
484			}
485			foreach ($this->definition->getFieldNames() as $field) {
486			if ($this->definition->getType($field) !== 'reference') {
487			continue;
488			}
489			$this->fetchReferencesForField($entities, $field);
490			}
491			}
492
493			}
494

philiplb / CRUDlex

Push — master ( 4e93f3...8a5e52 )

MySQLData::enrichWithMany() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like