Optimize Doctrine queries with fetch modes, lazy loading, extra lazy collections, and query hints for performance
This skill inherits all available tools. When active, it can use any tool Claude has access to.
Relations loaded on first access - can cause N+1:
#[ORM\ManyToOne(fetch: 'LAZY')]
private User $author;
// Usage
$post = $em->find(Post::class, 1);
$name = $post->getAuthor()->getName(); // Triggers query
Always load with parent - use sparingly:
#[ORM\ManyToOne(fetch: 'EAGER')]
private User $author;
// Usage - author loaded in same query
$post = $em->find(Post::class, 1);
$name = $post->getAuthor()->getName(); // No extra query
For large collections - partial operations without full load:
#[ORM\OneToMany(targetEntity: Comment::class, mappedBy: 'post', fetch: 'EXTRA_LAZY')]
private Collection $comments;
// These don't load the full collection:
$count = $post->getComments()->count(); // COUNT query
$has = $post->getComments()->contains($c); // EXISTS query
$slice = $post->getComments()->slice(0, 5); // LIMIT query
Override mapping fetch mode per query:
<?php
use Doctrine\ORM\Mapping\ClassMetadata;
// In repository
public function findWithAuthor(int $id): ?Post
{
return $this->createQueryBuilder('p')
->where('p.id = :id')
->setParameter('id', $id)
->getQuery()
->setFetchMode(Post::class, 'author', ClassMetadata::FETCH_EAGER)
->getOneOrNullResult();
}
Explicitly load relations in query:
<?php
// src/Repository/PostRepository.php
public function findAllWithRelations(): array
{
return $this->createQueryBuilder('p')
->addSelect('a', 't', 'c') // Include in SELECT
->leftJoin('p.author', 'a')
->leftJoin('p.tags', 't')
->leftJoin('p.comments', 'c')
->orderBy('p.createdAt', 'DESC')
->getQuery()
->getResult();
}
public function findByIdWithAuthor(int $id): ?Post
{
return $this->createQueryBuilder('p')
->addSelect('a')
->leftJoin('p.author', 'a')
->where('p.id = :id')
->setParameter('id', $id)
->getQuery()
->getOneOrNullResult();
}
Load only needed columns:
public function findPostTitles(): array
{
return $this->createQueryBuilder('p')
->select('PARTIAL p.{id, title, createdAt}')
->getQuery()
->getResult();
}
// Or with NEW DTO
public function findPostDTOs(): array
{
return $this->createQueryBuilder('p')
->select('NEW App\Dto\PostListItem(p.id, p.title, a.name)')
->leftJoin('p.author', 'a')
->getQuery()
->getResult();
}
Process large datasets without memory issues:
public function processAllPosts(): void
{
$query = $this->createQueryBuilder('p')
->getQuery();
foreach ($query->toIterable() as $post) {
$this->process($post);
// Clear entity manager periodically
$this->em->clear(Post::class);
}
}
Understanding lazy loading:
// $post->getAuthor() returns a Proxy, not User
$author = $post->getAuthor();
// Proxy is a subclass of User
$author instanceof User; // true
// Check if proxy is initialized
$em->getUnitOfWork()->isInIdentityMap($author); // true if loaded
// Force initialization
$em->getUnitOfWork()->initializeObject($author);
// N+1 queries!
$posts = $repository->findAll();
foreach ($posts as $post) {
echo $post->getAuthor()->getName(); // Query per iteration
}
// Single query with join
$posts = $repository->createQueryBuilder('p')
->addSelect('a')
->leftJoin('p.author', 'a')
->getQuery()
->getResult();
foreach ($posts as $post) {
echo $post->getAuthor()->getName(); // No extra query
}
use Doctrine\ORM\Query;
$query = $em->createQuery('SELECT p FROM Post p');
// Force refresh from database
$query->setHint(Query::HINT_REFRESH, true);
// Custom output walker for soft deletes
$query->setHint(
Query::HINT_CUSTOM_OUTPUT_WALKER,
'Gedmo\SoftDeleteable\Query\TreeWalker\SoftDeleteableWalker'
);
public function findAllIndexedById(): array
{
return $this->createQueryBuilder('p', 'p.id') // Index by ID
->getQuery()
->getResult();
}
// Returns ['1' => Post, '2' => Post, ...]
$posts = $repository->findAllIndexedById();
$post = $posts[42]; // Direct access, no loop needed
Skip change tracking for read-only data:
public function findForDisplay(): array
{
return $this->createQueryBuilder('p')
->getQuery()
->setHint(Query::HINT_READ_ONLY, true)
->getResult();
}