Get Results From Doctrine As DTOs

#php #doctrine #orm

Data Transfer Object is an element used to pass data between various places in application. It's only role comes down to store data. It does not contain any logic.

Let's assume we need data from database to create some report. Thera are a planty ways to do it. We can retrieve entities and then get interesting data from those entities. However, it will not probably be the most optimum way. We can also get raw data form database. But then there will be no type checking and so on. In Doctrine there is a simpler way.

Doctrine from version 2.4 allows to fetch data as DTO in a very pleasent way. It creates objects for us, setting the correct types the class has in the constructor. The only limitation is that the objects can be created from scalar types.

Let's assume there are two entities: User and Income.

/**
 * @ORM\Entity()
 */
class User
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private ?int $id;

    /**
     * @ORM\Column(type="string", length=255, nullable=false)
     */
    private string $fullName;

    /**
     * @var Collection
     * @ORM\OneToMany(targetEntity="Income", mappedBy="user")
     */
    private Collection $incomes;

    //…
}

/**
 * @ORM\Entity()
 */
class Income
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private ?int $id;

    /**
     * @ORM\ManyToOne(targetEntity="User", inversedBy="incomes")
     * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
     */
    private User $user;

    /**
     * @ORM\Column(type="datetime", nullable=false)
     */
    private \DateTime $when;

    /**
     * @ORM\Column(type="integer", nullable=false)
     */
    private int $income;

    //...
}

Database contains such data:

As shown above, there are two users with some income in each month.

The report requires whole user income so the DTO class may look like this:

class UserIncome
{
    private string $fullName;
    private int $income;

    public function __construct(string $fullName, int $income)
    {
        $this->fullName = $fullName;
        $this->income = $income;
    }

    public function getFullName(): string
    {
        return $this->fullName;
    }

    public function getIncome(): int
    {
        return $this->income;
    }
}

To get aggregated data we need to use a Query Builder to create a query:

$result = $this->entityManager->createQueryBuilder()
    ->select(sprintf(
        'NEW %s(u.fullName, SUM(i.income))',
        UserIncome::class
    ))
    ->from(User::class, 'u')
    ->leftJoin('u.incomes', 'i')
    ->where('YEAR(i.when) = 2020')
    ->groupBy('u.fullName')
    ->getQuery()
    ->getResult();

The creation of DTO object s occurs by adding a NEW operator with the fully qualified class name with the particular constructor arguments.

The results are as follows:

The way described above greately facilitates the creation of data transfer objects. Instead of fetching whole entities and extracting data from them, we can immedietly retrieve objects filled with data. Thanks to this we can save a lot of processor cycles, as well as avoid writing many more lines of code