Implementing Custom Repositories with Doctrine's QueryBuilder: A Practical Guide

Desired Outcome

By the end of this guide you will know how to:

• Define a custom repository class for an entity.
• Write a DQL query using Doctrine's QueryBuilder to fetch data efficiently.
• Inject the repository into a service and verify the generated SQL.
• Recover from common pitfalls such as the N+1 problem or mis-configured repositories.

Prerequisites

• PHP 8.2+ and Composer installed.
• Doctrine ORM 2.11+ (or Symfony 6+ where Doctrine is bundled).
• Basic understanding of entities, repositories, and DQL.
• Database access (MySQL, PostgreSQL, etc.) and a profiler (Doctrine DBAL profiler or Xdebug).

Step-by-Step Procedure

1. Create the Entity

Assume we have a Product entity with a many-to-one relationship to Category. The entity is mapped via attributes for brevity.

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: ProductRepository::class)]
#[ORM\Table(name: 'product')]
class Product
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private int $id;

    #[ORM\Column(type: 'string', length: 255)]
    private string $name;

    #[ORM\ManyToOne(targetEntity: Category::class, inversedBy: 'products')]
    #[ORM\JoinColumn(nullable: false)]
    private Category $category;

    // getters & setters omitted for brevity
}

The key line is repositoryClass: ProductRepository::class – it tells Doctrine which repository to instantiate for this entity.

2. Define the Custom Repository

Create src/Repository/ProductRepository.php extending ServiceEntityRepository and add a method that uses the QueryBuilder to fetch products by category name, eager-loading the category to avoid N+1 queries.

namespace App\Repository;

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

class ProductRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Product::class);
    }

    /**
     * Find all products belonging to a category with the given name.
     * @param string $categoryName
     * @return Product[]
     */
    public function findByCategoryName(string $categoryName): array
    {
        return $this->createQueryBuilder('p')
            ->join('p.category', 'c')
            ->addSelect('c') // eager-load category
            ->where('c.name = :name')
            ->setParameter('name', $categoryName)
            ->orderBy('p.id', 'ASC')
            ->getQuery()
            ->getResult();
    }
}

3. Inject the Repository into a Service

Using Symfony's autowiring, the repository becomes a constructor argument. Example service:

namespace App\Service;

use App\Repository\ProductRepository;

class ProductService
{
    public function __construct(private ProductRepository $productRepository) {}

    public function getProductsInCategory(string $name): array
    {
        return $this->productRepository->findByCategoryName($name);
    }
}

4. Verify Generated SQL

Run the service method in a controller or console command and enable the Doctrine DBAL profiler:

// in a controller
$products = $this->productService->getProductsInCategory('Electronics');

In Symfony's profiler toolbar, locate the SQL query. It should resemble:

SELECT p.id AS id, p.name AS name, c.id AS c_id, c.name AS c_name
FROM product p
INNER JOIN category c ON p.category_id = c.id
WHERE c.name = :name
ORDER BY p.id ASC

Check that only one query runs and that the category data is fetched via an INNER JOIN (not a separate query per product). This SQL shape is illustrative; confirm the actual output in your own profiler since aliases and column lists vary by mapping.

5. Recover from Common Pitfalls

• N+1 Problem: If you notice multiple queries for related entities, add addSelect('c') (a fetch join) as shown.
• Incorrect Repository Mapping: Verify repositoryClass is set on the entity. If missing, Doctrine falls back to the default EntityRepository, which lacks your custom methods and will throw a "method not found" error.
• Wrong Parameter Binding: Always use setParameter instead of string interpolation to avoid SQL injection and query-cache issues.
• Business Logic Leakage: Keep repositories focused on data retrieval. Complex domain rules belong in services; otherwise the data layer becomes hard to test and reuse.
• Profiler Not Visible: Ensure you are in the dev environment and that the web profiler bundle is installed.

Practical Checklist

• Run php bin/console doctrine:schema:validate to ensure mappings are correct.
• Write a test that calls findByCategoryName and asserts the returned objects are instances of Product.
• Inspect the profiler to confirm a single SQL statement and the presence of the join.
• Confirm the repository is autowired by running php bin/console debug:container App\Repository\ProductRepository.

Limitations and When to Use Native Queries

While QueryBuilder abstracts SQL, it may not support vendor-specific features (e.g., full-text search operators). In such cases, use createNativeQuery with a ResultSetMapping, but remember it bypasses DQL's portability and can lead to vendor lock-in. Keep business logic out of repositories regardless of query style.

Conclusion

Custom repositories keep data access logic isolated from business services, improving maintainability. By following the steps above, you can safely add complex queries, verify their performance, and recover from common misconfigurations.