Stop Repeating Yourself: Use the Builder Pattern for DTOs in Doctrine

– 5 min read
symfony doctrine

How do you fetch data from the database when you’re not using Doctrine entities? You probably use simple arrays or, if you care about type safety, you probably use DTOs. Now, let’s say the data you need is so complex that you decide to create a database view just for your use case. While database views can be treated as read-only entities, they’re not as flexible as handcrafted queries using DQL or the QueryBuilder.

If you find yourself repeating the same query over and over, that’s a sign it’s time to implement the Builder Pattern to avoid duplication. This approach will centralize all query-related logic, make it testable with PHPUnit, and give your code a more professional look.

Let’s begin with two simple queries that return the same DTO from the database.

<?php
/**
 * @return InvoiceListing[]
 */
function getInvoiceListingCollection(
    int $page,
    int $itemsPerPage
): array {
    return $this->createQueryBuilder('invoice')
        ->select(
            sprintf(
                <<<'DQL'
                    new %s(
                        invoice.id,
                        invoice.number,
                        invoice.emissionDate,
                        invoice.paidValue,
                        JSON_AGG(
                            JSON_BUILD_OBJECT(
                                'id', item.id,
                                'description', item.description,
                                'cents', item.cents
                            )
                        )
                    )
                DQL
                ,
                InvoiceListing::class
            )
        )
        ->leftJoin('invoice.invoiceItems', 'item')
        ->groupBy(
            'invoice.id',
            'invoice.number',
            'invoice.emissionDate',
            'invoice.paidValue'
        )
        ->setFirstResult(($page - 1) * $itemsPerPage)
        ->setMaxResults($itemsPerPage)
        ->getQuery()
        ->getArrayResult();
}

function findOneInvoiceListingById(int $id): InvoiceListing
{
    return $this->createQueryBuilder('invoice')
        ->select(
            sprintf(
                <<<'DQL'
                    new %s(
                        invoice.id,
                        invoice.number,
                        invoice.emissionDate,
                        invoice.paidValue,
                        JSON_AGG(
                            JSON_BUILD_OBJECT(
                                'id', item.id,
                                'description', item.description,
                                'cents', item.cents
                            )
                        )
                    )
                DQL
                ,
                InvoiceListing::class
            )
        )
        ->leftJoin('invoice.invoiceItems', 'item')
        ->groupBy(
            'invoice.id',
            'invoice.number',
            'invoice.emissionDate',
            'invoice.paidValue'
        )
        ->where('invoice.id = :invoiceId')
        ->setParameter(':invoiceId', $id)
        ->getQuery()
        ->getSingleResult();
}

As you can see, there’s a lot of repeated logic in this Doctrine repository class. The class can grow very quickly if the same DTO is queried in multiple ways. Now, what happens when you change the structure of the DTO? Are you going to update every single query in the repository? Are you going to copy and paste an old query every time you need to create a new one with a slightly different parameter?

When I was starting out as a PHP programmer, I would do it like this.

<?php
private function getInvoiceListingQueryBuilder(): QueryBuilder
{
    return $this->createQueryBuilder('invoice')
        ->select(
            sprintf(
                <<<'DQL'
                    new %s(
                        invoice.id,
                        invoice.number,
                        invoice.emissionDate,
                        invoice.paidValue,
                        JSON_AGG(
                            JSON_BUILD_OBJECT(
                                'id', item.id,
                                'description', item.description,
                                'cents', item.cents
                            )
                        )
                    )
                DQL
                ,
                InvoiceListing::class
            )
        )
        ->leftJoin('invoice.invoiceItems', 'item')
        ->groupBy(
            'invoice.id',
            'invoice.number',
            'invoice.emissionDate',
            'invoice.paidValue'
        );
}

/**
 * @return InvoiceListing[]
 */
function getInvoiceListingCollection(
    int $page,
    int $itemsPerPage
): array {
    return $this->getInvoiceListingQueryBuilder()
        ->setFirstResult(($page - 1) * $itemsPerPage)
        ->setMaxResults($itemsPerPage)
        ->getQuery()
        ->getArrayResult();
}

function findOneInvoiceListingById(int $id): InvoiceListing
{
    return $this->getInvoiceListingQueryBuilder()
        ->where('invoice.id = :invoiceId')
        ->setParameter(':invoiceId', $id)
        ->getQuery()
        ->getSingleResult();
}

It might seem like a good solution at first. However, in large projects, Doctrine repositories can become very messy, specially when developers try to deduplicate common queries by creating private helper methods. The result is often a bloated class, filled with private functions and multiple responsibilities, which ultimately becomes hard to test and maintain.

Let’s write a simple class that will encapsulate all the logic behind the custom DTO query.

<?php

declare(strict_types=1);

namespace App\Builder\ApiResource\InvoiceListing;

use App\ApiResource\InvoiceListing\InvoiceListing;
use App\Entity\Invoice;
use Doctrine\ORM\QueryBuilder;
use function sprintf;

