Skip to content
Skills Library
Programming · Building & coding

doctrine-query-optimization

/doctrine-query-optimization

Use when writing Doctrine queries, fixing N+1 problems, paginating large result sets, building read models, or tuning DQL/query builder performance. Use when the task mentions N+1, slow query, pagination, DQL, hydration, or read model.

Licence
MIT
Last repository change
25.07.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 25.07.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Use when writing Doctrine queries, fixing N+1 problems, paginating large result sets, building read models, or tuning DQL/query builder performance. Use when the task mentions N+1, slow query, pagination, DQL, hydration, or read model.

Original author description

Use when writing Doctrine queries, fixing N+1 problems, paginating large result sets, building read models, or tuning DQL/query builder performance. Use when the task mentions N+1, slow query, pagination, DQL, hydration, or read model.

How it works

  1. N+1: identify and fix
  2. Hydration modes
  3. Pagination
  4. Batch processing large sets
  5. Other wins
  6. Gotchas
Original SKILL.md headings · 128 lines, 626 words
SKILL.md preview
---
name: doctrine-query-optimization
description: >
  Use when writing Doctrine queries, fixing N+1 problems, paginating large result sets,
  building read models, or tuning DQL/query builder performance. Use when the task mentions
  N+1, slow query, pagination, DQL, hydration, or read model.
---

# Doctrine Query Optimization (Symfony)

## N+1: identify and fix

One query for the parents + one query per parent for a lazy association = **N+1**. It hides in loops that touch a lazy relation.

```php
// ❌ N+1 — every $order->getItems() fires a query
$orders = $repo->findBy(['status' => OrderStatus::Active]);
foreach ($orders as $order) {
    $total = count($order->getItems());   // SELECT ... WHERE order_id = ? — N times
}
```

**Fix A — `JOIN FETCH` (when you need the entities):**

```php
public function findActiveWithItems(): array
{
    return $this->createQueryBuilder('o')
        ->addSelect('i')                       // hydrate items in the same query
        ->leftJoin('o.items', 'i')
        ->andWhere('o.status = :status')
        ->setParameter('status', OrderStatus::Active)
        ->getQuery()
        ->getResult();
}
```

When fetch-joining a to-many and paginating, use Doctrine's `Paginator` (it switches to a windowed two-query strategy so `setMaxResults` stays correct):

```php
$query = $qb->setFirstResult($offset)->setMaxResults($limit)->getQuery();
$paginator = new Paginator($query, fetchJoinCollection: true);
```

**Fix B — read-model DTO (when you only need to display):** don't hydrate entities at all.

```php
public function summaries(OrderStatus $status): array
{
    return $this->createQueryBuilder('o')
        ->select(sprintf(
            'NEW %s(o.id, o.customerEmail, o.status, o.createdAt)',
            OrderSummary::class,
        ))
        ->andWhere('o.status = :status')
        ->setParameter('status', $status)
        ->getQuery()
        ->getResult();   // array of OrderSummary DTOs — no managed entities, no lazy loading
}
```

## Hydration modes

- Default `getResult()` returns managed entities — most expensive (UoW tracks them).
- `getArrayResult()` / `getScalarResult()` — when you only read and never mutate.
- DQL `NEW` (DTO hydration) — best for read endpoints; immutable, no identity map cost.
- For read-only list endpoints, prefer DTOs over entities every time.

## Pagination

```php
// Offset pagination (fine for early pages)
$qb->setFirstResult($page * $perPage)->setMaxResults($perPage);
```

`OFFSET` scans and discards every skipped row — page 5,000 reads 100,000 rows to return 20. For deep pages / infinite scroll, use **keyset (seek) pagination**: filter by the last seen sortable key.

```php
// ✅ Keyset — constant time regardless of depth. UUID v7 / createdAt are sortable.
public function pageAfter(?Uuid $lastId, int $limit): array
{
    $qb = $this->createQueryBuilder('o')
        ->orderBy('o.id', 'DESC')
        ->setMaxResults($limit);

    if ($lastId !== null) {
        $qb->andWhere('o.id < :lastId')->setParameter('lastId', $lastId, 'uuid');
    }

    return $qb->getQuery()->getResult();
}
```

