Doctrine NEW DQL operator and objects

Can we combine them?

Author: Przemek Pawlas

Tue Apr 03 2018
6
PHP
2
DQL
2
Doctrine
1
Data Transfer Object
1
Doctrine NEW
1
ORM

Issue

Sometimes we need to pull data from the database that doesn’t translate to an entity. For example, we want to obtain an entity and SUM() of one of its fields in one query. Or three different SUM()s for statistics. To do that, the most objective-oriented solution is passing the data to a Data Transfer Object.

Does NEW keyword help?

The NEW keyword, available since Doctrine 2.4, unfortunately currently allows only scalar values passed to DTO’s constructor. Example:

<?php

namespace Example\DTO;

class SomeEntityData
{
    // Property definitions

    public function __construct(string $field, ?int $count)
    {
        $this->field = $field;
        $this->count = $count;
    }

    // Getters
}
<?php

namespace Example\Repository;

use Doctrine\ORM\EntityRepository;
use Example\DTO\SomeEntityData;

class SomeEntityRepository extends EntityRepository
{
    public function getSomeEntityWithData() : array
    {
        $queryBuilder = $this->createQueryBuilder('someEntity');

        $queryBuilder
            ->select(
                sprintf(
                    'NEW %s(someEntity.field, COUNT(anotherEntity))',
                    SomeEntityData::class
                )
            )
            ->innerJoin('someEntity.collection', 'anotherEntity')
            ->where('someEntity.value > 5')
            ->groupBy('someEntity.id');

        // EntityData[]
        return $queryBuilder->getQuery()->getResult();
    }
}

If we used NEW %s(entity, COUNT(anotherEntity)) and accepted Entity $entity in DTO constructor instead, we would get an error saying that entity is an unexpected string.

You can easily miss that in the official documentation because the line that speaks about scalar values is below two code blocks.

What can we do then?

There are multiple solutions. One of them, the simpliest in my opinion, is adding aliases to selected fields and mapping query results to DTOs:

<?php

namespace Example\Repository;

use Doctrine\ORM\EntityRepository;
use Example\DTO\SomeEntityData;

class SomeEntityRepository extends EntityRepository
{
    public function getSomeEntityWithData() : array
    {
        $queryBuilder = $this->createQueryBuilder('someEntity');

        $queryBuilder
            ->select('someEntity AS entityData, COUNT(anotherEntity) AS countData')
            ->innerJoin('someEntity.collection', 'anotherEntity')
            ->where('someEntity.value > 5')
            ->groupBy('someEntity.id');

        // EntityData[]
        return \array_map(
            function (array $result) {
                return new SomeEntityData($result['entityData'], $result['countData']);
            },
            $queryBuilder->getQuery()->getResult()
        );
    }
}

Another one is using Doctrine’s ResultSetMapping, which basically does the same thing in a more defined and strict way.

Loading...
Boldare