Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 62 ยท Databases with Doctrine

Repositories and Queries

Read data with Doctrine in Symfony: find methods, custom repositories, DQL, the QueryBuilder, raw DBAL and pagination, with a runnable PDO stand-in.

Saving entities is only half of a database layer. Most of your time goes into reading: the ten newest posts, the products under a price, the user with a given email. In Doctrine you do not ask the entity manager for these lists directly. You ask a repository, a small service whose only job is to fetch objects of one entity class.

In this lesson you will use the built-in finder methods, write your own repository methods with the QueryBuilder and DQL, drop down to plain SQL when you need to, and paginate results. Doctrine and Symfony cannot run on this site, so the real code is read-only. The runnable examples use a plain PDO stand-in that shows the same ideas on SQLite.

Every entity has a repository

When you ask Doctrine for the repository of an entity, you get an object that knows how to build queries for that one class. In a controller or service you normally let autowiring hand it to you:

PHP
<?php

namespace App\Controller;

use App\Entity\Product;
use App\Repository\ProductRepository;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Attribute\Route;

class ProductController extends AbstractController
{
    #[Route('/products/{id}', name: 'product_show')]
    public function show(int $id, ProductRepository $products): Response
    {
        $product = $products->find($id);

        if ($product === null) {
            throw $this->createNotFoundException('No such product.');
        }

        return $this->render('product/show.html.twig', ['product' => $product]);
    }
}

find() returns an entity object or null. Nothing else in the application needs to know about SQL. The class ProductRepository is generated by make:entity and linked to the entity with the repositoryClass option:

PHP
<?php

namespace App\Entity;

use App\Repository\ProductRepository;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    // id, name, price ...
}

Because the repository is a registered service, it can have its own constructor dependencies too, which you will see in a moment.

The built-in finder methods

Every repository that extends ServiceEntityRepository inherits a small set of methods. They cover simple lookups without any query code:

Method Returns
find($id) One entity by primary key, or null
findAll() Every row as an array of entities
findBy($criteria, $orderBy, $limit, $offset) An array of entities matching all criteria
findOneBy($criteria, $orderBy) The first match, or null
count($criteria) The number of matching rows, as an integer
PHP
$cheap   = $products->findBy(['category' => 'books'], ['price' => 'ASC'], 10);
$one     = $products->findOneBy(['sku' => 'BK-100']);
$howMany = $products->count(['category' => 'books']);

The criteria array means "field equals value, and all of them". There is no OR, no greater-than and no LIKE. There are also magic methods such as findOneBySku('BK-100') and findByCategory('books'), but they hide the field names from your IDE and static analysis, so most teams prefer the explicit forms above.

Two cautions. findAll() loads every row into memory, which is fine for a table of ten countries and a mistake for a table of ten million orders. And the field names in criteria are property names of the entity, not column names. If you mapped $createdAt to the column created_at, you write createdAt.

Custom repository methods

When a query has a name in your domain, such as "products on sale" or "posts published this month", give it a method on your repository. The class that make:entity created is the right home:

PHP
<?php

namespace App\Repository;

use App\Entity\Product;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @extends ServiceEntityRepository<Product>
 */
class ProductRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Product::class);
    }

    /**
     * @return Product[]
     */
    public function findCheaperThan(int $cents): array
    {
        return $this->createQueryBuilder('p')
            ->andWhere('p.price < :limit')
            ->setParameter('limit', $cents)
            ->orderBy('p.price', 'ASC')
            ->getQuery()
            ->getResult();
    }
}

The p is an alias for the entity, like a table alias in SQL. The :limit is a named parameter: the value is sent separately from the query text, so it can never change the meaning of the query. Never concatenate user input into a query string. The @extends docblock tells tools such as PHPStan and your IDE that find() returns Product|null.

The shape of a repository call is always the same: build, convert to a query, execute.

flowchart LR
    C["Controller"] --> R["ProductRepository"]
    R --> QB["QueryBuilder<br/>(or DQL string)"]
    QB --> Q["Query"]
    Q --> SQL["SQL sent through DBAL"]
    SQL --> DB[("Database")]
    DB --> H["Hydration into<br/>Product objects"]
    H --> C

A repository builds a query in terms of entities; Doctrine turns it into SQL and turns the rows back into objects.

DQL and the QueryBuilder

DQL (Doctrine Query Language) looks like SQL but talks about entities and properties instead of tables and columns. The query below is DQL in a string:

PHP
$query = $entityManager->createQuery(
    'SELECT p FROM App\Entity\Product p WHERE p.price > :min ORDER BY p.price ASC'
);
$query->setParameter('min', 1000);
$expensive = $query->getResult();

The QueryBuilder produces exactly the same DQL through method calls, which makes it easy to add conditions only when you need them:

