Hydration and Partial Results

Hydration turns database rows into PHP values: objects, arrays, scalars or DTOs, each with a different cost.

Syntax$query->getArrayResult() // same as $query->getResult(Query::HYDRATE_ARRAY)

Hydration is the step where Doctrine turns the rows the database returned into PHP values. The same DQL can hydrate to entity objects, to nested arrays, to flat scalars, or to objects of a class you choose. Which one you pick decides how much memory the query costs and whether the result can be saved.

The modes

  • getResult() builds entities and registers each one in the entity manager's unit of work — its record of loaded objects and pending changes. They can be modified and flushed.
  • getArrayResult() returns plain arrays. Nothing is registered, so nothing can be written back.
  • getScalarResult() returns flat rows of scalar values.
  • getSingleScalarResult() returns one value, for a COUNT or a SUM.
  • getSingleColumnResult() returns a one-dimensional array, for a list of ids.

getArrayResult() is shorthand: getResult(Query::HYDRATE_ARRAY) is the same call written out, and getResult() with no argument means Query::HYDRATE_OBJECT.

Selecting less

For a list page you usually need two or three fields. DQL's NEW operator constructs a plain PHP object per row: SELECT NEW App\Dto\ArticleSummary(a.id, a.title). You get typed, read-only objects, no tracking, and only the columns you asked for.

DQL also has a partial keyword, SELECT partial a.{id, title}, which returns real entities with only those fields fetched. On PHP 8.4 and later, where native lazy objects are used, the fields you left out load on first access rather than being absent. The older Query::HINT_FORCE_PARTIAL_LOAD hint is deprecated; prefer a DTO.

What trips people up: a report that hydrates fifty thousand entities exhausts memory even though it only reads them.

Example

Example · php
<?php
// src/Repository/ArticleRepository.php

declare(strict_types=1);

namespace App\Repository;

use App\Dto\ArticleSummary;
use App\Entity\Article;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

class ArticleRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Article::class);
    }

    /**
     * Two columns per row and no managed entities, for a list page
     * that only prints a link.
     *
     * @return ArticleSummary[]
     */
    public function findSummaries(int $limit = 50): array
    {
        return $this->createQueryBuilder('a')
            // NEW calls the DTO constructor once per row.
            ->select(sprintf('NEW %s(a.id, a.title)', ArticleSummary::class))
            ->andWhere('a.status = :status')
            ->setParameter('status', 'published')
            ->orderBy('a.publishedAt', 'DESC')
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }
}

When to use it

  • Render a list page from two columns per row instead of whole entities.
  • Produce a CSV export of a large table without exhausting PHP's memory limit.
  • Return a count or a sum from the database as a single value.

More examples

The DTO the NEW operator constructs

A data transfer object is an ordinary class with no mapping attributes and no repository. It is not an entity, so the entity manager never tracks it and a later flush() cannot write it anywhere. That is the point: a list page cannot accidentally save something it only displayed.

Example · php
<?php
// src/Dto/ArticleSummary.php

declare(strict_types=1);

namespace App\Dto;

final class ArticleSummary
{
    public function __construct(
        public readonly int $id,
        public readonly string $title,
    ) {
    }
}

// Given rows (1, 'Indexes'), (2, 'Locks'):
// Output: array of two ArticleSummary objects; $list[0]->title === 'Indexes'

Objects against arrays, same query

When fields are selected individually the array keys are the entity property names, not the column names. Use the array form for anything you are about to serialise or render and will never modify; use entities when the code is going to change them and flush.

Example · php
// src/Repository/ArticleRepository.php (two methods on the generated class)

/**
 * Entities: modifiable, tracked, one object per row plus its fields.
 *
 * @return Article[]
 */
public function recentEntities(int $limit): array
{
    return $this->createQueryBuilder('a')
        ->orderBy('a.id', 'ASC')
        ->setMaxResults($limit)
        ->getQuery()
        ->getResult();
}

/**
 * Arrays: nothing tracked, only the two columns named.
 *
 * @return array<int, array{id: int, title: string}>
 */
public function recentRows(int $limit): array
{
    return $this->createQueryBuilder('a')
        ->select('a.id', 'a.title')
        ->orderBy('a.id', 'ASC')
        ->setMaxResults($limit)
        ->getQuery()
        ->getArrayResult();
}

// recentRows(2) returns rows keyed by field name:
// Output: [['id' => 1, 'title' => 'Indexes'], ['id' => 2, 'title' => 'Locks']]

Streaming a large result in a console command

toIterable() keeps peak memory near one row instead of the whole table. clear() detaches every entity the manager holds, which is what frees the memory, so never call it with unflushed changes pending. If the export only needs a few columns, array hydration is simpler still.

Example · php
<?php
// src/Command/ExportArticlesCommand.php

declare(strict_types=1);

namespace App\Command;

use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\Console\Attribute\AsCommand;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

#[AsCommand(name: 'app:export-articles', description: 'Writes every article id and title to stdout.')]
final class ExportArticlesCommand extends Command
{
    public function __construct(private readonly EntityManagerInterface $em)
    {
        parent::__construct();
    }

    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        $query = $this->em->createQuery('SELECT a FROM App\Entity\Article a ORDER BY a.id ASC');
        $written = 0;

        // toIterable() hydrates one entity at a time instead of all of them.
        foreach ($query->toIterable() as $article) {
            $output->writeln($article->getId()."\t".$article->getTitle());

            if (0 === ++$written % 500) {
                // Detach what has been hydrated so memory stays flat.
                $this->em->clear();
            }
        }

        $output->writeln(sprintf('%d rows written', $written));

        return Command::SUCCESS;
    }
}

// $ php bin/console app:export-articles
// 1	Indexes
// 2	Locks
// 2 rows written

Related

symfony dqlsymfony querybuildersymfony paginationsymfony doctrine performancesymfony serializer

Discussion

  • Be the first to comment on this lesson.