Doctrine fetch: EXTRA_LAZY, LAZY, EAGER

Fetch means whether a related entity or collection will be additionally fetched.
For example, is it necessary to get the Photo entity along with getting the Product entity?

There are several fetch strategies:

• EXTRA_LAZY - related collection will not be fetched until the collection method is called, except for these methods contains($entity), containsKey($key), count(), get($key), slice($offset, $length = null), add($entity).
  Adding an entity in this way will also not cause the collection to be fetched: $collection[] = $entity
• LAZY - related entity or collection will not be fetched until the entity property or collection method is called
• EAGER - related entity or collection will always be fetched

The EXTRA_LAZY strategy is used for collections, LAZY and EAGER for entities.
By default, all relation types use the LAZY strategy.

Rules for setting strategies:

• For relation OneToOne fit EAGER
• For relation OneToMany, fit EXTRA_LAZY
• For relation ManyToOne, fit EAGER
• For relation ManyToMany, fit EXTRA_LAZY

EXTRA_LAZY

OneToOne

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    #[ORM\OneToOne(fetch: 'EXTRA_LAZY')]
    private ?Photo $photo = null;
}

#[ORM\Entity(repositoryClass: PhotoRepository::class)]
class Photo
{
    // relation is set on the side of the Product entity
}

FOREIGN KEY is set on the side of the Product entity.

Getting the Product entity:

$product = $em->getRepository(Product::class)->find(1);

Doctrine will generate a SQL query:

SELECT * FROM product t0 WHERE t0.id = 1

The SQL query shows that only the Product entity was fetched.

EXTRA_LAZY strategy does not make sense to use for an entity, it is used for collections. For this relation type, it would be better to specify EAGER strategy, since the Photo entity will be used frequently and will be fetched through a LEFT JOIN, and not a separate SQL query.

OneToMany

#[ORM\Entity(repositoryClass: CategoryRepository::class)]
class Category
{
    #[ORM\OneToMany(mappedBy: 'category', targetEntity: Product::class, fetch: 'EXTRA_LAZY')]
    private $products;
}

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    #[ORM\ManyToOne(inversedBy: 'products')]
    #[ORM\JoinColumn(nullable: false)]
    private ?Category $category = null;
}

FOREIGN KEY is set on the side of the Product entity.

Getting the Category entity:

$category = $em->getRepository(Category::class)->find(1);

Doctrine will generate a SQL query:

SELECT * FROM category t0 WHERE t0.id = 1

The SQL query shows that only the Category entity was fetched.

Getting the number of products belonging to the Category entity:

$count = $em->getRepository(Category::class)->find(1)->getProducts()->count();

Doctrine will generate SQL queries:

SELECT * FROM category t0 WHERE t0.id = 1;
SELECT COUNT(*) FROM product t0 WHERE t0.category_id = 1;

Although the Product entity collection was accessed, it was not fetched; instead, the COUNT(*) SQL query was executed. The LAZY strategy in this case would get the collection and count the number of elements in the array with the count() function. In the case of a large number of elements in the array, this could result in high memory consumption and performance degradation.

Checking if the Product entity belongs to the Category entity:

$isContains = $em->getRepository(Category::class)->find(1)->getProducts()->contains($product);

Doctrine will generate a SQL query:

SELECT 1 FROM product t0 WHERE t0.id = 1 AND t0.category_id = 1

The belonging check for the Product entity was performed using a SQL query. The LAZY strategy in this case would load the collection and iterate over all the elements of the array. In the case of a large number of elements in the array, this could result in high memory consumption and performance degradation.

Collection slice (pagination):

$products = $em->getRepository(Category::class)->find(1)->getProducts()->slice(10, 10);

Doctrine will generate SQL query:

SELECT * FROM product t0
INNER JOIN category t10 ON t0.category_id = t10.id
WHERE t0.category_id = 1
LIMIT 10
OFFSET 10

The slice of the collection was performed using a SQL query. The LAZY strategy in this case would fetch the collection and slice the array with the array_slice() function. In the case of a large number of elements in the array, this could result in high memory consumption and performance degradation.

For a given relation type, the EXTRA_LAZY strategy is efficient.

ManyToOne

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    #[ORM\ManyToOne(fetch: 'EXTRA_LAZY', inversedBy: 'products')]
    #[ORM\JoinColumn(nullable: false)]
    private ?Category $category = null;
}

#[ORM\Entity(repositoryClass: CategoryRepository::class)]
class Category
{
    #[ORM\OneToMany(mappedBy: 'category', targetEntity: Product::class)]
    private $products;
}

FOREIGN KEY is set on the side of the Product entity.

Getting the Product entity:

$product = $em->getRepository(Product::class)->find(1);

Doctrine will generate a SQL query:

SELECT * FROM product t0 WHERE t0.id = 1

The SQL query shows that only the Product entity was fetched.

EXTRA_LAZY strategy does not make sense to use for an entity, it is used for collections. For this type of relation, it would be better to specify the EAGER strategy, since the Category entity will be used frequently and will be fetched via an INNER JOIN, and not a separate SQL query.

