Doctrine Query Builder Optimization

The article describes how to reduce the number of Doctrine generated SQL queries when using Query Builder.

The Product entity, which has the required Brand and Category entities and the optional Photo entity:

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    #[ORM\ManyToOne()]
    #[ORM\JoinColumn(nullable: false)]
    private ?Brand $brand = null;

    #[ORM\ManyToOne(inversedBy: 'products')]
    #[ORM\JoinColumn(nullable: false)]
    private ?Category $category = null;

    #[ORM\OneToOne()]
    private ?Photo $photo = null;
}

Product entity repository:

class ProductRepository extends ServiceEntityRepository
{
    public function findByCategoryId(int $categoryId): array
    {
        return $this->createQueryBuilder('p')
            ->andWhere('p.category = :categoryId')
            ->setParameter('categoryId', $categoryId)
            ->getQuery()
            ->getResult()
        ;
    }
}

Getting products that belong to category with id 1:

$products = $productRepository->findByCategoryId(1);

foreach ($products as $product) {
    $brand = $product->getBrand()->getName();
    $category = $product->getCategory()->getName();
    $photo = $product->getPhoto()->getName();
}

Doctrine will generate SQL queries:

--1.07 ms
SELECT * FROM product p0_ WHERE p0_.category_id = 1;
--0.32 ms
SELECT * FROM brand t0 WHERE t0.id = 1;
--0.32 ms
SELECT * FROM category t0 WHERE t0.id = 1;
--0.34 ms
SELECT * FROM photo t0 WHERE t0.id = 1;

In total, 4 SQL queries were generated, the execution of which took 2.04 ms.

For each Product entity, an SQL query will be generated to get the Brand and Photo entities, as well as one SQL query to get the Category entity.
As the amount of data increases, the number of SQL requests will increase, which will affect performance.

Let's rewrite the query in Query Builder:

public function findByCategoryId(int $categoryId): array
{
    return $this->createQueryBuilder('p')
        ->select(['p, b, c, ph'])
        ->innerJoin('p.brand', 'b')
        ->innerJoin('p.category', 'c')
        ->leftJoin('p.photo', 'ph')
        ->andWhere('p.category = :categoryId')
        ->setParameter('categoryId', $categoryId)
        ->getQuery()
        ->getResult()
    ;
}

An select has been added to load all joined entities.
An innerJoin has been added for the Brand, Categoryentities, as they are required.
An leftJoin has been added for the Photo entity, as it is optional.

Doctrine will generate 1 SQL query instead of 4:

--2.10 ms
SELECT * FROM product p0_
INNER JOIN brand b1_ ON p0_.brand_id = b1_.id
INNER JOIN category c2_ ON p0_.category_id = c2_.id
LEFT JOIN photo p3_ ON p0_.photo_id = p3_.id
WHERE p0_.category_id = 1;

As a result, the number of SQL queries was reduced from 4 to 1 and performance was improved, which will be noticeable on a large amount of data.

It is recommended to check Doctrine generated SQL queries when using Query Builder.
A large number of SQL queries may indicate an inefficient way to get data.