Doctrine extra_lazy does not work as expected when I count entities-CodePudding

I'm using EasyAdminBundle (v4.5) and I would like to hide the delete button when a Definition has at least one Property. As documentation suggests it, I use configureActions method to set it up:

class DefinitionCrudController extends AbstractCrudController
{
    public static function getEntityFqcn(): string
    {
        return Definition::class;
    }

    public function configureActions(Actions $actions): Actions
    {
        return $actions
            ->add(Crud::PAGE_INDEX, Action::DETAIL)
            ->update(Crud::PAGE_INDEX, Action::DELETE,
                fn (Action $action) => $action->displayIf(
                    fn (Definition $entity) => $entity->getProperties()->count() > 0
                )
            )
            ;
    }

I works, but when I checked profiler, I realized that n 1 queries are launched (n is the number of definitions). I checked that I have enabled extra_lazy association with fetch:'EXTRA_LAZY' like docs.

#[ORM\Entity(repositoryClass: DefinitionRepository::class)]
class Definition implements DefinitionInterface
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: Types::INTEGER)]
    private ?int $id = null;

    /**
     * @var ArrayCollection<int, PropertyInterface>
     */
    #[ORM\OneToMany(mappedBy: 'definition', targetEntity: AbstractProperty::class, fetch: 'EXTRA_LAZY', orphanRemoval: true)]
    private Collection $properties;

Her is the Property declarations:

#[ORM\Entity(repositoryClass: PropertyRepository::class)]
#[ORM\Table(name: 'lopb_properties')]
#[ORM\InheritanceType('SINGLE_TABLE')]
#[ORM\DiscriminatorColumn(name: 'type', type: 'string')]
#[ORM\DiscriminatorMap([
    Types::BOOLEAN => BoolProperty::class,
    Types::FLOAT => FloatProperty::class,
    Types::INTEGER => IntegerProperty::class,
    Types::STRING => StringProperty::class,
])]
abstract class AbstractProperty implements PropertyInterface
{
    #[ORM\ManyToOne(targetEntity: Definition::class, inversedBy: 'properties')]
    #[ORM\JoinColumn(nullable: false)]
    private DefinitionInterface $definition;

Here are the requests executed:

SELECT DISTINCT l0_.id AS id_0, l0_.name AS name_1, l0_.entity_classname AS entity_classname_2 
  FROM lopb_definitions l0_ 
 ORDER BY l0_.name ASC, l0_.entity_classname ASC LIMIT 20

SELECT l0_.enabled AS enabled_0, l0_.entity_classname AS entity_classname_1, l0_.id AS id_2, l0_.name AS name_3, l0_.type AS type_4 FROM lopb_definitions l0_ WHERE l0_.id IN (?, ?, ?, ?, ?) ORDER BY l0_.name ASC, l0_.entity_classname ASC

For the previous request, the select isn't complete. Doctrine should have executed something like this one:

SELECT DISTINCT l0_.id AS id_0, l0_.name AS name_1, l0_.entity_classname AS entity_classname_2, count(l1_.id)
  FROM lopb_definitions l0_ 
LEFT OUTER JOIN lopb_properties l1_ on l1_.id = lo_.property_id
GROUP BY l0_.id AS id_0, l0_.name AS name_1, l0_.entity_classname AS entity_classname_2
 ORDER BY l0_.name ASC, l0_.entity_classname ASC LIMIT 20

I read this post and I tried to change fn (Definition $entity) => $entity->getProperties()->count() > 0 by fn (Definition $entity) => count($entity->getProperties()). Same problem.

I tried to remove the SINGLE_TABLE inheritance to use a more standard mapping with a non-abstract class. It doesn't change the number of requests. I always have n 1 queries!

I don't find my error. Is it because I'm using OneToMany?

CodePudding user response：

The problem you are experiencing is caused by the way you are retrieving the count of properties for each definition in the configureActions method. The count method on a Doctrine collection, such as $entity->getProperties()->count() or count($entity->getProperties()), triggers a separate query for each definition to retrieve the count of properties. This is causing the n 1 query problem you are seeing in the profiler.

To avoid this, you can use the size method of the collection instead.

Change this line:

fn (Definition $entity) => $entity->getProperties()->count() > 0

fn (Definition $entity) => $entity->getProperties()->size() > 0

The size method returns the number of elements in the collection without triggering any additional queries.

Alternatively, you can also use DQL (Doctrine query language) or native SQL to retrieve the count of properties for all definitions in a single query and then use it in the configureActions method.

CodePudding user response：

I'm not familiar with EasyAdmin but I can help you understand Doctrine's lazy loading:

By default performing a count() on a OneToMany relationship that is not loaded in cache will query the whole collection and then count it.

Adding fetch: 'EXTRA_LAZY' means Doctrine will only perform the appropriate SELECT query to get the size of your collection which is better but still not what you want (see https://www.doctrine-project.org/projects/doctrine-orm/en/2.14/tutorials/extra-lazy-associations.html#extra-lazy-associations)

In both cases if you want to solve the n 1 problem you need to load the collection in cache beforehand.