ManyToMany

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    #[ORM\ManyToMany(targetEntity: Stock::class, inversedBy: 'products', fetch: 'EXTRA_LAZY')]
    private Collection $stocks;
}

#[ORM\Entity(repositoryClass: StockRepository::class)]
class Stock
{
    #[ORM\ManyToMany(targetEntity: Product::class, mappedBy: 'stocks', fetch: 'EXTRA_LAZY')]
    private Collection $products;
}

FOREIGN KEY's for the Product and Stock entities are set in the related table product_stock.

Getting the Product entity:

$product = $em->getRepository(Product::class)->find(1);

Doctrine will generate a SQL query:

SELECT * FROM product t0 WHERE t0.id = 1

The SQL query shows that only the Product entity was fetched.

Getting the Stock entity:

$stock = $em->getRepository(Stock::class)->find(1);

Doctrine will generate a SQL query:

SELECT * FROM stock t0 WHERE t0.id = 1

The SQL query shows that only the Stock entity was fetched.

Since the Stocks collection is specified in the Product entity and the Product collection is specified in the Stocks entity, it is optimal for both of them to set the EXTRA_LAZY strategy in order to be able to efficiently get the number of elements in the collection using the count() method and slice through the collection (pagination) using the slice() method .

LAZY (default)

OneToOne

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    #[ORM\OneToOne()] // no need to specify fetch: 'LAZY' as this is the default strategy
    private ?Photo $photo = null;
}

#[ORM\Entity(repositoryClass: PhotoRepository::class)]
class Photo
{
    // relation is set on the side of the Product entity
}

FOREIGN KEY is set on the side of the Product entity.

Getting the Product entity:

$product = $em->getRepository(Product::class)->find(1);

Doctrine will generate a SQL query:

SELECT * FROM product t0 WHERE t0.id = 1

The SQL query shows that only the Product entity was fetched.

For this type of relation, it would be better to specify the EAGER strategy, since the Photo entity will be used frequently and will be fetched via a LEFT JOIN, rather than a separate SQL query.

OneToMany

#[ORM\Entity(repositoryClass: CategoryRepository::class)]
class Category
{
    // no need to specify fetch: 'LAZY' as this is the default strategy
    #[ORM\OneToMany(mappedBy: 'category', targetEntity: Product::class )] 
    private $products;
}

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    #[ORM\ManyToOne(inversedBy: 'products')]
    #[ORM\JoinColumn(nullable: false)]
    private ?Category $category = null;
}

FOREIGN KEY is set on the side of the Product entity.

Getting the Category entity:

$category = $em->getRepository(Category::class)->find(1);

Doctrine will generate a SQL query:

SELECT * FROM category t0 WHERE t0.id = 1

The SQL query shows that only the Category entity was fetched.

Getting the number of products belonging to the Category entity:

$count = $em->getRepository(Category::class)->find(1)->getProducts()->count();

Doctrine will generate SQL queries:

SELECT * FROM category t0 WHERE t0.id = 1;
SELECT * FROM product t0 WHERE t0.category_id = 1;

The SQL queries shows that the Category entity was fetched and a collection of Product entities was fetched by a separate SQL query. The collection of Product entities was first loaded into memory, and then the number of elements in the array was counted by the count() function. This is an inefficient way of counting the number of elements in a collection, as memory consumption will increase with the size of the collection, resulting in performance degradation. The EXTRA_LAZY strategy would in this case execute the COUNT(*) SQL query, which is an efficient way to get the number of elements in the collection.

For a given relation type, it would be better to specify the EXTRA_LAZY strategy.

ManyToOne

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    // no need to specify fetch: 'LAZY' as this is the default strategy
    #[ORM\ManyToOne(inversedBy: 'products')]
    #[ORM\JoinColumn(nullable: false)]
    private ?Category $category = null;
}

#[ORM\Entity(repositoryClass: CategoryRepository::class)]
class Category
{
    #[ORM\OneToMany(mappedBy: 'category', targetEntity: Product::class)]
    private $products;
}

FOREIGN KEY is set on the side of the Product entity.

Getting the Product entity:

$product = $em->getRepository(Product::class)->find(1);

Doctrine will generate a SQL query:

SELECT * FROM product t0 WHERE t0.id = 1

The SQL query shows that only the Product entity was fetched.

For this type of relation, it would be better to specify the EAGER strategy, since the Category entity will be used frequently and will be efficiently fetched via an INNER JOIN, rather than a separate SQL query.

ManyToMany

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    // no need to specify fetch: 'LAZY' as this is the default strategy
    #[ORM\ManyToMany(targetEntity: Stock::class, inversedBy: 'products')]
    private Collection $stocks;
}

#[ORM\Entity(repositoryClass: StockRepository::class)]
class Stock
{
    #[ORM\ManyToMany(targetEntity: Product::class, mappedBy: 'stocks')]
    private Collection $products;
}

FOREIGN KEY's for the Product and Stock entities are set in the related table product_stock.

Getting the Product entity:

$product = $em->getRepository(Product::class)->find(1);

Doctrine will generate a SQL query:

SELECT * FROM product t0 WHERE t0.id = 1

The SQL query shows that only the Product entity was fetched.

