MySQLData::enrichWithMany() - Code Metrics - Inspection of "CS and small complexity reduction" - philiplb/CRUDlex - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( d5532b...2889a5 )

by Philip

created 2016-09-16 23:11 UTC

MySQLData::enrichWithMany() A

↳ Parent: MySQLData

Complexity

Conditions	4
Paths	6

Size

Total Lines	14
Code Lines	10

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	14
rs	9.2
c	0
b	0
f	0
cc	4
eloc	10
nc	6
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\Connection;
use Doctrine\DBAL\Query\QueryBuilder;

/**
 * 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;

    /**
     * Gets the many-to-many fields.
     *
     * @return array|\string[]
     * the many-to-many fields
     */
    protected function getManyFields() {
        $fields = $this->definition->getFieldNames(true);
        return array_filter($fields, function($field) {
            return $this->definition->getType($field) === 'many';
        });
    }

    /**
     * Gets all form fields including the many-to-many-ones.
     *
     * @return array
     * all form fields
     */
    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;
    }

    /**
     * Gets all possible many-to-many ids existing for this definition.
     *
     * @param array $fields
     * the many field names to fetch for
     * @param $params
     * the parameters the possible many field values to fetch for
     * @return array
     * an array of this many-to-many ids
     */
    protected function getManyIds(array $fields, array $params) {
        $manyIds = [];
        foreach ($fields as $field) {
            $thisField    = $this->definition->getSubTypeField($field, 'many', 'thisField');
            $thatField    = $this->definition->getSubTypeField($field, 'many', 'thatField');
            $queryBuilder = $this->database->createQueryBuilder();
            $queryBuilder
                ->select('`'.$thisField.'`')
                ->from($field)
                ->where('`'.$thatField.'` IN (?)')
                ->setParameter(0, array_column($params[$field], 'id'), Connection::PARAM_STR_ARRAY)
                ->groupBy('`'.$thisField.'`')
            ;
            $queryResult = $queryBuilder->execute();
            $manyResults = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
            foreach ($manyResults as $manyResult) {
                if (!in_array($manyResult[$thisField], $manyIds)) {
                    $manyIds[] = $manyResult[$thisField];
                }
            }
        }
        return $manyIds;
    }

    /**
     * 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;
        $manyFields = [];
        foreach ($filter as $field => $value) {
            if ($this->definition->getType($field) === 'many') {
                $manyFields[] = $field;
                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, \PDO::PARAM_STR);
            }
            $i++;
        }
        $idsToInclude = $this->getManyIds($manyFields, $filter);
        if (!empty($idsToInclude)) {
            $queryBuilder
                ->andWhere('id IN (?)')
                ->setParameter($i, $idsToInclude, Connection::PARAM_STR_ARRAY)
            ;
        }
    }

    /**
     * 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->getSubTypeField($field, 'reference', 'nameField');
        $queryBuilder = $this->database->createQueryBuilder();

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

        $referenceEntity = $this->definition->getSubTypeField($field, 'reference', 'entity');
        $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_STR_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;
    }

    /**
     * Enriches the given mapping of entity id to raw entity data with some many-to-many data.
     *
     * @param array $idToData
     * a reference to the map entity id to raw entity data
     * @param $manyField
     * the many field to enrich data with
     */
    protected function enrichWithManyField(&$idToData, $manyField) {
        $queryBuilder = $this->database->createQueryBuilder();
        $nameField    = $this->definition->getSubTypeField($manyField, 'many', 'nameField');
        $thisField    = $this->definition->getSubTypeField($manyField, 'many', 'thisField');
        $thatField    = $this->definition->getSubTypeField($manyField, 'many', 'thatField');
        $entity       = $this->definition->getSubTypeField($manyField, 'many', 'entity');
        $entityTable  = $this->definition->getServiceProvider()->getData($entity)->getDefinition()->getTable();
        $nameSelect   = $nameField !== null ? ', t2.`'.$nameField.'` AS name' : '';
        $queryBuilder
            ->select('t1.`'.$thisField.'` AS this, t1.`'.$thatField.'` AS id'.$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($idToData), Connection::PARAM_STR_ARRAY);
        $queryResult    = $queryBuilder->execute();
        $manyReferences = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($manyReferences as $manyReference) {
            $entityId = $manyReference['this'];
            unset($manyReference['this']);
            $idToData[$entityId][$manyField][] = $manyReference;
        }
    }

    /**
     * Fetches to the rows belonging many-to-many entries and adds them to the rows.
     *
     * @param array $rows
     * the rows to enrich
     * @return array
     * the enriched rows
     */
    protected function enrichWithMany(array $rows) {
        $manyFields = $this->getManyFields();
        $idToData   = [];
        foreach ($rows as $row) {
            foreach ($manyFields as $manyField) {
                $row[$manyField] = [];
            }
            $idToData[$row['id']] = $row;
        }
        foreach ($manyFields as $manyField) {
            $this->enrichWithManyField($idToData, $manyField);
        }
        return array_values($idToData);
    }

    /**
     * First, deletes all to the given entity related many-to-many entries from the DB
     * and then writes them again.
     *
     * @param Entity $entity
     * the entity to save the many-to-many entries of
     */
    protected function saveMany(Entity $entity) {
        $manyFields = $this->getManyFields();
        $id         = $entity->get('id');
        foreach ($manyFields as $manyField) {
            $thisField = $this->definition->getSubTypeField($manyField, 'many', 'thisField');
            $thatField = $this->definition->getSubTypeField($manyField, 'many', 'thatField');
            $this->database->delete($manyField, [$thisField => $id]);
            $manyValues = $entity->get($manyField) ?: [];
            foreach ($manyValues 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();
        }

        $this->enrichEntityWithMetaData($id, $entity);

        $this->saveMany($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->saveMany($entity);

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

        return $affected;
    }

    /**
     * {@inheritdoc}
     */
    public function getIdToNameMap($entity, $nameField) {
        $nameSelect = '';
        $order      = 'id';
        $getValue   = function($manyReference) {
            return $manyReference['id'];
        };
        if ($nameField !== null) {
            $nameSelect = ',`'.$nameField.'`';
            $order      = $nameField;
            $getValue   = function($manyReference) use ($nameField) {
                return $manyReference[$nameField];
            };
        }

        $table        = $this->definition->getServiceProvider()->getData($entity)->getDefinition()->getTable();
        $queryBuilder = $this->database->createQueryBuilder();
        $queryBuilder
            ->select('id'.$nameSelect)
            ->from('`'.$table.'`', 't1')
            ->where('deleted_at IS NULL')
            ->orderBy($order)
        ;
        $queryResult    = $queryBuilder->execute();
        $manyReferences = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
        $result         = array_reduce($manyReferences, function(&$carry, $manyReference) use ($getValue) {
            $carry[$manyReference['id']] = $getValue($manyReference);
            return $carry;
        }, []);
        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;
        $manyFields      = [];
        foreach ($params as $name => $value) {
            if ($this->definition->getType($name) === 'many') {
                $manyFields[] = $name;
                continue;
            }
            $queryBuilder
                ->andWhere('`'.$name.'` '.$paramsOperators[$name].' ?')
                ->setParameter($i, $value, \PDO::PARAM_STR)
            ;
            $i++;
            $deletedExcluder = 'andWhere';
        }

        $idsToInclude = $this->getManyIds($manyFields, $params);
        if (!empty($idsToInclude)) {
            $queryBuilder
                ->andWhere('id IN (?)')
                ->setParameter($i, $idsToInclude, Connection::PARAM_STR_ARRAY)
            ;
            $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, $excludeId = null) {
        $thisField    = $this->definition->getSubTypeField($field, 'many', 'thisField');
        $thatField    = $this->definition->getSubTypeField($field, 'many', 'thatField');
        $thatEntity   = $this->definition->getSubTypeField($field, 'many', 'entity');
        $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');
        if ($excludeId) {
''   == false // true
''   == null  // true
'ab' == false // false
'ab' == null  // false

// It is often better to use strict comparison
'' === false // false
'' === null  // false
            $queryBuilder
                ->andWhere('t1.`'.$thisField.'` != ?')
                ->setParameter(0, $excludeId)
            ;
        }
        $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\Connection;
15			use Doctrine\DBAL\Query\QueryBuilder;
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			* Gets the many-to-many fields.
34			*
35			* @return array\|\string[]
36			* the many-to-many fields
37			*/
38			protected function getManyFields() {
39			$fields = $this->definition->getFieldNames(true);
40			return array_filter($fields, function($field) {
41			return $this->definition->getType($field) === 'many';
42			});
43			}
44
45			/**
46			* Gets all form fields including the many-to-many-ones.
47			*
48			* @return array
49			* all form fields
50			*/
51			protected function getFormFields() {
52			$manyFields = $this->getManyFields();
53			$formFields = [];
54			foreach ($this->definition->getEditableFieldNames() as $field) {
55			if (!in_array($field, $manyFields)) {
56			$formFields[] = $field;
57			}
58			}
59			return $formFields;
60			}
61
62			/**
63			* Sets the values and parameters of the upcoming given query according
64			* to the entity.
65			*
66			* @param Entity $entity
67			* the entity with its fields and values
68			* @param QueryBuilder $queryBuilder
69			* the upcoming query
70			* @param string $setMethod
71			* what method to use on the QueryBuilder: 'setValue' or 'set'
72			*/
73			protected function setValuesAndParameters(Entity $entity, QueryBuilder $queryBuilder, $setMethod) {
74			$formFields = $this->getFormFields();
75			$count = count($formFields);
76			for ($i = 0; $i < $count; ++$i) {
77			$type = $this->definition->getType($formFields[$i]);
78			$value = $entity->get($formFields[$i]);
79			if ($type == 'boolean') {
80			$value = $value ? 1 : 0;
81			}
82			$queryBuilder->$setMethod('`'.$formFields[$i].'`', '?');
83			$queryBuilder->setParameter($i, $value);
84			}
85			}
86
87			/**
88			* Performs the cascading children deletion.
89			*
90			* @param integer $id
91			* the current entities id
92			* @param boolean $deleteCascade
93			* whether to delete children and sub children
94			*/
95			protected function deleteChildren($id, $deleteCascade) {
96			foreach ($this->definition->getChildren() as $childArray) {
97			$childData = $this->definition->getServiceProvider()->getData($childArray[2]);
98			$children = $childData->listEntries([$childArray[1] => $id]);
99			foreach ($children as $child) {
100			$childData->doDelete($child, $deleteCascade);
101			}
102			}
103			}
104
105			/**
106			* Checks whether the by id given entity still has children referencing it.
107			*
108			* @param integer $id
109			* the current entities id
110			*
111			* @return boolean
112			* true if the entity still has children
113			*/
114			protected function hasChildren($id) {
115			foreach ($this->definition->getChildren() as $child) {
116			$queryBuilder = $this->database->createQueryBuilder();
117			$queryBuilder
118			->select('COUNT(id)')
119			->from('`'.$child[0].'`', '`'.$child[0].'`')
120			->where('`'.$child[1].'` = ?')
121			->andWhere('deleted_at IS NULL')
122			->setParameter(0, $id);
123			$queryResult = $queryBuilder->execute();
124			$result = $queryResult->fetch(\PDO::FETCH_NUM);
125			if ($result[0] > 0) {
126			return true;
127			}
128			}
129			return false;
130			}
131
132			/**
133			* {@inheritdoc}
134			*/
135			protected function doDelete(Entity $entity, $deleteCascade) {
136			$result = $this->shouldExecuteEvents($entity, 'before', 'delete');
137			if (!$result) {
138			return static::DELETION_FAILED_EVENT;
139			}
140			$id = $entity->get('id');
141			if ($deleteCascade) {
142			$this->deleteChildren($id, $deleteCascade);
143			} elseif ($this->hasChildren($id)) {
144			return static::DELETION_FAILED_STILL_REFERENCED;
145			}
146
147			$query = $this->database->createQueryBuilder();
148			$query
149			->update('`'.$this->definition->getTable().'`')
150			->set('deleted_at', 'UTC_TIMESTAMP()')
151			->where('id = ?')
152			->setParameter(0, $id);
153
154			$query->execute();
155			$this->shouldExecuteEvents($entity, 'after', 'delete');
156			return static::DELETION_SUCCESS;
157			}
158
159			/**
160			* Gets all possible many-to-many ids existing for this definition.
161			*
162			* @param array $fields
163			* the many field names to fetch for
164			* @param $params
165			* the parameters the possible many field values to fetch for
166			* @return array
167			* an array of this many-to-many ids
168			*/
169			protected function getManyIds(array $fields, array $params) {
170			$manyIds = [];
171			foreach ($fields as $field) {
172			$thisField = $this->definition->getSubTypeField($field, 'many', 'thisField');
173			$thatField = $this->definition->getSubTypeField($field, 'many', 'thatField');
174			$queryBuilder = $this->database->createQueryBuilder();
175			$queryBuilder
176			->select('`'.$thisField.'`')
177			->from($field)
178			->where('`'.$thatField.'` IN (?)')
179			->setParameter(0, array_column($params[$field], 'id'), Connection::PARAM_STR_ARRAY)
180			->groupBy('`'.$thisField.'`')
181			;
182			$queryResult = $queryBuilder->execute();
183			$manyResults = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
184			foreach ($manyResults as $manyResult) {
185			if (!in_array($manyResult[$thisField], $manyIds)) {
186			$manyIds[] = $manyResult[$thisField];
187			}
188			}
189			}
190			return $manyIds;
191			}
192
193			/**
194			* Adds sorting parameters to the query.
195			*
196			* @param QueryBuilder $queryBuilder
197			* the query
198			* @param $filter
199			* the filter all resulting entities must fulfill, the keys as field names
200			* @param $filterOperators
201			* the operators of the filter like "=" defining the full condition of the field
202			*/
203			protected function addFilter(QueryBuilder $queryBuilder, array $filter, array $filterOperators) {
204			$i = 0;
205			$manyFields = [];
206			foreach ($filter as $field => $value) {
207			if ($this->definition->getType($field) === 'many') {
208			$manyFields[] = $field;
209			continue;
210			}
211			if ($value === null) {
212			$queryBuilder->andWhere('`'.$field.'` IS NULL');
213			} else {
214			$operator = array_key_exists($field, $filterOperators) ? $filterOperators[$field] : '=';
215			$queryBuilder
216			->andWhere('`'.$field.'` '.$operator.' ?')
217			->setParameter($i, $value, \PDO::PARAM_STR);
218			}
219			$i++;
220			}
221			$idsToInclude = $this->getManyIds($manyFields, $filter);
222			if (!empty($idsToInclude)) {
223			$queryBuilder
224			->andWhere('id IN (?)')
225			->setParameter($i, $idsToInclude, Connection::PARAM_STR_ARRAY)
226			;
227			}
228			}
229
230			/**
231			* Adds pagination parameters to the query.
232			*
233			* @param QueryBuilder $queryBuilder
234			* the query
235			* @param integer\|null $skip
236			* the rows to skip
237			* @param integer\|null $amount
238			* the maximum amount of rows
239			*/
240			protected function addPagination(QueryBuilder $queryBuilder, $skip, $amount) {
241			$queryBuilder->setMaxResults(9999999999);
242			if ($amount !== null) {
243			$queryBuilder->setMaxResults(abs(intval($amount)));
244			}
245			if ($skip !== null) {
246			$queryBuilder->setFirstResult(abs(intval($skip)));
247			}
248			}
249
250			/**
251			* Adds sorting parameters to the query.
252			*
253			* @param QueryBuilder $queryBuilder
254			* the query
255			* @param string\|null $sortField
256			* the sort field
257			* @param boolean\|null $sortAscending
258			* true if sort ascending, false if descending
259			*/
260			protected function addSort(QueryBuilder $queryBuilder, $sortField, $sortAscending) {
261			if ($sortField !== null) {
262
263			$type = $this->definition->getType($sortField);
264			if ($type === 'many') {
265			$sortField = $this->definition->getInitialSortField();
266			}
267
268			$order = $sortAscending === true ? 'ASC' : 'DESC';
269			$queryBuilder->orderBy('`'.$sortField.'`', $order);
270			}
271			}
272
273			/**
274			* Adds the id and name of referenced entities to the given entities. The
275			* reference field is before the raw id of the referenced entity and after
276			* the fetch, it's an array with the keys id and name.
277			*
278			* @param Entity[] &$entities
279			* the entities to fetch the references for
280			* @param string $field
281			* the reference field
282			*/
283			protected function fetchReferencesForField(array &$entities, $field) {
284			$nameField = $this->definition->getSubTypeField($field, 'reference', 'nameField');
285			$queryBuilder = $this->database->createQueryBuilder();
286
287			$ids = array_map(function(Entity $entity) use ($field) {
288			return $entity->get($field);
289			}, $entities);
290
291			$referenceEntity = $this->definition->getSubTypeField($field, 'reference', 'entity');
292			$table = $this->definition->getServiceProvider()->getData($referenceEntity)->getDefinition()->getTable();
293			$queryBuilder
294			->from('`'.$table.'`', '`'.$table.'`')
295			->where('id IN (?)')
296			->andWhere('deleted_at IS NULL');
297			if ($nameField) {
298			$queryBuilder->select('id', $nameField);
299			} else {
300			$queryBuilder->select('id');
301			}
302
303			$queryBuilder->setParameter(0, $ids, Connection::PARAM_STR_ARRAY);
304
305			$queryResult = $queryBuilder->execute();
306			$rows = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
307			$amount = count($entities);
308			foreach ($rows as $row) {
309			for ($i = 0; $i < $amount; ++$i) {
310			if ($entities[$i]->get($field) == $row['id']) {
311			$value = ['id' => $entities[$i]->get($field)];
312			if ($nameField) {
313			$value['name'] = $row[$nameField];
314			}
315			$entities[$i]->set($field, $value);
316			}
317			}
318			}
319			}
320
321			/**
322			* Generates a new UUID.
323			*
324			* @return string\|null
325			* the new UUID or null if this instance isn't configured to do so
326			*/
327			protected function generateUUID() {
328			$uuid = null;
329			if ($this->useUUIDs) {
330			$sql = 'SELECT UUID() as id';
331			$result = $this->database->fetchAssoc($sql);
332			$uuid = $result['id'];
333			}
334			return $uuid;
335			}
336
337			/**
338			* Enriches the given mapping of entity id to raw entity data with some many-to-many data.
339			*
340			* @param array $idToData
341			* a reference to the map entity id to raw entity data
342			* @param $manyField
343			* the many field to enrich data with
344			*/
345			protected function enrichWithManyField(&$idToData, $manyField) {
346			$queryBuilder = $this->database->createQueryBuilder();
347			$nameField = $this->definition->getSubTypeField($manyField, 'many', 'nameField');
348			$thisField = $this->definition->getSubTypeField($manyField, 'many', 'thisField');
349			$thatField = $this->definition->getSubTypeField($manyField, 'many', 'thatField');
350			$entity = $this->definition->getSubTypeField($manyField, 'many', 'entity');
351			$entityTable = $this->definition->getServiceProvider()->getData($entity)->getDefinition()->getTable();
352			$nameSelect = $nameField !== null ? ', t2.`'.$nameField.'` AS name' : '';
353			$queryBuilder
354			->select('t1.`'.$thisField.'` AS this, t1.`'.$thatField.'` AS id'.$nameSelect)
355			->from('`'.$manyField.'`', 't1')
356			->leftJoin('t1', '`'.$entityTable.'`', 't2', 't2.id = t1.`'.$thatField.'`')
357			->where('t1.`'.$thisField.'` IN (?)')
358			->andWhere('t2.deleted_at IS NULL');
359			$queryBuilder->setParameter(0, array_keys($idToData), Connection::PARAM_STR_ARRAY);
360			$queryResult = $queryBuilder->execute();
361			$manyReferences = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
362			foreach ($manyReferences as $manyReference) {
363			$entityId = $manyReference['this'];
364			unset($manyReference['this']);
365			$idToData[$entityId][$manyField][] = $manyReference;
366			}
367			}
368
369			/**
370			* Fetches to the rows belonging many-to-many entries and adds them to the rows.
371			*
372			* @param array $rows
373			* the rows to enrich
374			* @return array
375			* the enriched rows
376			*/
377			protected function enrichWithMany(array $rows) {
378			$manyFields = $this->getManyFields();
379			$idToData = [];
380			foreach ($rows as $row) {
381			foreach ($manyFields as $manyField) {
382			$row[$manyField] = [];
383			}
384			$idToData[$row['id']] = $row;
385			}
386			foreach ($manyFields as $manyField) {
387			$this->enrichWithManyField($idToData, $manyField);
388			}
389			return array_values($idToData);
390			}
391
392			/**
393			* First, deletes all to the given entity related many-to-many entries from the DB
394			* and then writes them again.
395			*
396			* @param Entity $entity
397			* the entity to save the many-to-many entries of
398			*/
399			protected function saveMany(Entity $entity) {
400			$manyFields = $this->getManyFields();
401			$id = $entity->get('id');
402			foreach ($manyFields as $manyField) {
403			$thisField = $this->definition->getSubTypeField($manyField, 'many', 'thisField');
404			$thatField = $this->definition->getSubTypeField($manyField, 'many', 'thatField');
405			$this->database->delete($manyField, [$thisField => $id]);
406			$manyValues = $entity->get($manyField) ?: [];
407			foreach ($manyValues as $thatId) {
408			$this->database->insert($manyField, [
409			$thisField => $id,
410			$thatField => $thatId['id']
411			]);
412			}
413			}
414			}
415
416			/**
417			* Constructor.
418			*
419			* @param EntityDefinition $definition
420			* the entity definition
421			* @param FileProcessorInterface $fileProcessor
422			* the file processor to use
423			* @param $database
424			* the Doctrine DBAL instance to use
425			* @param boolean $useUUIDs
426			* flag whether to use UUIDs as primary key
427			*/
428			public function __construct(EntityDefinition $definition, FileProcessorInterface $fileProcessor, $database, $useUUIDs) {
429			$this->definition = $definition;
430			$this->fileProcessor = $fileProcessor;
431			$this->database = $database;
432			$this->useUUIDs = $useUUIDs;
433			}
434
435			/**
436			* {@inheritdoc}
437			*/
438			public function get($id) {
439			$entities = $this->listEntries(['id' => $id]);
440			if (count($entities) == 0) {
441			return null;
442			}
443			return $entities[0];
444			}
445
446			/**
447			* {@inheritdoc}
448			*/
449			public function listEntries(array $filter = [], array $filterOperators = [], $skip = null, $amount = null, $sortField = null, $sortAscending = null) {
450			$fieldNames = $this->definition->getFieldNames();
451
452			$queryBuilder = $this->database->createQueryBuilder();
453			$table = $this->definition->getTable();
454			$queryBuilder
455			->select('`'.implode('`,`', $fieldNames).'`')
456			->from('`'.$table.'`', '`'.$table.'`')
457			->where('deleted_at IS NULL');
458
459			$this->addFilter($queryBuilder, $filter, $filterOperators);
460			$this->addPagination($queryBuilder, $skip, $amount);
461			$this->addSort($queryBuilder, $sortField, $sortAscending);
462
463			$queryResult = $queryBuilder->execute();
464			$rows = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
465			$rows = $this->enrichWithMany($rows);
466			$entities = [];
467			foreach ($rows as $row) {
468			$entities[] = $this->hydrate($row);
469			}
470			return $entities;
471			}
472
473			/**
474			* {@inheritdoc}
475			*/
476			public function create(Entity $entity) {
477
478			$result = $this->shouldExecuteEvents($entity, 'before', 'create');
479			if (!$result) {
480			return false;
481			}
482
483			$queryBuilder = $this->database->createQueryBuilder();
484			$queryBuilder
485			->insert('`'.$this->definition->getTable().'`')
486			->setValue('created_at', 'UTC_TIMESTAMP()')
487			->setValue('updated_at', 'UTC_TIMESTAMP()')
488			->setValue('version', 0);
489
490
491			$this->setValuesAndParameters($entity, $queryBuilder, 'setValue');
492
493			$id = $this->generateUUID();
494			if ($this->useUUIDs) {
495			$queryBuilder->setValue('`id`', '?');
496			$uuidI = count($this->getFormFields());
497			$queryBuilder->setParameter($uuidI, $id);
498			}
499
500			$queryBuilder->execute();
501
502			if (!$this->useUUIDs) {
503			$id = $this->database->lastInsertId();
504			}
505
506			$this->enrichEntityWithMetaData($id, $entity);
507
508			$this->saveMany($entity);
509
510			$this->shouldExecuteEvents($entity, 'after', 'create');
511
512			return true;
513			}
514
515			/**
516			* {@inheritdoc}
517			*/
518			public function update(Entity $entity) {
519
520			$result = $this->shouldExecuteEvents($entity, 'before', 'update');
521			if (!$result) {
522			return false;
523			}
524
525			$formFields = $this->getFormFields();
526			$queryBuilder = $this->database->createQueryBuilder();
527			$queryBuilder
528			->update('`'.$this->definition->getTable().'`')
529			->set('updated_at', 'UTC_TIMESTAMP()')
530			->set('version', 'version + 1')
531			->where('id = ?')
532			->setParameter(count($formFields), $entity->get('id'));
533
534			$this->setValuesAndParameters($entity, $queryBuilder, 'set');
535			$affected = $queryBuilder->execute();
536
537			$this->saveMany($entity);
538
539			$this->shouldExecuteEvents($entity, 'after', 'update');
540
541			return $affected;
542			}
543
544			/**
545			* {@inheritdoc}
546			*/
547			public function getIdToNameMap($entity, $nameField) {
548			$nameSelect = '';
549			$order = 'id';
550			$getValue = function($manyReference) {
551			return $manyReference['id'];
552			};
553			if ($nameField !== null) {
554			$nameSelect = ',`'.$nameField.'`';
555			$order = $nameField;
556			$getValue = function($manyReference) use ($nameField) {
557			return $manyReference[$nameField];
558			};
559			}
560
561			$table = $this->definition->getServiceProvider()->getData($entity)->getDefinition()->getTable();
562			$queryBuilder = $this->database->createQueryBuilder();
563			$queryBuilder
564			->select('id'.$nameSelect)
565			->from('`'.$table.'`', 't1')
566			->where('deleted_at IS NULL')
567			->orderBy($order)
568			;
569			$queryResult = $queryBuilder->execute();
570			$manyReferences = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
571			$result = array_reduce($manyReferences, function(&$carry, $manyReference) use ($getValue) {
572			$carry[$manyReference['id']] = $getValue($manyReference);
573			return $carry;
574			}, []);
575			return $result;
576			}
577
578			/**
579			* {@inheritdoc}
580			*/
581			public function countBy($table, array $params, array $paramsOperators, $excludeDeleted) {
582			$queryBuilder = $this->database->createQueryBuilder();
583			$queryBuilder
584			->select('COUNT(id)')
585			->from('`'.$table.'`', '`'.$table.'`')
586			;
587
588			$deletedExcluder = 'where';
589			$i = 0;
590			$manyFields = [];
591			foreach ($params as $name => $value) {
592			if ($this->definition->getType($name) === 'many') {
593			$manyFields[] = $name;
594			continue;
595			}
596			$queryBuilder
597			->andWhere('`'.$name.'` '.$paramsOperators[$name].' ?')
598			->setParameter($i, $value, \PDO::PARAM_STR)
599			;
600			$i++;
601			$deletedExcluder = 'andWhere';
602			}
603
604			$idsToInclude = $this->getManyIds($manyFields, $params);
605			if (!empty($idsToInclude)) {
606			$queryBuilder
607			->andWhere('id IN (?)')
608			->setParameter($i, $idsToInclude, Connection::PARAM_STR_ARRAY)
609			;
610			$deletedExcluder = 'andWhere';
611			}
612
613			if ($excludeDeleted) {
614			$queryBuilder->$deletedExcluder('deleted_at IS NULL');
615			}
616
617			$queryResult = $queryBuilder->execute();
618			$result = $queryResult->fetch(\PDO::FETCH_NUM);
619			return intval($result[0]);
620			}
621
622			/**
623			* {@inheritdoc}
624			*/
625			public function fetchReferences(array &$entities = null) {
626			if (!$entities) {
627			return;
628			}
629			foreach ($this->definition->getFieldNames() as $field) {
630			if ($this->definition->getType($field) !== 'reference') {
631			continue;
632			}
633			$this->fetchReferencesForField($entities, $field);
634			}
635			}
636
637
638			/**
639			* {@inheritdoc}
640			*/
641			public function hasManySet($field, array $thatIds, $excludeId = null) {
642			$thisField = $this->definition->getSubTypeField($field, 'many', 'thisField');
643			$thatField = $this->definition->getSubTypeField($field, 'many', 'thatField');
644			$thatEntity = $this->definition->getSubTypeField($field, 'many', 'entity');
645			$entityTable = $this->definition->getServiceProvider()->getData($thatEntity)->getDefinition()->getTable();
646			$queryBuilder = $this->database->createQueryBuilder();
647			$queryBuilder
648			->select('t1.`'.$thisField.'` AS this, t1.`'.$thatField.'` AS that')
649			->from('`'.$field.'`', 't1')
650			->leftJoin('t1', '`'.$entityTable.'`', 't2', 't2.id = t1.`'.$thatField.'`')
651			->andWhere('t2.deleted_at IS NULL')
652			->orderBy('this, that');
653			if ($excludeId) {
			0 ignored issues – show Bug Best Practice introduced 2016-09-12 11:53 UTC by Report Bug Copy Issue Report The expression `$excludeId` of type `string\|null` is loosely compared to `true`; this is ambiguous if the string can be empty. You might want to explicitly use `!== null` instead. In PHP, under loose comparison (like `==`, or `!=`, or `switch` conditions), values of different types might be equal. For `string` values, the empty string `''` is a special case, in particular the following results might be unexpected: '' == false // true '' == null // true 'ab' == false // false 'ab' == null // false // It is often better to use strict comparison '' === false // false '' === null // false Loading history...
654			$queryBuilder
655			->andWhere('t1.`'.$thisField.'` != ?')
656			->setParameter(0, $excludeId)
657			;
658			}
659			$queryResult = $queryBuilder->execute();
660			$existingMany = $queryResult->fetchAll(\PDO::FETCH_ASSOC);
661			$existingMap = [];
662			foreach ($existingMany as $existing) {
663			$existingMap[$existing['this']][] = $existing['that'];
664			}
665			sort($thatIds);
666			return in_array($thatIds, array_values($existingMap));
667			}
668
669			}
670

philiplb / CRUDlex

Push — master ( d5532b...2889a5 )

MySQLData::enrichWithMany() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like