PHP
public function search(?string $term, ?int $maxPrice): array
{
    $qb = $this->createQueryBuilder('p')->orderBy('p.name', 'ASC');

    if ($term !== null) {
        $qb->andWhere('p.name LIKE :term')
           ->setParameter('term', '%'.$term.'%');
    }

    if ($maxPrice !== null) {
        $qb->andWhere('p.price <= :max')
           ->setParameter('max', $maxPrice);
    }

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

Which way you use is a matter of taste: DQL is compact for a fixed query, and the QueryBuilder is better for a filter form where the conditions vary. The result type depends on the method you call at the end:

  • getResult() returns an array of entities (or arrays, if you selected fields).
  • getOneOrNullResult() returns one entity or null, and throws if more than one row matches.
  • getSingleScalarResult() returns one value, perfect for SELECT COUNT(p.id).
  • toIterable() yields rows one at a time, which saves memory on big exports.

If you select only some fields, for example ->select('p.name', 'p.price'), you get plain arrays and no entity objects. That is faster for read-only lists and avoids loading data you will not show.

Here is a small runnable stand-in. It is not Doctrine: it is a tiny query builder over PDO and in-memory SQLite, with the same fluent style and, just as important, the same bound parameters.

PHP A tiny QueryBuilder over PDO (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Notice that the SQL text never contains a value, only :category and :max. The values travel separately, which is what makes this safe against SQL injection.

Dropping down to DBAL

Doctrine is built in two layers. The upper layer is the ORM, which maps objects to rows. The lower layer is DBAL (Database Abstraction Layer), a thin wrapper around PDO that handles connections, parameters and portability. You can use DBAL directly for reports, bulk updates and queries that do not map to one entity:

PHP
use Doctrine\DBAL\Connection;

public function revenuePerCategory(Connection $connection): array
{
    $sql = 'SELECT category, SUM(price) AS revenue
            FROM product
            GROUP BY category
            ORDER BY revenue DESC';

    return $connection->fetchAllAssociative($sql);
}

The Connection service is autowirable. The result is an array of associative arrays, not entities. Use this for read-only aggregates, where building objects would be wasted work. Be aware that raw SQL uses table and column names, and it ties you to one database vendor's dialect. Always pass values as parameters: $connection->fetchAllAssociative($sql, ['cat' => $category]).

A good rule of thumb: use finder methods for simple lookups, the QueryBuilder for entity queries, and DBAL for reports.

Pagination

Lists should not return every row. The idea is LIMIT and OFFSET: a page number becomes "skip this many, take that many". With Doctrine's Paginator you also get the total count, which you need to draw page links:

PHP
use Doctrine\ORM\Tools\Pagination\Paginator;

public function paginate(int $page, int $perPage = 10): Paginator
{
    $query = $this->createQueryBuilder('p')
        ->orderBy('p.id', 'DESC')
        ->setFirstResult(($page - 1) * $perPage)
        ->setMaxResults($perPage)
        ->getQuery();

    return new Paginator($query);
}

In the controller, count($paginator) gives the total number of rows and iterating the paginator gives the entities of the current page. Always add an ORDER BY with a unique tiebreaker such as the id: without a stable order, a row can appear on two pages or on none. For very deep pages, OFFSET gets slow because the database still counts the skipped rows, and a "seek" query (WHERE id < :lastSeenId) scales better. The stand-in below shows both the page maths and the total:

PHP Paging with LIMIT and OFFSET (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which call returns the first matching entity or null?

    findOneBy() returns a single entity or null. findBy() returns an array, findAll() returns every row and count() returns an integer.
  2. 2. Why should values go into setParameter() instead of being concatenated into DQL?

    Bound parameters keep data and query text apart, which prevents SQL (and DQL) injection.
  3. 3. What does DQL refer to in a query such as SELECT p FROM App\Entity\Product p?

    DQL is written in terms of entities and properties. Doctrine translates it to SQL using your mapping.
  4. 4. Which tool fits a read-only report that sums prices per category?

    An aggregate returns plain rows, so DBAL avoids building entity objects you do not need and lets the database do the summing.
  5. 5. Why add a unique column such as the id to the ORDER BY of a paginated query?

    When many rows tie on the sort column, the database may order them differently per query. A unique tiebreaker makes pages consistent.

Summary

  • A repository fetches entities of one class; autowire it like any service.
  • The built-ins (find, findAll, findBy, findOneBy, count) cover equality lookups; avoid findAll() on big tables.
  • Put named queries in custom repository methods built with the QueryBuilder or DQL, and always bind values as parameters.
  • DQL speaks about entities and properties; DBAL speaks plain SQL and returns arrays, which suits reports.
  • Paginate with setFirstResult, setMaxResults and the Paginator, using a stable ORDER BY.

Next up: Relations.