MySQLData::doDelete() - Code Metrics - Inspection of "follow the PSR-2 standard" - philiplb/CRUDlex - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( a99c75...28b121 )

by Philip

created 2017-06-30 15:18 UTC

MySQLData::doDelete() B

↳ Parent: MySQLData

Complexity

Conditions	4
Paths	4

Size

Total Lines	22
Code Lines	16

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	15
CRAP Score	4

Importance

Changes

Metric	Value
dl	0
loc	22
ccs	15
cts	15
cp	1
rs	8.9197
c	0
b	0
f	0
cc	4
eloc	16
nc	4
nop	2
crap	4

<?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;
use League\Flysystem\FilesystemInterface;

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

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

    /**
     * Flag whether to use UUIDs as primary key.
     * @var bool
     */
    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->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;
            }
            if ($type == 'reference' && is_array($value)) {
                $value = $value['id'];
            }
            $queryBuilder->$setMethod('`'.$formFields[$i].'`', '?');
            $queryBuilder->setParameter($i, $value);
        }
    }

    /**
     * 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)
    {
        $id = $entity->get('id');
        if ($deleteCascade) {
            $result = $this->deleteChildren($id, $deleteCascade);
            if ($result !== static::DELETION_SUCCESS) {
                return $result;
            }
        } 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();
        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);
            $manyIds     = array_merge($manyIds, array_column($manyResults, $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 = $this->getReferenceIds($entities, $field);

        $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']
                ]);
            }
        }
    }

    /**
     * Adds the id and name of referenced entities to the given entities. Each
     * 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
     *
     * @return void
     */
    protected function enrichWithReference(array &$entities)
    {
        if (empty($entities)) {
            return;
        }
        foreach ($this->definition->getFieldNames() as $field) {
            if ($this->definition->getType($field) !== 'reference') {
                continue;
            }
            $this->fetchReferencesForField($entities, $field);
        }
    }

    /**
     * {@inheritdoc}
     */
    protected function doCreate(Entity $entity)
    {

        $queryBuilder = $this->database->createQueryBuilder();
        $queryBuilder
            ->insert('`'.$this->definition->getTable().'`')
            ->setValue('created_at', 'UTC_TIMESTAMP()')
            ->setValue('updated_at', 'UTC_TIMESTAMP()');
        if ($this->definition->hasOptimisticLocking()) {
            $queryBuilder->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);
        $entities = [$entity];
        $this->enrichWithReference($entities);

        return true;
    }

    /**
     * {@inheritdoc}
     */
    protected function doUpdate(Entity $entity)
    {
        $queryBuilder = $this->database->createQueryBuilder();
        $queryBuilder->update('`'.$this->definition->getTable().'`')
            ->set('updated_at', 'UTC_TIMESTAMP()')
            ->where('id = ?')
            ->setParameter(count($this->getFormFields()), $entity->get('id'));
        if ($this->definition->hasOptimisticLocking()) {
            $queryBuilder->set('version', 'version + 1');
        }

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

        $this->saveMany($entity);
        $entities = [$entity];
        $this->enrichWithReference($entities);
        return $affected > 0;
    }

    /**
     * Constructor.
     *
     * @param EntityDefinition $definition
     * the entity definition
     * @param FilesystemInterface $filesystem
     * the filesystem to use
     * @param Connection $database
     * the Doctrine DBAL instance to use
     * @param boolean $useUUIDs
     * flag whether to use UUIDs as primary key
     */
    public function __construct(EntityDefinition $definition, FilesystemInterface $filesystem, Connection $database, $useUUIDs)
    {
        $this->definition = $definition;
        $this->filesystem = $filesystem;
        $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);
        }
        $this->enrichWithReference($entities);
        return $entities;
    }

    /**
     * {@inheritdoc}
     */
    public function getIdToNameMap($entity, $nameField)
    {
        $nameSelect   = $nameField !== null ? ',`'.$nameField.'`' : '';
        $drivingField = $nameField ?: 'id';

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

philiplb / CRUDlex

Push — master ( a99c75...28b121 )

MySQLData::doDelete() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like