The QueryBuilder
Assembling a query method by method, binding every value as a parameter, and the difference between where and andWhere.
$qb = $repository->createQueryBuilder('a')->andWhere('a.status = :s')->setParameter('s', $status);The QueryBuilder builds a query by method calls instead of by string concatenation. You reach for it when parts of the query depend on runtime conditions: a filter that is only applied when the user supplied it, a sort column chosen from a whitelist, an optional join.
$this->createQueryBuilder('a') returns a builder already selecting the repository's entity under the alias a. From there andWhere(), setParameter(), orderBy(), setMaxResults() and setFirstResult() each return the builder, so calls chain. getQuery() ends the building and gives you a Query, which you execute with getResult(), getOneOrNullResult() or getSingleScalarResult().
Values are parameters, always
A placeholder such as :status in the query, and the value passed to setParameter('status', $status). The value never reaches the query string, so it cannot change the query's meaning. This holds inside a LIKE too: the wildcards go in the value, not in the query. There is no case where concatenating a value is acceptable, including when you are sure it is an integer.
Shorthand and longhand
join() is the same method as innerJoin(). Conditions can be written as strings or built with the expression API: $qb->expr()->eq('a.status', ':status') produces the string a.status = :status, and orX() joins conditions with OR. Use whichever reads better; they compile to the same query.
What trips people up: where() discards every condition set before it. In a method with two optional filters, the second where() silently deletes the first. Use andWhere() throughout, even for the first condition.
Example
<?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);
}
/**
* Both filters are optional, so both are added with andWhere().
*
* @return Article[]
*/
public function search(?string $term, ?string $status, int $limit = 20): array
{
$qb = $this->createQueryBuilder('a')
->orderBy('a.publishedAt', 'DESC')
->setMaxResults($limit);
if (null !== $status) {
$qb->andWhere('a.status = :status')
->setParameter('status', $status);
}
if (null !== $term) {
// The wildcards belong to the value, not to the query string.
$qb->andWhere('a.title LIKE :term')
->setParameter('term', '%'.$term.'%');
}
return $qb->getQuery()->getResult();
}
}When to use it
- Build a search endpoint where each query-string filter is applied only when present.
- Fetch a list together with one related entity so the template does not trigger a query per row.
- Keep a complicated filter in one repository method that several controllers call.
More examples
Joining an association and selecting it in the same query
Without addSelect(), each author is loaded on first access, so a list of twenty articles runs twenty-one queries. With it, the join supplies the authors in the first query. This is the fix for the one-query-per-row problem in any template that prints a related value.
// src/Repository/ArticleRepository.php (method on the generated class)
/**
* An association is a property mapped as a link to another entity;
* here Article::$author points at an Author.
*
* @return Article[]
*/
public function findRecentWithAuthor(int $limit = 20): array
{
return $this->createQueryBuilder('a')
// innerJoin() walks the mapped association, not a table name.
->innerJoin('a.author', 'u')
// addSelect() brings the author back in this query, so reading
// $article->getAuthor() in a loop costs no extra queries.
->addSelect('u')
->andWhere('a.status = :status')
->setParameter('status', 'published')
->orderBy('a.publishedAt', 'DESC')
->setMaxResults($limit)
->getQuery()
->getResult();
}
// join('a.author', 'u') is the same call as innerJoin('a.author', 'u').A list parameter, and reading back the query you built
An empty array produces a query matching nothing rather than an error, so guard for it if an empty result is not what you want. getDQL() is the quickest way to see whether the condition you expected actually made it into the query.
// src/Repository/ArticleRepository.php (method on the generated class)
/**
* @param int[] $ids
*
* @return Article[]
*/
public function findByIds(array $ids): array
{
$qb = $this->createQueryBuilder('a')
// One placeholder for the whole list; Doctrine expands it.
->andWhere('a.id IN (:ids)')
->setParameter('ids', $ids)
->orderBy('a.id', 'ASC');
// getDQL() returns the query text assembled so far. Useful in a log
// line while you are working out why a filter did not apply.
// $this->logger->debug($qb->getDQL());
return $qb->getQuery()->getResult();
}An OR group, as a string and through the expression API
Both forms produce the same query. The expression API earns its keep when the parts of an OR group are themselves conditional, because you can collect expressions in an array and pass the ones you want; the string form is easier to read when the condition is fixed.
// src/Repository/ArticleRepository.php (method on the generated class)
/**
* @return Article[]
*/
public function findDraftsOrUnscheduled(): array
{
$qb = $this->createQueryBuilder('a');
// Longhand: build the condition as objects.
$qb->andWhere(
$qb->expr()->orX(
$qb->expr()->eq('a.status', ':status'),
$qb->expr()->isNull('a.publishedAt'),
)
);
// Shorthand: the same condition written out.
// $qb->andWhere('a.status = :status OR a.publishedAt IS NULL');
return $qb->setParameter('status', 'draft')
->getQuery()
->getResult();
}
Discussion