Show aggregate count of large number of entities in Symfony 3.4 Easy Admin bundle

Question

I'm trying to show a count of related entities for an entity List view, but there's too many so it runs out of memory (it does a simple count($this->relatedEntities)) in the Entity.

Any idea how/where I can override the ListController of QueryBuilder to add an aggeregated COUNT() column?

I tried extending the AdminController and hooking into the findAll() function (to manually add a count to each object), but that doesn't give me a list of entities but a Pagerfanta object.

additionally you can check extra lazy loading or eager loading, both approaches avoid the extra sql queries for related entities when you're just interested in the count — LBA, Dec 17 '18 at 13:57

score 2 · Accepted Answer · answered Dec 17 '18 at 13:57

Here's how I fixed it:

Overriding the renderTemplate in custom AdminController:

protected function renderTemplate($actionName, $templatePath, array $parameters = array())
{
    if ($actionName === 'list' && $this->entity['class'] === ClassA::class) {
        //piggyback on virtual property 'count'
        $parameters['fields']['count']['servicecounts'] = $this->MyEntityRepository->getCounts();
    }

    return $this->render($templatePath, $parameters);
}

easy_admin config:

  list:
    fields:
    - { property: 'count', template: 'count.html.twig' }

count.html.twig:

{{ field_options.servicecounts[item.id] }}

getCounts function:

public function getCounts()
{
    $qb = $this->getEntityManager()->createQueryBuilder();

    $qb
        ->select('s.id, count(ce.recordId)')
        ->from(ClassA::class, 's')
        ->leftJoin(ClassB::class, 'ce', Join::WITH, 's.id = ce.service')
        ->groupBy('s.id')
    ;

    $results = [];
    foreach ($qb->getQuery()->execute() as $row) {
        $results[$row['id']] = $row[1];
    }

    return $results;

}

Show aggregate count of large number of entities in Symfony 3.4 Easy Admin bundle

1 Answers1