Index the sort key. With a tie-prone sort (`createdAt`), order by `(createdAt DESC, id DESC)` and seek on the tuple so the cursor is stable.

## Batch processing large sets

Don't load a million rows into memory. Iterate and clear the identity map periodically.

```php
$query = $em->createQuery('SELECT o FROM App\Entity\Order o WHERE o.status = :s')
    ->setParameter('s', OrderStatus::Pending);

$batchSize = 500;
$i = 0;
foreach ($query->toIterable() as $order) {
    $order->markStale();
    if (++$i % $batchSize === 0) {
        $em->flush();
        $em->clear();   // detach everything, release memory
    }
}
$em->flush();
$em->clear();
```

For inserts, enable JDBC-style batching and flush in chunks. Note: `GenerationType.IDENTITY`-style auto-increment forces a round-trip per row; UUID v7 keeps batching intact (another reason to use UUIDs).

## Other wins

- Select only the columns you need with **partial DTO hydration**, not whole entities.
- Use `EXISTS`/`SELECT 1 … setMaxResults(1)` for existence checks, not `count()` of loaded rows.
- Add a second-level / result cache only for genuinely hot, rarely-changing queries — measure first.
- Profile with the Symfony Profiler's Doctrine panel; watch query count and duplicate queries.

## Gotchas

- Agent writes a loop that touches a lazy association — fetch-join it or use a read-model DTO.
- Agent fetch-joins a to-many **and** paginates with plain `setMaxResults` — wrap in `Paginator(fetchJoinCollection: true)`.
- Agent hydrates full entities for a read-only list endpoint — use DQL `NEW` DTO hydration.
- Agent uses `OFFSET` for deep pagination — switch to keyset/seek on a sortable indexed key.
- Agent loads huge result sets into an array — use `toIterable()` + periodic `flush()`/`clear()`.
- Agent calls `count($repo->findBy(...))` to check existence — use an `EXISTS`/`SELECT 1` query.
- Agent forgets to index the column used in `WHERE`/`ORDER BY` for keyset — add the index.

Versions

The database has no other skills named doctrine-query-optimization.

Database snapshot: 13.11.2026 · package version 80ca3a7754bf (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100doctrine-patterns
    Doctrine ORM best practices for Symfony: entity mapping as source of truth, repositories, DQL/QueryBuilder with parameters, N+1 prevention with fetch joins, relations (cascade/fetch/orphanRemoval), lifecycle events, batch processing, and generated migrations. Apply when: writing…
  • 100php-sql-conventions
    ACTIVATE when writing SQL queries in PHP code, using Doctrine DBAL, or formatting database queries. ACTIVATE for 'SQL', 'query', 'DBAL', 'JOIN', 'SELECT'. Covers: query direction convention (start from known entity), nowdoc formatting for SQL, column listing, JOIN ordering. DO…
  • 100dt-dql-essentials
    Core DQL syntax, pitfalls, query patterns, and query optimization. Load to write, build, fix, or OPTIMIZE a DQL query — prevents syntax errors and makes queries faster, more efficient, and cheaper (less data scanned = lower query consumption/cost per run). Covers fetch commands,…
  • 100database-optimizer
    Database performance tuning and query optimization across platforms. Use when analyzing slow queries, designing indexing strategies, tuning database configurations, or scaling database systems.
  • 100doctrine-migrations
    Use when changing the database schema in Symfony — generating Doctrine migrations, writing reversible up()/down(), data migrations, and deploy-safe (zero-downtime) changes. Use when the task mentions migration, schema change, ALTER TABLE, or doctrine:migrations.
  • 90sql-optimization
    Query tuning, indexing strategies, and EXPLAIN plan analysis. Use when: sql optimization.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.