findBy() from chapter 21 is enough for simple queries – for more complex cases (combinations of filters, sorting, aggregation), we need Doctrine's query builder and DQL (Doctrine Query Language).

DQL vs. SQL: the decisive difference

DQL looks syntactically similar to SQL, but works with ENTITY classes and their PROPERTIES instead of tables and columns – Doctrine automatically translates DQL into the actual SQL dialect of your configured database (PostgreSQL, MySQL, ...), without you having to worry about dialect differences.

Using the query builder in the repository

Custom, reusable query methods belong IN the repository, NOT in the controller – the controller shouldn't need to know ANYTHING about the concrete query implementation:

// ... constructor as in chapter 21 ...

/**
 * @return Task[]
 */
public function findOverdueTasks(): array
{
    return $this->createQueryBuilder('t')
        ->andWhere('t.dueAt < :today')
        ->andWhere('t.status != :done')
        ->setParameter('today', new \DateTimeImmutable())
        ->setParameter('done', 'done')
        ->orderBy('t.dueAt', 'ASC')
        ->getQuery()
        ->getResult()
    ;
}

createQueryBuilder('t') creates a query builder with t as an alias for Task – EXACTLY like a SQL table alias. setParameter() binds values SAFELY (automatically protects against SQL injection) instead of inserting them directly into the query string.

JOINs across relationships

/**
 * @return Task[]
 */
public function findTasksForUser(User $user): array
{
    return $this->createQueryBuilder('t')
        ->innerJoin('t.project', 'p')
        ->innerJoin('p.members', 'm')
        ->andWhere('m = :user')
        ->setParameter('user', $user)
        ->getQuery()
        ->getResult()
    ;
}

innerJoin('t.project', 'p') follows the ManyToOne relationship from chapter 22 (NO manual ON statement needed, Doctrine already knows the connection from the entity mapping), innerJoin('p.members', 'm') follows the ManyToMany relationship from chapter 23 – the same method works identically for BOTH relationship types.

Writing DQL directly as a string

For some cases, raw DQL is more readable than the query builder – functionally EQUIVALENT, purely a matter of taste/team convention:

$query = $this->getEntityManager()->createQuery(
    'SELECT t FROM App\Entity\Task t WHERE t.status = :status ORDER BY t.dueAt ASC'
);
$query->setParameter('status', 'open');

$tasks = $query->getResult();

Aggregation: COUNT, AVG, and friends

public function countTasksByStatus(Project $project): array
{
    return $this->createQueryBuilder('t')
        ->select('t.status, COUNT(t.id) as count')
        ->andWhere('t.project = :project')
        ->setParameter('project', $project)
        ->groupBy('t.status')
        ->getQuery()
        ->getResult()
    ;
}

Ideal for dashboard statistics (chapter 45 uses exactly this kind of query, cached) – returns an array of status/count pairs instead of full entity objects.

getResult() vs. getOneOrNullResult() vs. getSingleResult()