Doctrine DQL — Complete Guide to Doctrine Query Language
In this tutorial, you will learn about Doctrine DQL. We cover key concepts, practical examples, and best practices to help you master this topic.
Doctrine Query Language (DQL) is an object-oriented query language that operates on entity objects instead of database tables, supporting SELECT, JOIN, aggregation, and subqueries.
What You'll Learn
By the end of this tutorial, you'll write DQL queries, use the QueryBuilder for dynamic queries, perform JOINs and aggregations, use subqueries, and optimize with partial objects.
Why DQL Matters
DQL provides database-agnostic querying that returns hydrated entity objects. The QueryBuilder enables dynamic query construction safe from SQL Injection.
Real-World Use
A reporting module uses DQL with JOINs to aggregate sales by region, QueryBuilder for dynamic filter building, and pagination for large result sets. Native SQL handles complex reporting queries.
DQL Path
flowchart LR
A[Doctrine Relations] --> B[DQL Queries]
B --> C[SELECT]
B --> D[JOIN]
B --> E[Aggregation]
B --> F[QueryBuilder]
B --> G{You Are Here}
style G fill:#f90,color:#fff
Basic DQL SELECT
Write DQL queries against entity classes.
<?php
use Doctrine\ORM\EntityManager;
$dql = "SELECT p FROM App\Entity\Product p WHERE p.price > :minPrice ORDER BY p.price DESC";
$query = $entityManager->createQuery($dql);
$query->setParameter("minPrice", 100);
$products = $query->getResult();
// Single result
$dql = "SELECT p FROM App\Entity\Product p WHERE p.id = :id";
$product = $entityManager->createQuery($dql)
->setParameter("id", 1)
->getOneOrNullResult();
JOIN Queries
Join related entities in DQL.
<?php
$dql = "SELECT o, c, i FROM App\Entity\Order o
JOIN o.customer c
JOIN o.items i
WHERE o.total > :min AND c.active = :active
ORDER BY o.createdAt DESC";
$orders = $entityManager->createQuery($dql)
->setParameter("min", 100)
->setParameter("active", true)
->getResult();
// LEFT JOIN for optional associations
$dql = "SELECT p, c FROM App\Entity\Product p
LEFT JOIN p.category c";
$products = $entityManager->createQuery($dql)->getResult();
Aggregation Queries
Use aggregation functions in DQL.
<?php
$dql = "SELECT c.name, COUNT(p.id) AS productCount,
AVG(p.price) AS avgPrice,
MAX(p.price) AS maxPrice,
MIN(p.price) AS minPrice
FROM App\Entity\Product p
JOIN p.category c
GROUP BY c.id
HAVING COUNT(p.id) > 5
ORDER BY productCount DESC";
$stats = $entityManager->createQuery($dql)->getScalarResult();
QueryBuilder
Build dynamic queries programmatically.
<?php
use Doctrine\ORM\QueryBuilder;
$qb = $entityManager->createQueryBuilder();
$qb->select("p")
->from("App\Entity\Product", "p")
->leftJoin("p.category", "c")
->where("p.price > :minPrice")
->andWhere("p.active = :active")
->orderBy("p.name", "ASC")
->setFirstResult(0)
->setMaxResults(10)
->setParameter("minPrice", 50)
->setParameter("active", true);
$products = $qb->getQuery()->getResult();
Subqueries
Use subqueries in DQL where clauses.
<?php
$qb = $entityManager->createQueryBuilder();
$sub = $entityManager->createQueryBuilder();
$sub->select("AVG(p2.price)")
->from("App\Entity\Product", "p2");
$qb->select("p")
->from("App\Entity\Product", "p")
->where($qb->expr()->gt("p.price", sprintf("(%s)", $sub->getDQL())))
->orderBy("p.price", "DESC");
$expensiveProducts = $qb->getQuery()->getResult();
Common Mistakes
1. Selecting Entities Without Joining
SELECT p.category.name causes a lazy load. JOIN the relation instead.
2. N+1 Queries via DQL
Fetching orders without joining items loads items lazily. Always JOIN related entities.
3. Forgetting to Set Max Results on Pagination
Without setMaxResults, pagination fetches all rows. Always limit.
4. Using getResult() for Single Results
getResult() returns an array. Use getOneOrNullResult() or getSingleResult() for single entities.
5. Hydrating Entire Objects for Counts
Use getScalarResult() or getSingleScalarResult() for aggregate values.
Practice Questions
1. What is the difference between getResult() and getArrayResult()?
getResult() hydrates entity objects. getArrayResult() returns nested arrays without objects.
2. How do you add a WHERE IN clause in DQL?
Use WHERE p.id IN (:ids) and setParameter("ids", [1, 2, 3]).
3. What is the QueryBuilder?
A fluent API for building DQL queries programmatically with conditionals.
4. How do you paginate DQL results?
Use setFirstResult() and setMaxResults() on the Query.
5. Challenge: Write a QueryBuilder for filtered product search.
<?php
function searchProducts(EntityManager $em, array $filters): array {
$qb = $em->createQueryBuilder();
$qb->select("p")->from("App\Entity\Product", "p");
if (!empty($filters["minPrice"])) {
$qb->andWhere("p.price >= :minPrice")->setParameter("minPrice", $filters["minPrice"]);
}
if (!empty($filters["categoryId"])) {
$qb->join("p.category", "c")->andWhere("c.id = :catId")->setParameter("catId", $filters["categoryId"]);
}
if (!empty($filters["search"])) {
$qb->andWhere("p.name LIKE :search")->setParameter("search", "%{$filters["search"]}%");
}
return $qb->getQuery()->getResult();
}
FAQ
Mini Project: Reporting Queries with DQL
Build reporting queries for an e-commerce application.
<?php
$dql = "SELECT c.name,
COUNT(DISTINCT o.id) AS orderCount,
SUM(oi.quantity * oi.unitPrice) AS revenue,
AVG(o.total) AS avgOrderValue
FROM App\Entity\Customer c
JOIN c.orders o
JOIN o.items oi
WHERE o.createdAt >= :startDate
GROUP BY c.id
ORDER BY revenue DESC";
$report = $entityManager->createQuery($dql)
->setParameter("startDate", new DateTimeImmutable("-30 days"))
->getArrayResult();
What's Next
Eloquent ORM Deep Doctrine Relations Doctrine Migrations
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro