DQL

Doctrine Query Language queries entity classes and properties rather than tables and columns, then compiles to SQL you can inspect.

Syntax$em->createQuery('SELECT a FROM App\Entity\Article a WHERE a.slug = :slug')->setParameter('slug', $slug)

DQL is Doctrine Query Language. It looks like SQL but it addresses your object model: entity class names where SQL has table names, property names where SQL has columns, and mapped associations where SQL has join conditions. Doctrine compiles it to SQL for the database you are connected to.

You create one with $entityManager->createQuery($dql), bind values with setParameter(), and execute with getResult() for a list, getOneOrNullResult() for zero or one, or getSingleScalarResult() for a single value such as a count.

Where it differs from SQL

  • There is no SELECT *. SELECT a selects the whole entity.
  • There is no LIMIT. Use setMaxResults() and setFirstResult() on the Query; they end up in the SQL.
  • You join through a mapped property, JOIN a.comments c, not through a foreign key you write yourself.
  • The identifier after FROM is a PHP class name: App\Entity\Article.

Shorthand and longhand

The QueryBuilder is a builder for DQL and nothing more. createQueryBuilder('a')->andWhere('a.status = :status') and the DQL string SELECT a FROM App\Entity\Article a WHERE a.status = :status are the same query; getDQL() on the builder prints it. Parameters can be named, :status, or positional, ?1. Named placeholders survive refactoring better.

What trips people up: UPDATE and DELETE in DQL go straight to the database. They do not consult the entity manager's record of loaded objects and pending changes, its unit of work, and they do not update objects already in memory.

Example

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

declare(strict_types=1);

namespace App\Repository;

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);
    }

    /**
     * @return Article[]
     */
    public function findPublishedBefore(\DateTimeImmutable $before, int $limit = 10): array
    {
        return $this->getEntityManager()
            ->createQuery(
                'SELECT a
                 FROM App\Entity\Article a
                 WHERE a.status = :status
                   AND a.publishedAt < :before
                 ORDER BY a.publishedAt DESC'
            )
            ->setParameter('status', 'published')
            ->setParameter('before', $before)
            // DQL has no LIMIT clause; this is where the limit goes.
            ->setMaxResults($limit)
            ->getResult();
    }
}

When to use it

  • Write a report query over several entities that no single repository finder covers.
  • Count or sum in the database instead of loading rows and reducing them in PHP.
  • Archive or delete a large batch of rows in one statement rather than entity by entity.

More examples

Reading the SQL Doctrine generated

Reach for getSQL() when a query returns something you did not expect, or when a database administrator asks what your application is running. It is also how you check that a join you wrote in DQL became the join you meant in SQL. The web profiler shows the same SQL for every query in a request.

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

public function debugPublishedSql(): string
{
    $query = $this->getEntityManager()->createQuery(
        'SELECT a FROM App\Entity\Article a WHERE a.status = :status'
    );

    // getSQL() compiles the DQL without executing it. The result has
    // this shape: one generated alias per table, one per selected column,
    // and the named parameter turned into a positional placeholder.
    //
    //   SELECT a0_.id AS id_0, a0_.title AS title_1, a0_.status AS status_2
    //   FROM article a0_ WHERE a0_.status = ?
    //
    // The aliases are generated, so never depend on their exact names.
    return $query->getSQL();
}

An aggregate instead of a row count in PHP

This is the alternative to loading the rows and calling count() on the array: one number comes back from the database and no entity is built. getSingleScalarResult() throws when the query returns no row at all, which an aggregate never does, so a COUNT is a safe use of it.

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

public function countPublishedSince(\DateTimeImmutable $since): int
{
    $value = $this->getEntityManager()
        ->createQuery(
            'SELECT COUNT(a.id)
             FROM App\Entity\Article a
             WHERE a.status = :status
               AND a.publishedAt >= :since'
        )
        ->setParameter('status', 'published')
        ->setParameter('since', $since)
        ->getSingleScalarResult();

    // Some drivers return a numeric string, so cast at the boundary.
    return (int) $value;
}

A bulk update, and what it leaves stale

One statement updates thousands of rows, where loading each entity and flushing would be far slower. The cost is that the unit of work knows nothing about it: anything already in memory keeps its old status, and no lifecycle callback runs. Do bulk statements early in a request, or clear afterwards.

Example · php
// src/Service/ArticleArchiver.php (method on a service)

public function archivePublishedBefore(\DateTimeImmutable $cutoff): int
{
    $affected = $this->entityManager
        ->createQuery(
            'UPDATE App\Entity\Article a
             SET a.status = :archived
             WHERE a.status = :published
               AND a.publishedAt < :cutoff'
        )
        ->setParameter('archived', 'archived')
        ->setParameter('published', 'published')
        ->setParameter('cutoff', $cutoff)
        ->execute();

    // The database changed; objects loaded before this call did not.
    // clear() detaches everything, so flush any pending work first.
    $this->entityManager->clear();

    return $affected;
}

Related

symfony querybuildersymfony repositories introsymfony hydrationsymfony entity managersymfony native sql

Discussion

  • Be the first to comment on this lesson.