final readonly class InvoiceListingQueryBuilder
{
    public function __construct(private QueryBuilder $queryBuilder)
    {
        $this->queryBuilder
            ->select(
                sprintf(
                    <<<'DQL'
                        new %s(
                            invoice.id,
                            invoice.number,
                            invoice.emissionDate,
                            invoice.paidValue,
                            JSON_AGG(
                                JSON_BUILD_OBJECT(
                                    'id', item.id,
                                    'description', item.description,
                                    'cents', item.cents
                                )
                            )
                        )
                    DQL,
                    InvoiceListing::class
                )
            )
            ->from(Invoice::class, 'invoice')
            ->leftJoin('invoice.invoiceItems', 'item')
            ->groupBy(
                'invoice.id',
                'invoice.number',
                'invoice.emissionDate',
                'invoice.paidValue'
            );
    }
}

This class will follow the Builder Pattern as described by Refactoring Guru. It can be extended with methods that modify the query parameters without duplicating any code.

Can you spot any difference between the queries in the first example? They use the same selection, but apply different database filters. These differences can be implemented as simple methods in the query builder class, as if you were writing your own version of QueryBuilder.

Now, you might be wondering:

— Doctrine’s Query Builder already follows the builder pattern. Why should I create another one on top of it?

The answer is simple: Doctrine’s QueryBuilder is too generic for your specific case.

<?php

declare(strict_types=1);

namespace App\Builder\ApiResource\InvoiceListing;

use App\ApiResource\InvoiceListing\InvoiceListing;
use App\Entity\Invoice;
use Doctrine\ORM\QueryBuilder;
use function sprintf;

final readonly class InvoiceListingQueryBuilder
{
    public function __construct(private QueryBuilder $queryBuilder)
    {
        // ...
    }

    public function withInvoiceId(int $id): static
    {
        $this->queryBuilder
            ->where('invoice.id = :invoiceId')
            ->setParameter(':invoiceId', $id);
        return $this;
    }

    public function withPagination(int $page, int $itemsPerPage): static
    {
        $this->queryBuilder
            ->setMaxResults($itemsPerPage)
            ->setFirstResult(($page - 1) * $itemsPerPage);
        return $this;
    }

    /**
     * @return InvoiceListing[]
     */
    public function getArrayResult(): array
    {
        return $this->queryBuilder->getQuery()->getArrayResult();
    }

    public function getSingleResult(): InvoiceListing
    {
        return $this->queryBuilder->getQuery()->getSingleResult();
    }
}

This is the full class with all remaining methods, now let’s see how it looks in the Doctrine Repository.

<?php

function getInvoiceListingCollection(int $page, int $itemsPerPage): array
{
    return (
        new InvoiceListingQueryBuilder(
            $this->getEntityManager()->createQueryBuilder()
        )
    )
        ->withPagination($page, $itemsPerPage)
        ->getArrayResult();
}

function findOneInvoiceListingById(int $id): InvoiceListing
{
    return (
        new InvoiceListingQueryBuilder(
            $this->getEntityManager()->createQueryBuilder()
        )
    )
        ->withInvoiceId($id)
        ->getSingleResult();
}

It looks much cleaner, doesn’t it? Now, let’s see how a unit test looks like:

<?php

declare(strict_types=1);

namespace Tests\Unit\Builder\ApiResource\InvoiceListing;

use App\Builder\ApiResource\InvoiceListing\InvoiceListingQueryBuilder;
use Doctrine\ORM\QueryBuilder;
use PHPUnit\Framework\TestCase;

final class InvoiceListingQueryBuilderTest extends TestCase
{
    public function testWithPaginationSetsCorrectFirstResult(): void
    {
        $queryBuilder = $this->createMock(QueryBuilder::class);
        $queryBuilder->method('select')->willReturnSelf();
        $queryBuilder->method('from')->willReturnSelf();
        $queryBuilder->method('leftJoin')->willReturnSelf();
        $queryBuilder->method('groupBy')->willReturnSelf();
        $queryBuilder->method('setMaxResults')->willReturnSelf();

        $queryBuilder->expects($this->once())
            ->method('setFirstResult')
            ->with(20)
            ->willReturnSelf();

        $builder = new InvoiceListingQueryBuilder($queryBuilder);
        $builder->withPagination(3, 10);
    }
}

These kinds of tests can be easily generated by any reasonable AI 1, so if you follow a design pattern like Builder, you’re in good hands.

Conclusion

Using DTOs in Doctrine doesn’t mean your repository classes need to become bloated or repetitive. By introducing a small abstraction with the Builder Pattern, you can isolate query logic, reduce duplication, and write cleaner, more maintainable code.

This approach also makes your queries easier to test and reuse across different contexts like pagination, filtering, custom endpoints, without ever having to repeat the same logic twice.

So the next time you find yourself copying a createQueryBuilder() block for the tenth time, take a step back and consider: maybe it’s time to build a builder.

  1. Like ChatGPT, Claude AI, and others. ↩︎