Missing aggregate count on AssociationField

[kissifrot]

Describe the bug

Looks like for each entity EasyAdmin just uses a SELECT * for each related entity. While it can be mostly useful with few entities, it quickly becomes a nightmare when there are thousands of related entities. I'm getting out of memory limit when listing entities with AssociationField. I searched through the docs and I didn't find a way to use aggregate counts on AssociationField displayed on index page.

Do I have to create a custom field or is there a way to override the "SELECT * " and replace it with a "SELECT COUNT()"?

To Reproduce Use an AssociationField with many related entities --> memory limit is hit

[fabienlem]

Hello @kissifrot,

This is how I proceed in such cases thanks to EasyAdmin events. Let's say the main entity is Library and related one is Book.

1. Add a non-mapped field (not stored in database) in Library entity.

#[ORM\Entity(repositoryClass: LibraryRepository::class)]
class Library 
{
    private int $numberOfBooks = 0;

    public function getNumberOfBooks(): int
    {
        return $this->numberOfBooks;
    }

    public function setNumberOfBooks($numberOfBooks): self
    {
        $this->numberOfBooks = $numberOfBooks;

        return $this;
    }
}

2. Hook on AfterCrudActionEvent with an EventSubscriber

AfterCrudActionEvent contains the list of entities to display in the index action.

<?php

namespace App\EventSubscriber;

use App\Controller\LibraryCrudController;
use App\Service\LibraryCollectionFiller;
use EasyCorp\Bundle\EasyAdminBundle\Event\AfterCrudActionEvent;
use Symfony\Component\EventDispatcher\EventSubscriberInterface;

class LibraryIndexSubscriber implements EventSubscriberInterface
{
    protected LibraryCollectionFiller $libraryCollectionFiller;

    public function __construct(LibraryCollectionFiller $libraryCollectionFiller)
    {
        $this->libraryCollectionFiller = $libraryCollectionFiller;
    }

    public function afterCrud(AfterCrudActionEvent $event)
    {
        if (LibraryCrudController::class !== $event->getAdminContext()->getRequest()->query->get('crudControllerFqcn') || !isset($event->getResponseParameters()->all()['entities'])) {
            return;
        }

        $this->libraryCollectionFiller->fill($event->getResponseParameters()->all()['entities']);
    }

    public static function getSubscribedEvents(): array
    {
        return [
            AfterCrudActionEvent::class => ['afterCrud'],
        ];
    }
}

3. Use a service to populate the field we previously added to the Library entity

<?php

namespace App\Service;

use App\Repository\LibraryRepository;
use EasyCorp\Bundle\EasyAdminBundle\Collection\EntityCollection;

class LibraryCollectionFiller
{
    protected LibraryRepository $libraryRepository;

    public function __construct(LibraryRepository $libraryRepository)
    {
        $this->libraryRepository= $libraryRepository;
    }

    public function fill(EntityCollection $libraryCollection): void
    {
        // gets ids of entities to display
        $ids = [];

        foreach ($libraryCollection as $id => $entityDto) {
            $ids[] = $id;
        }

        // gets mapping: entity id => number of books
        $counters = $this->libraryRepository->countBooksByLibraryIds($ids);

        // populates entities with corresponding number of books
        foreach ($libraryCollection as $id => $entityDto) {
            $entityDto->getInstance()->setNumberOfBooks($counters[$id]['number_of_books'] ?? 0);
        }
    }
}

4. Perform a query in order to get mapping: entity id => number of books

Doctrine hydrates an array here, so no memory consumption issue.

namespace App\Repository;

class LibraryRepository extends ServiceEntityRepository
{
    public function countBooksByLibraryIds(array $libraryIds): array
    {
        return $this
            ->getEntityManager()
            ->createQuery('
                SELECT lib.id, COUNT(b) AS number_of_books
                FROM App\Entity\Library lib INDEX BY lib.id
                JOIN lib.books b
                WHERE lib.id IN (:ids)
                GROUP BY lib.id
            ')
            ->setParameter('ids', $libraryIds)
            ->getResult();
    }
}

5. Use the new field in your CrudController

public function configureFields(string $pageName): iterable
{
    yield IntegerField::new('numberOfBooks', 'Books')
        ->setTemplatePath('field/custom_template_if_necessary.html.twig')
        ->onlyOnIndex();
}

I hope it helps you.

Fabien

[parijke]

I do more or less the same but way simpeler I think? Just call the service in the formatValue method like this

->formatValue(
                function ($value, $entity): ?int {
                    if (null !== $entity) {
                        return $this->bookingService->daysBeforeNextBooking($entity);
                    }

                    return -1;
                }

[kissifrot]

I do more or less the same but way simpeler I think? Just call the service in the formatValue method like this

->formatValue(
                function ($value, $entity): ?int {
                    if (null !== $entity) {
                        return $this->bookingService->daysBeforeNextBooking($entity);
                    }

                    return -1;
                }

It indeed works, thanks for the trick 👍

Would have been cool to be able to add an option in AssociationField though, without having to add a "virtual" property or adding many classes.