MySQLData::addFilter() - Code Metrics - Inspection of "updated docs" - philiplb/CRUDlex - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 5b7e22...1f9a7e )

by Philip

created 2017-01-03 22:41 UTC

MySQLData::addFilter() B

↳ Parent: MySQLData

Complexity

Conditions	6
Paths	10

Size

Total Lines	26
Code Lines	20

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
c	0
b	0
f	0
dl	0
loc	26
rs	8.439
cc	6
eloc	20
nc	10
nop	3

<?php

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

namespace CRUDlex;

use Doctrine\DBAL\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;

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

    /**
     * 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);
        }
        $this->enrichWithReference($entities);
        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);
        $entities = [$entity];
        $this->enrichWithReference($entities);

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

        return true;
    }

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

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

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

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

        $this->saveMany($entity);
        $entities = [$entity];
        $this->enrichWithReference($entities);

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

        return $affected;
    }

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

philiplb / CRUDlex

Push — master ( 5b7e22...1f9a7e )

MySQLData::addFilter() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like