Since the Stocks collection is specified in the Product entity and the Product collection is specified in the Stocks entity, it is optimal for both of them to set the EXTRA_LAZY strategy in order to be able to efficiently get the number of elements in the collection using the count() method and slice through the collection (pagination) using the slice() method .

EAGER

OneToOne

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    #[ORM\OneToOne(mappedBy: 'product', fetch: 'EAGER')]
    private ?Photo $photo = null;
}

#[ORM\Entity(repositoryClass: PhotoRepository::class)]
class Photo
{
    // relation is set on the side of the Product entity
}

FOREIGN KEY is set on the side of the Product entity.

Getting the Product entity:

$product = $em->getRepository(Product::class)->find(1);

Doctrine will generate a SQL query:

SELECT * FROM product t0
LEFT JOIN photo t8 ON t0.photo_id = t8.id
WHERE t0.id = 1

The SQL query shows that along with the Product entity, the Photo entity was also fetched.

For this type of relation, this is the optimal strategy, since the Photo entity will be used frequently and will be fetched through a LEFT JOIN, and not a separate SQL query.

OneToMany

#[ORM\Entity(repositoryClass: CategoryRepository::class)]
class Category
{
    #[ORM\OneToMany(mappedBy: 'category', targetEntity: Product::class, fetch: 'EAGER')]
    private $products;
}

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    #[ORM\ManyToOne(inversedBy: 'products')]
    #[ORM\JoinColumn(nullable: false)]
    private ?Category $category = null;
}

FOREIGN KEY is set on the side of the Product entity.

Getting the Category entity:

$category = $em->getRepository(Category::class)->find(1);

Doctrine will generate SQL queries:

SELECT * FROM category t0
LEFT JOIN product t3 ON t3.category_id = t0.id
WHERE t0.id = 1;
SELECT * FROM photo t0 WHERE t0.id IN (...);

The SQL query shows that together with the Category entity, a collection of Product entities was also fetched, and the Photo entity was fetched by a separate inefficient SQL query.

For this type of relation, it is recommended to use the EXTRA_LAZY strategy.

ManyToOne

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    #[ORM\ManyToOne(fetch: 'EAGER', inversedBy: 'products')
    #[ORM\JoinColumn(nullable: false)]
    private ?Category $category = null;
}

#[ORM\Entity(repositoryClass: CategoryRepository::class)]
class Category
{
    #[ORM\OneToMany(mappedBy: 'category', targetEntity: Product::class)]
    private $products;
}

FOREIGN KEY is set on the side of the Product entity.

Getting the Product entity:

$product = $em->getRepository(Product::class)->find(1);

Doctrine will generate SQL queries:

SELECT * FROM product t0
INNER JOIN category t7 ON t0.category_id = t7.id
WHERE t0.id = 1

The SQL query shows that along with the Product entity, the Category entity was also fetched.

For a given relation type, the EAGER strategy is efficient.

ManyToMany

#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
    #[ORM\ManyToMany(targetEntity: Stock::class, inversedBy: 'products', fetch: 'EAGER')]
    private Collection $stocks;
}

#[ORM\Entity(repositoryClass: StockRepository::class)]
class Stock
{
    #[ORM\ManyToMany(targetEntity: Product::class, mappedBy: 'stocks', fetch: 'EAGER')]
    private Collection $products;
}

FOREIGN KEY's for the Product and Stock entities are set in the related table product_stock.

Getting the Product entity:

$product = $em->getRepository(Product::class)->find(1);

Doctrine will generate SQL queries:

SELECT * FROM product t0 WHERE t0.id = 1;
SELECT * FROM stock t0
INNER JOIN product_stock ON t0.id = product_stock.stock_id
WHERE product_stock.product_id = 1;
SELECT * FROM product t0
INNER JOIN product_stock ON t0.id = product_stock.product_id
WHERE product_stock.stock_id = 1;

SQL queries show that the Product, Stock entity and the Product entity belonging to this stock were fetched by a separate SQL query. The number of SQL queries will increase with the number of products in stock. In this case, Doctrine generated inefficient SQL queries, which will increase memory consumption and degrade performance.

Getting the Stock entity:

$stock = $em->getRepository(Stock::class)->find(1);

Doctrine will generate SQL queries:

SELECT * FROM stock t0 WHERE t0.id = 1;
SELECT * FROM product t0
INNER JOIN product_stock ON t0.id = product_stock.product_id
WHERE product_stock.stock_id = 1;
SELECT * FROM stock t0
INNER JOIN product_stock ON t0.id = product_stock.stock_id
WHERE product_stock.product_id = 1;

It can be seen from the SQL queries that the Stock entity was fetched, a collection of Product entities belonging to this stock, and a Stock entity for each Product entity. The number of SQL queries will increase with the number of products in a given stock. In this case, Doctrine generated inefficient SQL queries, which will increase memory consumption and degrade performance.

Since the Stocks collection is specified in the Product entity and the Product collection is specified in the Stocks entity, it is optimal for both of them to set the EXTRA_LAZY strategy in order to be able to efficiently get the number of elements in the collection using the count() method and slice through the collection (pagination) using the slice() method .